如何：使用 SQL Server Profiler 來建立 SQL 追蹤收集組

在 SQL Server 2008 中，您可以利用 SQL Server Profiler 的伺服器端追蹤功能來匯出追蹤定義，以便用來建立使用一般 SQL 追蹤收集器型別的收集組。這個程序可以分成兩個部分：

1. 建立和匯出 SQL Server Profiler 追蹤。

2. 根據匯出的追蹤，編寫新收集組的指令碼。

下列程序的狀況包含收集需要 80 毫秒或更長時間才能完成之任何預存程序的相關資訊。若要完成這些程序，您應該要能夠：

• 使用 SQL Server Profiler 來建立和設定追蹤。

• 使用 SQL Server Management Studio 來開啟、編輯和執行查詢。

建立和匯出 SQL Server Profiler 追蹤

1. 在 SQL Server Management Studio 中，開啟 SQL Server Profiler (在 [工具] 功能表上，按一下 [SQL Server Profiler])。

2. 在 [連接到伺服器] 對話方塊中，按一下 [取消]。

3. 在這個狀況中，請確定持續時間值設定為以毫秒顯示 (預設值)。若要這樣做，請遵循下列步驟：

   1. 在 [工具] 功能表上，按一下 [選項]。

   2. 在 [顯示選項] 區域中，請確定已清除 [以百萬分之一秒顯示 [持續時間] 資料行的值 (僅限 SQL Server 2005 或更新版本)] 核取方塊。

   3. 按一下 [確定] 關閉 [一般選項] 對話方塊。

4. 在 [檔案] 功能表上，按一下 [新增追蹤]。

5. 在 [連接到伺服器] 對話方塊中，選取您想要連接的伺服器，然後按一下 [連接]。

   將出現 [追蹤屬性] 對話方塊。

6. 在 [一般] 索引標籤上，執行下列動作：

   1. 在 [追蹤名稱] 方塊中，輸入您想要用於追蹤的名稱。在這則範例中，追蹤名稱為 SPgt80。

   2. 在 [使用範本] 清單中，選取要用於追蹤的範本。在這則範例中，請按一下 [TSQL_SPs]。

7. 在 [事件選取範圍] 索引標籤上，執行下列動作：

   1. 識別要用於追蹤的事件。在這則範例中，請清除 [事件] 資料行中的所有核取方塊，但 [ExistingConnection] 和 [SP:Completed] 除外。

   2. 在右下角中，選取 [顯示所有資料行] 核取方塊。

   3. 按一下 [SP:Completed] 資料列。

   4. 在資料列中捲動至 [持續時間] 資料行，然後選取 [持續時間] 核取方塊。

8. 在右下角中，按一下 [資料行篩選] 開啟 [編輯篩選] 對話方塊。在 [編輯篩選] 對話方塊中，執行下列步驟：

   1. 在篩選清單中，按一下 [持續時間]。

   2. 在布林運算子視窗中，展開 [大於或等於] 節點、輸入 80 當做值，然後按一下 [確定]。

9. 按一下 [執行] 啟動追蹤。

10. 在工具列上，按一下 [停止選取的追蹤] 或 [暫停選取的追蹤]。

11. 在 [檔案] 功能表上，依序指向 [匯出] 和 [指令碼追蹤定義]，然後按一下 [對於 SQL 追蹤收集組]。

12. 在 [另存新檔] 對話方塊的 [檔案名稱] 方塊中，輸入您想要用於追蹤定義的名稱，然後將它儲存在所需的位置。在這則範例中，檔案名稱與追蹤名稱 (SPgt80) 相同。

13. 當您收到檔案儲存成功的訊息時，請按一下 [確定]，然後關閉 SQL Server Profiler。

根據 SQL Server Profiler 追蹤編寫新收集組的指令碼

1. 在 SQL Server Management Studio 的 [檔案] 功能表上，指向 [開啟]，然後按一下 [檔案]。

2. 在 [開啟檔案] 對話方塊中，找出並開啟您在上一個程序中建立的檔案 (SPgt80)。

   您所儲存的追蹤資訊會在 [查詢] 視窗中開啟，並且合併至您可以執行以建立新收集組的指令碼中。

