使用 SQL Server Profiler 建立 SQL 追蹤收集組

發行項
03/05/2024

適用於：SQL Server

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

建立和匯出 SQL Server Profiler 追蹤。
根據匯出的追蹤，編寫新收集組的指令碼。

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

使用 SQL Server Profiler 來建立及設定追蹤。
使用 SQL Server Management Studio 開啟、編輯和執行查詢。

建立和匯出 SQL Server Profiler 追蹤

在 SQL Server Management Studio 中開啟 SQL Server Profiler。 (在 [工具] 功能表上，選取 [SQL Server Profiler])。
在 [連線至伺服器] 對話方塊中，選取 [取消]。
在這個狀況中，請確定持續時間值設定為以毫秒顯示 (預設值)。若要執行此動作，請依照下列步驟執行：
1. 在 [工具] 功能表上，選取 [選項]。
2. 在 [顯示選項] 區域中，請確定已清除 [顯示 [持續時間] 資料行中的值 (以百萬分之一秒為單位)] 核取方塊。
3. 選取 [確定] 以關閉 [一般選項] 對話框。
在 [檔案] 功能表上選取 [新增追蹤]。
在 [連接到伺服器] 對話方塊中，選取您想要連接的伺服器，然後選取 [連接]。

會出現 [追蹤屬性] 對話方塊。
在 [一般] 索引標籤上，執行下列動作：
1. 在 [追蹤名稱] 方塊中，輸入您想要用於追蹤的名稱。在此範例中，追蹤名稱為 SPgt140。
2. 在 [使用範本] 清單中，選取要用於追蹤的範本。在此範例中，選取 [TSQL_SPs]。
在 [事件選取範圍] 索引標籤上，執行下列動作：
1. 識別要用於追蹤的事件。在這則範例中，請清除 [事件] 資料行中的所有核取方塊，但 [ExistingConnection] 和 [SP:Completed] 除外。
2. 在右下角中，選取 [顯示所有資料行] 核取方塊。
3. 選取 [SP:Completed] 資料列。
4. 在資料列中捲動至 [持續時間] 資料行，然後選取 [持續時間] 核取方塊。
在右下角中，選取 [資料行篩選] 開啟 [編輯篩選] 對話方塊。在 [編輯篩選] 對話方塊中，執行下列步驟：
1. 在篩選清單中，選取 [持續時間]。
2. 在布林運算子視窗中，展開 [大於或等於] 節點，輸入 80 作為值，然後選取 [確定]。
選取 [執行] 啟動追蹤。
在工具列上，選取 [停止選取的追蹤] 或 [暫停選取的追蹤]。
在 [檔案] 功能表上，依序指向 [匯出] 和 [指令碼追蹤定義]，然後選取 [對於 SQL 追蹤收集組]。
在 [另存新檔] 對話方塊的 [檔案名稱] 方塊中，輸入您想要用於追蹤定義的名稱，然後將它儲存在所需的位置。在這則範例中，檔案名稱與追蹤名稱 (SPgt140) 相同。
當您收到檔案儲存成功的訊息時，請選取 [確定]，然後關閉 SQL Server Profiler。

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

在 SQL Server Management Studio 中的 [檔案] 功能表中，指向 [開啟]，然後選取 [檔案]。
在 [開啟檔案] 對話方塊中，找出並開啟您在上一個程序中建立的檔案 (SPgt140)。

您所儲存的追蹤資訊會在 [查詢] 視窗中開啟，並且合併至您可以執行以建立新收集組的指令碼中。
捲動指令碼並進行下列取代 (這些取代會以指令碼註解文字顯示)：
- 將 SQLTrace Collection Set Name Here 取代成您想要針對收集組使用的名稱。在這則範例中，請將收集組命名為 SPROC_CollectionSet。
- 將 SQLTrace Collection Item Name Here 取代成您想要針對收集項使用的名稱。在這則範例中，請將收集項命名為 SPROC_Collection_Item。
選取 [執行] 來執行查詢並建立收集組。
在 [物件總管] 中，確認已建立收集組。若要執行此動作，請依照下列步驟執行：
1. 以滑鼠右鍵按一下 [管理]，然後選取 [重新整理]。
2. 展開 [管理]，然後展開 [資料收集]。
[SPROC_CollectionSet] 收集組會與 [系統資料收集組] 節點顯示在相同的層級中。根據預設，此收集組是停用的。
您可以使用 [物件總管] 來編輯 SPROC_CollectionSet 的屬性，例如收集模式和上傳排程。請遵循您用來處理資料收集器所提供之系統資料收集組的相同程序。

範例

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

/*************************************************************/
-- SQL Trace collection set generated from SQL Server Profiler
-- Date: 11/19/2022  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 Server Profiler 建立 SQL 追蹤收集組

建立和匯出 SQL Server Profiler 追蹤

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

範例

意見反應

意見反應

其他資源