使用 SQL Server Profiler 來建立 SQL 追蹤收集組 (SQL Server Management Studio)
在 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])。
在 [連接到伺服器] 對話方塊中,按一下 [取消]。
在這個狀況中,請確定持續時間值設定為以毫秒顯示 (預設值)。 若要這樣做,請遵循下列步驟:
在 [工具] 功能表上,按一下 [選項]。
在 [顯示選項] 區域中,請確定已清除 [以百萬分之一秒顯示 [持續時間] 資料行的值 (僅限 SQL Server 2005 或更新版本)] 核取方塊。
按一下 [確定] 關閉 [一般選項] 對話方塊。
在 [檔案] 功能表上,按一下 [新增追蹤]。
在 [連接到伺服器] 對話方塊中,選取您想要連接的伺服器,然後按一下 [連接]。
將出現 [追蹤屬性] 對話方塊。
在 [一般] 索引標籤上,執行下列動作:
在 [追蹤名稱] 方塊中,輸入您想要用於追蹤的名稱。 在這則範例中,追蹤名稱為 SPgt80。
在 [使用範本] 清單中,選取要用於追蹤的範本。 在這則範例中,請按一下 [TSQL_SPs]。
在 [事件選取範圍] 索引標籤上,執行下列動作:
識別要用於追蹤的事件。 在這則範例中,請清除 [事件] 資料行中的所有核取方塊,但 [ExistingConnection] 和 [SP:Completed] 除外。
在右下角中,選取 [顯示所有資料行] 核取方塊。
按一下 [SP:Completed] 資料列。
在資料列中捲動至 [持續時間] 資料行,然後選取 [持續時間] 核取方塊。
在右下角中,按一下 [資料行篩選] 開啟 [編輯篩選] 對話方塊。 在 [編輯篩選] 對話方塊中,執行下列步驟:
在篩選清單中,按一下 [持續時間]。
在布林運算子視窗中,展開 [大於或等於] 節點、輸入 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。
按一下 [執行] 執行查詢並建立收集組。
在 [物件總管] 中,確認已建立收集組。 若要這樣做,請遵循下列步驟:
以滑鼠右鍵按一下 [管理],然後按一下 [重新整理]。
展開 [管理],然後展開 [資料收集]。
[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