TL;DR
DMF(動的管理関数)の sys.dm_exec_query_statistics_xml を使うことで現在実行中のSQLのプランを実際に読み込んだ行数含めて取得できます。
現在どこまで実行されたか、どこで時間がかかっているかなどが確認できます。
実行プランについて
データベースのクエリチューニングを行うときに参考になるのが実行プランです。
実行プランにはクエリを実行するにあたってどのような順番・方法でテーブルにアクセスするかなどの情報が含まれています。
ここで推定の実行プランと実際の実行プランについて軽く触れさせていただきます。
推定の実行プランについて
統計情報やインデックスの有無などを基に、どのように実行すれば効率良くできるかをオプティマイザが計算して作成した手順書のようなものが推定実行プランです。
SSMS(SQL Server Managament Studio)からはクエリエディタにクエリを入力した状態で下図のボタンを押すことで取得することができます。
このボタンを押しても実際に実行されるわけではないので、INSERT文に対して行っても行の追加はされないし、パラメータが正しく設定されていなくても推定実行プランを取得することが可能です。
-- 実行はされないので [Sales].[SalesReason]に行は増えない
INSERT INTO [Sales].[SalesReason]([Name],[ReasonType],[ModifiedDate])
VALUES ('sample', 'Other', GETDATE());
-- ストアドの必須パラメータが未指定でも取得可能
EXEC [dbo.SampleProcedure]
実際の実行プランについて
実際の実行プランは実際に実行した結果を基にして作られた、正確性の高い情報が含まれた実行プランです。
SSMSからは下記のボタンを押して、実行ボタンを押すと取得できます。
実際に実行する必要があるので実行時エラーなどが出ない状態とする必要があります。
-- [Sales].[SalesReason]に1行追加される
INSERT INTO [Sales].[SalesReason]([Name],[ReasonType],[ModifiedDate])
VALUES ('sample', 'Other', GETDATE());
-- ストアドの必須パラメータが未指定だと実行時エラーとなる
EXEC [dbo.SampleProcedure] 100 -- 必須パラメータが必要
同じクエリに対して推定実行プランと実際の実行プランを取得した結果が次の図です。
推定実行プランと比べて確認できる情報が増えていることがわかります。
稼働中のSQL Serverでの実行プランの取り方
過去に実行されたクエリの推定の実行プランの取り方
クエリを実行するときに実行プランが生成されプランキャッシュに保存されます。
そのキャッシュを動的管理ビュー dm_exec_query_plan を使うことで取得することが可能です。
SELECT
*
FROM
Sales.SalesOrderDetail AS sod
LEFT OUTER JOIN Sales.SalesOrderHeader AS soh
ON soh.SalesOrderID = sod.SalesOrderID
LEFT OUTER JOIN Sales.SalesOrderHeaderSalesReason AS sohsr
ON sohsr.SalesOrderID = sod.SalesOrderID
LEFT OUTER JOIN Sales.SalesReason AS sr
ON sr.SalesReasonID = sohsr.SalesReasonID
-- __TEST_TARGET__ <- 検索時対象をわかりやすくするための文字列
WHERE
SpecialOfferID = 5
SELECT TOP(100)
qs.[creation_time] AS [キャッシュされた時間]
, qs.[last_execution_time] AS [最後に実行された時間]
, SUBSTRING(st.[text], (qs.[statement_start_offset] / 2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH(st.[text])
ELSE qs.[statement_end_offset]
END - qs.[statement_start_offset]) / 2) + 1) AS [クエリ]
, qp.[query_plan] AS [実行計画]
FROM
sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS qs
ON p.[plan_handle] = qs.[plan_handle]
CROSS APPLY sys.dm_exec_sql_text(p.[plan_handle]) AS st
CROSS APPLY sys.dm_exec_query_plan(p.[plan_handle]) AS qp
WHERE
st.[text] LIKE '%__TEST_TARGET__%' -- 検索用文字列
AND st.[text] NOT LIKE '%dm_exec_cached_plans%'
ORDER BY
qs.[max_elapsed_time] DESC
- 実行結果
この方法でプランキャッシュに保存された推定実行プランを取得できますが、推定実行プランだと分析しにくいケースが存在します。
推定行数と実際の行数がずれているパターンです。
次の画像は同じクエリを実行した時の推定実行プランと実際の実行プランをPlan Explorerで見やすくした画像です。
推定実行プランだとScanなどが見られる部分もありますが、読み込む行数は少なく見えます。しかし実際に読み込まれた行数は多く、乖離が生じてしまっています。
- 推定実行プラン
- 実際の実行プラン
実行中のクエリの実際の実行プランの取り方
SQL Server 2016 SP1からDMF(動的管理関数) のsys.dm_exec_query_statistics_xml が追加されました。
これを使うことで現在実行中のSQLのプランを、その時までに実際に読み込んだ行数含めて取得できます。
SQL Serverのバージョンによって使用するための条件が異なります。
- SQL Server2019以降はデフォルトで有効となっています。
- それ以前は トレースフラグ7412 を有効にするなどの対応が必要です。
この関数は引数にsession_idを渡してあげることで実行可能であり、例えば次のような使い方ができます。
exec [dbo].[uspSelectOrderBySpecialOffer] 1
SELECT TOP 100
der.session_id as spid
,DB_NAME(der.database_id) AS db_name
,der.status
,datediff(s, der.start_time, GETDATE()) as time_sec
,dest.text as command_text
, deqsx.query_plan
FROM
sys.dm_exec_requests der
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS dest
OUTER APPLY sys.dm_exec_query_statistics_xml(der.session_id) AS deqsx
WHERE
dest.text like '%uspSelect%'
結果は次の通りであり、約200秒経過した時点での、実際に実行された行数が含まれたプランが取得できています。
また、この実行プランにはパラメータの情報も含まれています。
コンパイルされた時は @SpecialOfferID に対して5が指定されたのに対し、スロークエリの時は1が指定されています。
このことからパラメータスニッフィングの可能性も推測できます。
おわりに
スロークエリの分析が捗る非常に便利な機能です。
推定実行プランを見るよりも原因の目処がつけやすく、原因分析の確実性も上げることができます。
機能が有効化されていれば簡単に確認できるので、実行中のスロークエリが観測されたらまずはここから調べていくのが良さそうです。