SE の雑記

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

ストアドプロシージャとパラメーター化クエリのコンパイル時のパラメーター

leave a comment

少しわかりにくいタイトルですが。
ストアドプロシージャやパラメーター化クエリは初回コンパイルされた後は再利用されることになります。

初回コンパイル時にどのようなパラメーターを使用してコンパイルされたのかを確認する方法をまとめてみたいと思います。

以下のような一つのパラメーターを引数として実行されるストアドプロシージャがあります。
image

このストアドプロシージャの初回実行時には初回実行時に使用されたパラメーターを元にコンパイルされ、実行プランが生成されそれが再利用されていくことになります。

どのような実行プランが作成されたかをキャッシュから確認する場合は以下のようなクエリを実行することで情報が取得できます。

SELECT
OBJECT_NAME(object_id),
database_id,
type_desc,
cached_time,
last_execution_time,
execution_count,
query_plan,
text
FROM sys.dm_exec_procedure_stats
cross apply
sys.dm_exec_query_plan(plan_handle)
cross apply
sys.dm_exec_sql_text(sql_handle)
where object_id = OBJECT_ID(N’usp_test’)

 

取得した結果の query_plan の XML のリンクからキャッシュされているプランをグラフィカルに表示することができます。この時に最終の結果のアイコンを選択し、プロパティの [Parameter List] を確認することでコンパイル時に使用されたパラメーター (Parameter Compiled Value) を確認することができます。
image

ストアドプロシージャを実際の実行プランを表示して実行した場合には、キャッシュされているプランのパラメーター (Parameter Compiled Value) と実行時に使用したパラメーター (Parameter Runtime Value) が表示されます。
image

この情報はパラメーター化クエリでも同じように取得することができます。

以下のようなクエリを実行した場合は、

DECLARE @val uniqueidentifier
SET @val = NEWID()
EXEC sp_executesql N’SELECT * FROM Table_1 WHERE Col1 = @param1′, N’@param1 uniqueidentifier’, @param1 = @val

以下のようなクエリで情報を取得するうことができます。
# ストアドプロシージャの情報であれば、sys.dm_exec_procedure_stats から取得できますが、パラメーター化クエリに関しては sys.dm_exec_query_stats から取得します。ストアドプロシージャも sys.dm_exec_query_stats  から取得できますので、こちらで統一してしまってもよいかもしれないですね。

SELECT
creation_time,
last_execution_time,
execution_count,
query_plan,
text
FROM sys.dm_exec_query_stats
cross apply
sys.dm_exec_query_plan(plan_handle)
cross apply
sys.dm_exec_sql_text(sql_handle)

image

キャッシュされているプランがどのようなパラメーターを使用して実行されたのかを確認したい場合には Parameter List から確認するとよいかと。
検証環境で再現テストをしたい場合などにもこの辺を把握しておくと役に立ちそうですね。

Share

Written by Masayuki.Ozawa

7月 30th, 2013 at 11:20 pm

Posted in SQL Server

Tagged with

Leave a Reply