SQL Server プロファイラーの使用による SQL トレース コレクション セットの作成
適用対象 
SQL Server
SQL Server では、SQL Server Profiler のサーバー側の SQL Server Profiler のトレース機能を利用し、ジェネリック SQL トレース コレクター型を使用するコレクション セットを作成するためのトレース定義をエクスポートできます。 このプロセスは 2 つの部分で構成されます。
- SQL Server Profiler トレースの作成とエクスポート
- エクスポートされたトレースに基づく新しいコレクション セットのスクリプト化
次の手順のシナリオでは、完了に 80 ミリ秒以上かかるストアド プロシージャに関するデータを収集します。 この手順を完了するには、次の操作を実行できる必要があります。
- SQL Server Profiler を使用したトレースの作成および構成
- SQL Server Management Studio を使用して、クエリを開き、編集し、実行します。
SQL Server Profiler トレースの作成とエクスポート
SQL Server Management Studio で SQL Server Profiler を開きます。 ([ツール] メニューの [SQL Server プロファイラー] を選択します)。
[サーバーへの接続] ダイアログ ボックスで、[キャンセル] を選択します。
このシナリオでは、実行時間の値が既定でミリ秒で表示されるように設定されていることを確認します。 これを行うには、次の手順を実行します。
[Tools](ツール) メニューの [Options](オプション) を選択します。
[表示オプション] 領域で、[実行時間列の値をマイクロ秒で表示する] チェック ボックスがオフになっていることを確認します。
[OK] を選択して、[一般オプション] ダイアログ ボックスを閉じます。
[ファイル] メニューで [新しいトレース] を選択します。
[サーバーへの接続] ダイアログ ボックスで、接続するサーバーを選択して [接続] を選択します。
[トレースのプロパティ] ダイアログ ボックスが表示されます。
全般 タブで、次の手順を実行します:
[トレース名] ボックスに、トレースに使用する名前を入力します。 この例では、トレース名は「
SPgt140」です。[使用するテンプレート]ボックスの一覧でトレースに使用するテンプレートを選択します。 この例では、[TSQL_SPs] を選択します。
[イベントの選択] タブで、次の操作を行います。
トレースに使用するイベントを指定します。 この例では、 [ExistingConnection] と [SP:Completed] を除く [イベント]列のすべてのチェック ボックスをオフにします。
右上隅の [すべての列を表示する] チェック ボックスを選択します。
[SP:Completed] 行を選択します。
行をスクロールして [実行時間] 列を表示し、 [実行時間] チェック ボックスを選択します。
右下隅の [列フィルター] を選択し、[フィルターの編集] ダイアログ ボックスを開きます。 [フィルターの編集] ダイアログ ボックスで、次の操作を行います。
フィルター一覧で、[実行時間] を選択します。
ブール演算子ウィンドウで、[次の値以上] ノードを展開し、値として「
80」と入力し、[OK] を選択します。
[実行] を選択してトレースを開始します。
ツール バーの [選択されたトレースの停止] または [選択されたトレースの一時停止] を選択します。
[ファイル] メニューで [エクスポート]、[トレース定義のスクリプト] の順にポイントし、[SQL トレース コレクション セット] を選択します。
[名前を付けてファイルを保存] ダイアログ ボックスの [ファイル名] ボックスに、トレース定義に使用する名前を入力し、目的の場所に保存します。 この例では、ファイル名はトレース名 (
SPgt140) と同じです。ファイルが正常に保存されたことを通知するメッセージを受信したら [OK] を選択し、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」です。
[実行] を選択して、クエリを実行してコレクション セットを作成します。
[オブジェクト エクスプローラー] で、コレクション セットが作成されたことを確認します。 これを行うには、次の手順を実行します。
[管理] を右クリックし、[更新] をクリックします。
[管理]、 [データ コレクション]の順に展開します。
[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