SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server でステートメントレベルでキャッシュから実行プランを取得

leave a comment

SQL Server でプランキャッシュから実行プランを取得する場合、「sys.dm_exec_query_plan」を用いて、クエリの実行プランの取得を行うことが一般的ではないでしょうか。

このテーブル値関数ではプランハンドルを渡すことで、プランキャッシュから実行プランを取得することができます。
この時、取得される実行プランは「バッチ全体」のプランとなっています。

例として、次のクエリを実行したケースで考えてみます。

SELECT * FROM NATION
SELECT * FROM REGION

 
SQL Server では「バッチ」と「ステートメント」という考えがあり、上記のクエリを実行した場合は次のようになります。
image
単一のクエリを実行した場合は、「1 バッチ / 1 ステートメント」となりますが、複数のステートメントを実行するクエリの場合は「1 バッチ / 複数ステートメント」となります。
 
キャッシュから、実行プランの取得を行う場合、次のような JOIN を書くことが多いのではないでしょうか。

	[sys].[dm_exec_query_stats]
	CROSS APPLY
	[sys].[dm_exec_sql_text]([sql_handle]) AS st
	CROSS APPLY
	[sys].[dm_exec_query_plan]([plan_handle]) AS qp

 
実行結果の一部を抜粋したものが次の画像となります。
image
キャッシュ上では、バッチ内のステートメントのオフセットの情報を持っていますので、バッチの中からどのステートメントの情報なのかをオフセットの情報をもとに分割すると、ステートメント単位で情報を取得することができます。
この情報を取得した際に、該当のプランハンドルの実行プランを取得することができますが、取得した実行プランの XML は次のような内容となっています。

(上記の実行結果の、どちらの「query_plan」も同一の内容となっています)
image
通常の取得方法ですと、バッチ全体の実行プランの取得が行われますので、複数のステートメントで構成されているクエリについては、「このステートメントの実行プランはバッチ全体のどの部分になるのか?」というのを確認する必要が出てきます。
SQL Server 2005 で DMV が実装された当初は、この取得方法のみが使用できたため、公開されている様々なクエリでは、この方法によるキャッシュからの実行プランの取得が多いのではないでしょうか。
この方法は、最新の SQL Server でも有効な取得方法ではありますが、SQL Server 2008 以降では、sys.dm_exec_text_query_plan を使用することもできますので、現在の SQL Server では、このテーブル値関数を使用した情報の取得を行ってもよいかと思います。
「sys.dm_exec_query_plan」では、プランハンドルを引数として設定することができました。
「sys.dm_exec_text_query_plan」では、プランハンドルの他にステートメントのオフセットを引数として設定ができるようになっています。
これにより、「バッチ内の特定のステートメントの実行プランを「nvarchar(max)」で出力」することが可能となっています。

sys.dm_exec_query_plan では、XML で情報が出力されていましたが、sys.dm_exec_text_query_plan では、nvarchar(max) で出力されますので、XML へのキャストが必要となります。

SQL Server では、128 を超えてネストされた XML については、キャスト時にエラーとなりますので、複雑なクエリを実行している際には注意が必要ですが。

メッセージ 6335、レベル 16、状態 102、行 262

XML データ型インスタンスに入れ子になったノードのレベルが深すぎます。許容最大深度は 128 レベルです。

クエリキャッシュを取得する際の JOIN を次のように変更してみます。

	[sys].[dm_exec_query_stats]
	CROSS APPLY
	[sys].[dm_exec_sql_text]([sql_handle]) AS st
	CROSS APPLY
	[sys].[dm_exec_query_plan]([plan_handle]) AS qp
	CROSS APPLY
	[sys].[dm_exec_text_query_plan]([plan_handle], [statement_start_offset], [statement_end_offset]) AS tqp

 
そうすることで、該当のステートメントの実行プランのみを抽出することができますので、「ステートメントと実行プランの対応付け」の手間を省くことができます。
今回、使用したステートメント単位に対応したプランキャッシュからの実行プランの取得については キャッシュされたクエリ情報の取得.sql のクエリに対応を反映していますので、自分でも取得してみたいという方がいらっしゃいましたらこちらのクエリをベースにしてご利用いただければと思います。

Share

Written by Masayuki.Ozawa

2月 17th, 2019 at 1:53 pm

Posted in SQL Server

Tagged with

Leave a Reply