読者です 読者をやめる 読者になる 読者になる

小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【SQL server】クエリ統計の履歴レポートについて

SQL Server

データコレクションの機能で、「クエリ統計の履歴」を実行し、
レポート表示させる場合の元スクリプトとテーブルについて
確認してみました。
内容に誤りがある場合は、ご指摘いただけると幸いです。
※変数の値はそれぞれの対象となるSQLによって異なります。
レポートに出力されている各項目におけるinputデータやモジュールから
把握することで、レポート内容をより正確に読み取ることを趣旨と
しています。

 

 

◆「クエリ統計の履歴」の初期表示時のスクリプト

exec snapshots.rpt_top_query_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016', -- インスタンス
@end_time='2016-07-13 16:30:02.500', -- データコレクション収集最終時間
@time_window_size=240, -- レポートの期間(デフォルトは240分)
@order_by_criteria=N'CPU', -- レポートのTOP10のソート(デフォルトはCPUtime)
@database_name=NULL

◆「クエリ統計の履歴」のTOPxの特定のクエリを選択した場合のスクリプト
※レポート名は「クエリの詳細」
ここでは、対象SQLで使用されたの全ての実行プランの場合の以下値を合計して出力しているようです。
【出力項目】CPU、実行時間、物理読み取り、論理書き込み、実行回数
exec snapshots.rpt_query_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@end_time='2016-07-13 15:49:58',
@time_window_size=240,
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116

f:id:utiowa:20160720001745p:plain

ここでは、対象SQLで使用されたそれぞれの実行プランの場合の以下値を合計してレポート期間で割った
1分当たりの各値を出力しているようです。
exec snapshots.rpt_query_plan_stats
@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@end_time='2016-07-13 15:49:58',
@time_window_size=240,
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116,
@order_by_criteria=N'CPU',
@plan_handle_str=NULL,
@plan_creation_time=NULL


◆「クエリの詳細」レポートにおけるSQLステートメント全文出力スクリプト

exec sp_executesql @stmt=N'DECLARE @sql_handle varbinary(64)
SET @sql_handle = snapshots.fn_hexstrtovarbin (@sql_handle_str)
SELECT TOP 1 sql.*
FROM core.snapshots AS snap
CROSS APPLY snapshots.fn_get_query_text (snap.source_id, @sql_handle, @statement_start_offset, @statement_end_offset) AS sql
WHERE
snap.instance_name = @instance_name
AND snap.collection_set_uid = @collection_set_uid
AND sql.query_text IS NOT NULL',
@params=N'@sql_handle_str NVarChar(max),
@statement_start_offset Int,
@statement_end_offset Int,
@instance_name NVarChar(max),
@collection_set_uid NVarChar(max)',
@sql_handle_str=N'0x0200000031e4602070c1e890afdfeaf91d35396449b4f4a60000000000000000000000000000000000000000',
@statement_start_offset=0,
@statement_end_offset=1116,@instance_name=N'YUUSUKE-VAIO\INS_NISHI2016',
@collection_set_uid=N'2DC02BD6-E230-4C05-8516-4E8C0EF21F95'

 

f:id:utiowa:20160720001815p:plain

f:id:utiowa:20160720001825p:plain


◆各プランの番号を押下して、「クエリプランの詳細」レポートを出力
「グラフィカルなクエリ実行プランの表示」を押下した場合のスクリプト
exec sp_executesql N'SELECT [query_plan]
FROM [snapshots].[notable_query_plan]
WHERE [plan_handle]=[snapshots].[fn_hexstrtovarbin](@PlanHandle)
AND [sql_handle]=[snapshots].[fn_hexstrtovarbin](@SqlHandle)
AND [statement_start_offset]=@StartOffset
AND [statement_end_offset]=@EndOffset
AND [creation_time]=@CreationTime',
N'@PlanHandle varchar(90),
@SqlHandle varchar(90),
@StartOffset int,
@EndOffset int,
@CreationTime datetimeoffset(7)',
@PlanHandle=N'0x06000B0031E46020B0EE6D667001000001000000000000000000000000000000000000000000000000000000',
@SqlHandle='0x0200000031E4602070C1E890AFDFEAF91D35396449B4F4A60000000000000000000000000000000000000000',
@StartOffset=0,
@EndOffset=1116,
@CreationTime='2016-07-13 15:36:44.4730000 +00:00'

f:id:utiowa:20160720001837p:plain

 

 

広告を非表示にする