使用 SQL Server Profiler 來建立 SQL 追蹤收集組 (SQL Server Management Studio)

發行項
06/25/2013

在 SQL Server 2012 中，您可以利用 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. 在 [顯示選項] 區域中，請確定已清除 [以百萬分之一秒顯示 [持續時間] 資料行的值 (僅限 SQL Server 2005 或更新版本)] 核取方塊。
3. 按一下 [確定] 關閉 [一般選項] 對話方塊。
在 [檔案] 功能表上，按一下 [新增追蹤]。
在 [連接到伺服器] 對話方塊中，選取您想要連接的伺服器，然後按一下 [連接]。

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

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

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

您所儲存的追蹤資訊會在 [查詢] 視窗中開啟，並且合併至您可以執行以建立新收集組的指令碼中。
捲動指令碼並進行下列取代 (這些取代會以指令碼註解文字顯示)：
- 將 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/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 Server Profiler 來建立 SQL 追蹤收集組 (SQL Server Management Studio)

建立和匯出 SQL Server Profiler 追蹤

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

範例

其他資源