SQL Server - 拡張イベントを使ってスロークエリを取得するクエリ
SQL Serverの拡張イベントを手っ取り早く試すにはこの記事が良いかなと思います。
この記事もわかりやすくSSMSのGUI自体もわかりやすいので、簡単に拡張イベントを使えるかなと思います。簡単に使えてしまうのですが、せっかくなのでクエリをそこそこ理解したいところ。今回はGUIを使わずにクエリだけで次のことを試します。
- 拡張イベントのセッションを作成する
- イベントをキャプチャする
- キャプチャしたイベントを確認する
例としてはありがちですが、いわゆるスロークエリを取得してみます。順番に見ていきましょう。
拡張イベントのセッションを作成する
イベントをキャプチャするためにまずはセッションを作成します。
-- セッションを作成 create event session test_xes on server -- sql_batch_completedイベントをキャプチャする add event sqlserver.sql_batch_completed( -- duration（バッチが完了するまでの時間）が3秒以上のイベントにフィルタする where duration >= 3000000 ), -- rpc_completedイベントをキャプチャする add event sqlserver.rpc_completed( where duration >= 3000000 ) -- イベントデータはring_buffer（メモリ）に出力する add target package0.ring_buffer;
add event句でキャプチャしたい「イベント」を指定します。このクエリではsql_batch_completedイベント・rpc_completedイベントの2つをキャプチャするようにしています。where句は「述語」や「述語フィルタ」と呼ばれる部分です。クエリが完了するまでに3秒以上かかったイベントだけにフィルタをしています。add target句でイベントデータの出力先である「ターゲット」を指定します。このクエリではリングバッファ（メモリ）を指定しています。
またイベントや述語、ターゲットなどは「パッケージ」に含まれています。このクエリのsqlserverやpackage0がパッケージ名です。パッケージは名前空間やコンテナのようなイメージでいいのかなと思います。
参考
イベントをキャプチャする
セッションを開始します。キャプチャを開始するというか、監視を開始するというか。
-- セッション（キャプチャ）を開始 alter event session test_xes on server state = start; -- ちなみに停止するには /* alter event session test_xes on server state = stop; */
セッションが開始されたので、イベントを発生させてキャプチャしたいと思います。そのために次のプログラムを実行します。sql_batch_completedイベントとrpc_completedイベントをキャプチャできるように、3秒以上かかるクエリを2つ実行するプログラムです。
class Program { private static readonly string _connectionString = new SqlConnectionStringBuilder { DataSource = ".", IntegratedSecurity = true, }.ToString(); static void Main(string[] args) { using (var connection = new SqlConnection(_connectionString)) { // 3秒以上かかってsql_batch_completedイベントを発生 var result1 = connection.Query<int>( "waitfor delay '00:00:03'; select 100;").First(); Console.WriteLine(result1); // 結果に意味はない // 3秒以上かかってrpc_completedイベントを発生 var result2 = connection.Query<int>( "waitfor delay '00:00:03'; select @p;", new { p = 99 }).First(); Console.WriteLine(result2); // 結果に意味はない } } }
これで2つのイベントがリングバッファに出力されているはずです。
キャプチャしたイベントを確認する
キャプチャしたイベントのイベントデータを確認します。
出力されたイベントデータは、SSMSだとオブジェクトエクスプローラから[管理]-[拡張イベント]-[セッション]-[対象のセッション]の対象ターゲットを選択して[ターゲットデータを表示]すると確認できます。今回の記事ではターゲットはpackage0.ring_bufferです。
ターゲットデータを表示すると次のようなxmlを確認できます。
<!-- イベントデータ：一部要素や属性を省略 --> <RingBufferTarget> <event name="sql_batch_completed" package="sqlserver" timestamp="2017-08-11T03:54:22.422Z"> <data name="duration"> <type name="uint64" package="package0"></type> <value>3002521</value> </data> <data name="batch_text"> <type name="unicode_string" package="package0"></type> <value><![CDATA[waitfor delay '00:00:03'; select 100;]]></value> </data> </event> <event name="rpc_completed" package="sqlserver" timestamp="2017-08-11T03:54:25.445Z"> <data name="duration"> <type name="uint64" package="package0"></type> <value>3001218</value> </data> <data name="statement"> <type name="unicode_string" package="package0"></type> <value><![CDATA[exec sp_executesql N'waitfor delay ''00:00:03''; select @p;',N'@p int',@p=99]]></value> </data> </event> </RingBufferTarget>
これをクエリで確認するには、次のようなクエリを実行します。
select cast(xe_st.target_data as xml) -- データ（xml） from sys.dm_xe_sessions as xe_s inner join sys.dm_xe_session_targets as xe_st on xe_s.address = xe_st.event_session_address where xe_s.name = N'test_xes';
sys.dm_xe_sessionsを使ってアクティブな（=開始されている）セッションを特定できます。セッションが停止中は取得できません。sys.dm_xe_session_targetsを使ってセッションのターゲットを取得でき、target_dataカラムからイベントデータを取得できます。
ここまでくればあとはxmlデータ型のnodesメソッドやvalueメソッドを使って必要な情報を抜き出すだけです。上記クエリと組み合わせて、xmlデータから必要な情報を抜き出すクエリはこんな感じです。
with xe_e(data) as( select cast(xe_st.target_data as xml) -- データ（xml） from sys.dm_xe_sessions as xe_s inner join sys.dm_xe_session_targets as xe_st on xe_s.address = xe_st.event_session_address where xe_s.name = N'test_xes' ) select node.value('@package', 'nvarchar(10)') as package, node.value('@name', 'nvarchar(20)') as event, node.value('@timestamp', 'datetime2') as timestamp, node.value('(data[@name="duration"]/value)[1]', 'bigint') as duration, node.value('(data[@name="batch_text"]/value)[1]', 'nvarchar(max)') as batch_text, node.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement from xe_e cross apply [data].nodes('/RingBufferTarget/event') as event(node); -- 結果 /* package event timestamp duration batch_text statement ---------- -------------------- --------------------------- -------------------- ---------------------------------------- -------------------------------------------------------------------------------- sqlserver sql_batch_completed 2017-08-11 03:54:22.4220000 3002521 waitfor delay '00:00:03'; select 100; NULL sqlserver rpc_completed 2017-08-11 03:54:25.4450000 3001218 NULL exec sp_executesql N'waitfor delay ''00:00:03''; select @p;',N'@p int',@p=99 */
必要な情報を取得することができました。ただXQueryがよく分からないので手探りです。
参考
T-SQLでn個ずつのグループにわける
前回はn個のグループにわけるクエリを書いてみました。
今回はn個ずつになるようにグルーピングするクエリをROW_NUMBER関数を使って書いてみたいと思います。
ROW_NUMBER (Transact-SQL) | Microsoft Docs
前回と同じように11から20までの数値があるとします。
-- 11から20までの数値があるとして select * from #Sequence; /* Value ----------- 11 12 13 14 15 16 17 18 19 20 */
この10個の数値を4個ずつのグループ（この場合は4個・4個・2個のグループ）にわかれるようにグループ番号を割り振ります。グループ番号はNTILE関数と同じように1からとしてみます。
-- 4個ずつのグループにわける declare @count int = 4; select *, -- グループ番号は1からにする (row_number() over (order By Value) - 1) / @count + 1 as GroupNo -- グループ番号が0からでよければ -- (row_number() over (order By Value) - 1) / @count as GroupNo from #Sequence; /* Value GroupNo ----------- -------------------- 11 1 12 1 13 1 14 1 15 2 16 2 17 2 18 2 19 3 20 3 */
4個ずつのグループにわけることができました。
T-SQLでn個のグループにわける
NTILE関数を使うと簡単にデータをn個のグループにわけることができます。正確には、n個のグループにわけた場合のグループ番号を割り振ると言ったほうがいいかもしれません。
NTILE (Transact-SQL) | Microsoft Docs
NTILE関数を使ったサンプルを書いてみます。
まず適当なデータ、今回は11から20までの10個の数値を用意します。
drop table if exists #Sequence; -- 11から20までの10個の数値を生成 with Source(Value) as( select 11 union all select Value + 1 from Source ) select top 10 * into #Sequence from Source; -- 確認 select * from #Sequence; /* Value ----------- 11 12 13 14 15 16 17 18 19 20 */
この11から20までの数値を3つのグループにわけたいと思います。
-- 3つのグループにわける declare @group int = 3; select *, -- Valueの昇順で3つのグループにわけたグループ番号を割り振る ntile(@group) over (order By Value) as GroupNo from #Sequence; /* Value GroupNo ----------- -------------------- 11 1 12 1 13 1 14 1 15 2 16 2 17 2 18 3 19 3 20 3 */
4個・3個・3個のグループにわけることができました。総数がグループ数で割り切れない場合、NTILE関数は最初のグループから多く割り振っていきます。
SQL Server - 拡張イベントで収集できるイベントデータを確認するクエリ
拡張イベントで収集できるイベントデータを確認するクエリです。
動的管理ビューを3つ使います。sys.dm_xe_packages、sys.dm_xe_objects、sys.dm_xe_object_columnsの3つ。
| 動的管理ビュー | 概要 |
|---|---|
| sys.dm_xe_packages (Transact-SQL) | パッケージ一覧。イベントはパッケージによってグルーピングされています。 |
| sys.dm_xe_objects (Transact-SQL) | イベント（オブジェクト）一覧。収集したいイベントを確認できます。 |
| sys.dm_xe_object_columns (Transact-SQL) | オブジェクトのカラム一覧。イベントが収集されるときにどんなデータが含まれるかを確認できます。イベントデータというかイベントの属性というか。 |
収集できるイベントデータを確認するクエリがこちらです。例としてsql_batch_completedイベントのイベントデータを確認しています。
-- sql_batch_completedイベントのイベントデータを確認する select xe_oc.name as column_name, xe_oc.column_type as column_type, xe_oc.type_name as type_name, xe_oc.description as column_description from sys.dm_xe_packages as xe_p inner join sys.dm_xe_objects as xe_o on xe_p.guid = xe_o.package_guid inner join sys.dm_xe_object_columns as xe_oc on xe_o.package_guid = xe_oc.object_package_guid and xe_o.name = xe_oc.object_name where -- sys.dm_xe_objectsにはアクション・ターゲットなどイベント以外も含まれるので -- object_type = N'event'でフィルタ xe_o.object_type = N'event' and xe_p.name = N'sqlserver' and xe_o.name = N'sql_batch_completed';
結果です。（空白などは調整してあります。）
column_name column_type type_name column_description ------------------- ------------- ------------------ ------------------------------------------------------------------------------------- UUID readonly guid_ptr グローバル一意 ID VERSION readonly uint8 イベント スキーマのバージョン CHANNEL readonly etw_channel ETW チャネル KEYWORD readonly keyword_map 関連キーワード collect_batch_text customizable boolean 0 に設定すると、collect_batch_text によって batch_text の収集が無効になります。既定では、batch_text 列が生成されます。 cpu_time data uint64 バッチによって使用される CPU 時間 (ミリ秒)。 duration data uint64 バッチ完了にかかった時間 (マイクロ秒)。 physical_reads data uint64 バッチによって発行された、物理ページの読み取り回数。 logical_reads data uint64 バッチによって発行された、論理ページの読み取り回数。 writes data uint64 NULL row_count data uint64 バッチによって返された行数。 result data rpc_return_result バッチの実行結果を示します。 batch_text data unicode_string 実行されているバッチのテキスト。
column_typeがdataの行が実行時に収集されるイベントデータです。column_typeがcustomizableの行は収集するかどうかを設定できます。readonlyは固定値？まだよくわからず。
ついでにrpc_completedイベントのイベントデータも確認しておきます。where句以外は上記と同じクエリです。
-- rpc_completedイベントのイベントデータを確認する select xe_oc.name as column_name, xe_oc.column_type as column_type, xe_oc.type_name as type_name, xe_oc.description as column_description from sys.dm_xe_packages as xe_p inner join sys.dm_xe_objects as xe_o on xe_p.guid = xe_o.package_guid inner join sys.dm_xe_object_columns as xe_oc on xe_o.package_guid = xe_oc.object_package_guid and xe_o.name = xe_oc.object_name where xe_o.object_type = N'event' and xe_p.name = N'sqlserver' and xe_o.name = N'rpc_completed';
結果。
column_name column_type type_name column_description -------------------------- ------------- ------------------------- -------------------------------------------------------------------------------------------------------------- UUID readonly guid_ptr グローバル一意 ID VERSION readonly uint8 イベント スキーマのバージョン CHANNEL readonly etw_channel ETW チャネル KEYWORD readonly keyword_map 関連キーワード collect_statement customizable boolean 0 に設定すると、collect_statement によって statement の収集が無効になります。既定では、statement 列が生成されます。 collect_data_stream customizable boolean 1 に設定すると、collect_data_stream によって data_stream の収集が有効になります。既定では、data_stream 列は生成されません。 collect_output_parameters customizable boolean 1 に設定すると、collect_output_parameters によって output_parameters の収集が有効になります。既定では、output_parameters 列は生成されません。 cpu_time data uint64 NULL duration data uint64 リモート プロシージャ呼び出しが完了するまでにかかった時間 (マイクロ秒)。 physical_reads data uint64 リモート プロシージャ呼び出しによって発行された、物理ページの読み取り回数。 logical_reads data uint64 リモート プロシージャ呼び出しによって発行された、論理ページの読み取り回数。 writes data uint64 NULL result data rpc_return_result リモート プロシージャ呼び出しからの戻り値。 row_count data uint64 リモート プロシージャ呼び出しによって返された行数。 connection_reset_option data connection_reset_option 接続のリセットに使用されたオプション。このフィールドは、接続のリセット リモート プロシージャ呼び出しでのみ使用できます。 object_name data unicode_string リモート プロシージャ呼び出しで参照されていたオブジェクトの名前。 statement data unicode_string リモート プロシージャ呼び出しで実行されたステートメントのテキスト。 data_stream data binary_data トレースの再生に使用される、リモート プロシージャ呼び出しのバイナリ ストリーム。 output_parameters data xml リモート プロシージャ呼び出しから返された出力パラメーター。
SQL Server 2016で確認しています。
SQL Server - トレースイベントに対応する拡張イベントを確認するクエリ
拡張イベントを調べています。勉強した小ネタを残していこうと思います。
今回はトレースイベントに対応する拡張イベントを確認するクエリです。sys.trace_eventsとsys.trace_xe_event_mapを使います。
例として次の2つのトレースイベントに対応する拡張イベントを取得したいとします。
クエリは次のような感じに。
-- トレースイベントに対応する拡張イベントを確認する select te.trace_event_id as te_event_id, -- トレースイベントID te.name as te_event_name, -- トレースイベント名 xe.package_name as xe_package_name, -- 拡張イベントパッケージ名 xe.xe_event_name as xe_event_name -- 拡張イベント名 from sys.trace_events as te inner join sys.trace_xe_event_map as xe on te.trace_event_id = xe.trace_event_id where te.name in (N'RPC:Completed', N'SQL:BatchCompleted'); -- 結果 /* te_event_id te_event_name xe_package_name xe_event_name ------------ -------------------- -------------------- -------------------- 10 RPC:Completed sqlserver rpc_completed 12 SQL:BatchCompleted sqlserver sql_batch_completed */
いずれは拡張イベントでいわゆるスロークエリを取得したいと思っているのでこの2つのイベントを例として選んでみました。