3. 捲動指令碼並進行下列取代 (這些取代會以指令碼註解文字顯示)：

   • 將 SQLTrace Collection Set Name Here 取代成您想要針對收集組使用的名稱。在這則範例中，請將收集組命名為 SPROC_CollectionSet。

   • 將 SQLTrace Collection Item Name Here 取代成您想要針對收集項使用的名稱。在這則範例中，請將收集項命名為 SPROC_Collection_Item。

4. 按一下 [執行] 執行查詢並建立收集組。

5. 在 [物件總管] 中，確認已建立收集組。若要這樣做，請遵循下列步驟：

   1. 以滑鼠右鍵按一下 [管理]，然後按一下 [重新整理]。

   2. 展開 [管理]，然後展開 [資料收集]。

   [SPROC_CollectionSet] 收集組會與 [系統資料收集組] 節點顯示在相同的層級中。根據預設，此收集組是停用的。

6. 您可以使用 [物件總管] 來編輯 SPROC_CollectionSet 的屬性，例如收集模式和上載排程。請遵循您用來處理資料收集器所提供之系統資料收集組的相同程序。

範例

下列程式碼範例是上述程序所列步驟所產生的最終指令碼。

/*************************************************************/
-- SQL Trace collection set generated from SQL Server Profiler
-- Date: 11/19/2007  12:55:31 AM
/*************************************************************/

USE msdb
GO

BEGIN TRANSACTION
BEGIN TRY

-- Define collection set
-- ***
-- *** Replace 'SqlTrace Collection Set Name Here' in the 
-- *** following script with the name you want
-- *** to use for the collection set.
-- ***
DECLARE @collection_set_id int;
EXEC [dbo].[sp_syscollector_create_collection_set]
    @name = N'SPROC_CollectionSet',
    @schedule_name = N'CollectorSchedule_Every_15min',
    @collection_mode = 0, -- cached mode needed for Trace collections
    @logging_level = 0, -- minimum logging
    @days_until_expiration = 5,
    @description = N'Collection set generated by SQL Server Profiler',
    @collection_set_id = @collection_set_id output;
SELECT @collection_set_id;

-- Define input and output variables for the collection item.
DECLARE @trace_definition xml;
DECLARE @collection_item_id int;

-- Define the trace parameters as an XML variable
SELECT @trace_definition = convert(xml, 
N'<ns:SqlTraceCollector xmlns:ns"DataCollectorType" use_default="0">
<Events>
  <EventType name="Sessions">
    <Event id="17" name="ExistingConnection" columnslist="1,2,14,26,3,35,12" />
  </EventType>
  <EventType name="Stored Procedures">
    <Event id="43" name="SP:Completed" columnslist="1,2,26,34,3,35,12,13,14,22" />
  </EventType>
</Events>
<Filters>
  <Filter columnid="13" columnname="Duration" logical_operator="AND" comparison_operator="GE" value="80000L" />
</Filters>
</ns:SqlTraceCollector>
');

-- Retrieve the collector type GUID for the trace collector type.
DECLARE @collector_type_GUID uniqueidentifier;
SELECT @collector_type_GUID = collector_type_uid FROM [dbo].[syscollector_collector_types] WHERE name = N'Generic SQL Trace Collector Type';

-- Create the trace collection item.
-- ***
-- *** Replace 'SqlTrace Collection Item Name Here' in 
-- *** the following script with the name you want to
-- *** use for the collection item.
-- ***
EXEC [dbo].[sp_syscollector_create_collection_item]
   @collection_set_id = @collection_set_id,
   @collector_type_uid = @collector_type_GUID,
   @name = N'SPROC_Collection_Item',
   @frequency = 900, -- specified the frequency for checking to see if trace is still running
   @parameters = @trace_definition,
   @collection_item_id = @collection_item_id output;
SELECT @collection_item_id;

COMMIT TRANSACTION;
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @ErrorNumber int;
DECLARE @ErrorLine int;
DECLARE @ErrorProcedure nvarchar(200);
SELECT @ErrorLine = ERROR_LINE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
END CATCH;
GO

請參閱

概念

一般 SQL 追蹤收集器型別