sys.dm_exec_query_plan

更新 : 2006 年 12 月 12 日

プラン ハンドルで指定されたバッチのプラン表示を XML 形式で返します。プラン ハンドルで指定するプランは、キャッシュ内のもの、または現在実行中のものを指定できます。

プラン表示の XML スキーマは、Microsoft Web サイトで公開されているので、そこから入手できます。また、SQL Server 2005 がインストールされている次のディレクトリからも入手できます。

\\Program Files\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

Transact-SQL 構文表記規則

構文

sys.dm_exec_query_plan ( plan_handle )

引数

• plan_handle
  キャッシュ内または現在実行中のバッチのクエリ プランを一意に識別します。

  plan_handle のデータ型は varbinary(64) です。plan_handle は次の動的管理オブジェクトから取得できます。

  sys.dm_exec_cached_plans

  sys.dm_exec_query_stats

  sys.dm_exec_requests

返されるテーブル

列名	データ型	説明
dbid	smallint	このプランに対応する Transact-SQL ステートメントがコンパイルされたときに有効であったコンテキスト データベースの ID。アドホック バッチおよび準備されたバッチの場合、この列の値は NULL です。 NULL 値は許可されます。
objectid	int	ストアド プロシージャやユーザー定義関数など、クエリ プランのオブジェクトの ID。アドホック バッチおよび準備されたバッチの場合、この列の値は NULL です。 NULL 値は許可されます。
number	smallint	ストアド プロシージャに付けられた番号 (整数)。たとえば、orders アプリケーションのプロシージャ グループの名前は、orderproc;1、orderproc;2 のように指定されることがあります。アドホック バッチおよび準備されたバッチの場合、この列の値は NULL です。 NULL 値は許可されます。
encrypted	bit	対応するプロシージャが暗号化されているかどうか。 0 = 暗号化されていない 1 = 暗号化されている NULL 値は許可されません。
query_plan	xml	plan_handle で指定したクエリ実行プランを表す、コンパイル時のプラン表示。プラン表示は XML 形式です。アドホック Transact-SQL ステートメント、ストアド プロシージャ コール、ユーザー定義関数コールなどを含むバッチごとに、1 つのプランが生成されます。 NULL 値は許可されます。

解説

次の場合、sys.dm_exec_query_plan で返されるテーブルの query_plan 列にはプラン表示の出力は返されません。

• plan_handle を使用して指定したクエリ プランがプラン キャッシュから削除された場合、返されるテーブルの query_plan 列は NULL になります。たとえば、プラン ハンドルがキャプチャされてから sys.dm_exec_query_plan に使用されるまでに遅延が生じると、クエリ プランがキャッシュから削除されることがあります。
• 一括操作ステートメントや、8 KB よりも大きなサイズの文字列リテラルを含むステートメントなど、キャッシュされない Transact-SQL ステートメントがいくつかあります。これらのステートメントはキャッシュに存在しないため、バッチが現在実行中でない限り、sys.dm_exec_query_plan を使用してこれらのステートメントの XML プラン表示を取得することはできません。
• Transact-SQL バッチまたはストアド プロシージャに、ユーザー定義関数への呼び出しや動的 SQL への呼び出し (EXEC (string) の使用など) が含まれている場合、このようなバッチやストアド プロシージャに対する sys.dm_exec_query_plan によって返されるテーブルには、ユーザー定義関数に関するコンパイル済みの XML プラン表示は含まれません。代わりに、sys.dm_exec_query_plan を個別に呼び出して、ユーザー定義関数に対応するプラン ハンドルを取得する必要があります。

アドホック クエリで簡易または強制のパラメータ化を行う場合、query_plan 列にはステートメント テキストのみが格納され、実際のクエリ プランは格納されません。クエリ プランを返すには、sys.dm_exec_query_plan を呼び出して、準備されたパラメータ化クエリのプラン ハンドルを取得します。クエリがパラメータ化されたかどうかを判断するには、sys.syscacheobjects ビューの sql 列、または sys.dm_exec_sql_text 動的管理ビューの text 列を参照します。パラメータ化の詳細については、「簡易パラメータ化」および「強制パラメータ化」を参照してください。

xml データ型で許可される入れ子のレベルの制限により、sys.dm_exec_query_plan は、入れ子になった要素のレベルが 128 以上のクエリ プランを返すことができません。SQL Server 2005 の以前のバージョンでは、この条件が原因でクエリ プランが返されず、エラー 6335 が生成されます。Service Pack 2 では、query_plan 列に NULL が返されます。sys.dm_exec_text_query_plan 動的管理関数を使用すると、クエリ プランの出力をテキスト形式で返すことができます。

権限

sys.dm_exec_query_plan を実行するには、ユーザーは sysadmin 固定サーバー ロールのメンバであるか、サーバーの VIEW SERVER STATE 権限が与えられている必要があります。

例

次の例は、sys.dm_exec_query_plan 動的管理ビューの使用方法を示しています。

XML プラン表示を表示するには、SQL Server Management Studio のクエリ エディタで次のクエリを実行した後、sys.dm_exec_query_plan によって返されるテーブルの query_plan 列で [ShowPlanXML] をクリックします。XML プラン表示は、Management Studio の概要ペインに表示されます。XML プラン表示をファイルに保存するには、query_plan 列で [ShowPlanXML] を右クリックし、[結果に名前を付けて保存] をクリックして、<file_name>.sqlplan の形式でファイル名を指定します。たとえば、MyXMLShowplan.sqlplan のように指定します。

A. 実行速度の遅い Transact-SQL クエリまたは Transact-SQL バッチに対して、キャッシュされたクエリ プランを取得する

SQL Server 2005 では、アドホック バッチ、ストアド プロシージャ、ユーザー定義関数などの各種 Transact-SQL バッチのクエリ プランは、プラン キャッシュと呼ばれるメモリ領域にキャッシュされます。キャッシュされたそれぞれのクエリ プランは、プラン ハンドルと呼ばれる一意識別子で識別されます。sys.dm_exec_query_plan 動的管理ビューでは、このプラン ハンドルを指定して、特定の Transact-SQL クエリまたはバッチの実行プランを取得できます。

Transact-SQL クエリまたは Transact-SQL バッチが、特定の SQL Server との接続において長時間実行されている場合は、このクエリやバッチの実行プランを取得して、遅延の原因を調べることができます。次の例では、実行速度の遅いクエリまたはバッチに対して XML プラン表示を取得する方法を示します。

メモ :
この例を実行するには、session_id と plan_handle の値を、使用しているサーバー固有の値に置き換えてください。

まず、sp_who ストアド プロシージャを使用して、クエリまたはバッチを実行しているプロセスのサーバー プロセス ID (SPID) を取得します。

USE master;
GO
exec sp_who;
GO

sp_who によって返される結果セットでは、SPID の値が 54 であることが示されます。sys.dm_exec_requests 動的管理ビューで、この SPID を使用して次のクエリを実行すると、プラン ハンドルを取得できます。

USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO

sys.dm_exec_requests から返されるテーブルでは、実行速度の遅いクエリやバッチのプラン ハンドルが 0x06000100A27E7C1FA821B10600 であることが示されます。次のように sys.dm_exec_query_plan の plan_handle 引数にこの値を指定して実行すると、XML 形式の実行プランを取得できます。実行速度の遅いクエリまたはバッチの XML 形式の実行プランは、sys.dm_exec_query_plan によって返されるテーブルの query_plan 列に格納されます。

USE master;
GO
SELECT * FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);
GO

B. プラン キャッシュからすべてのクエリ プランを取得する

プラン キャッシュにあるすべてのクエリ プランのスナップショットを取得するには、sys.dm_exec_cached_plans 動的管理ビューに対してクエリを実行し、キャッシュにあるすべてのクエリ プランのプラン ハンドルを取得します。プラン ハンドルは、sys.dm_exec_cached_plans の plan_handle 列に格納されます。その後、次のように CROSS APPLY 演算子を使用して、プラン ハンドルを sys.dm_exec_query_plan に渡します。現在プラン キャッシュにある各プランの XML プラン表示の出力は、返されるテーブルの query_plan 列に格納されます。

USE master;
GO
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO

C. サーバーで収集されたクエリ統計情報に関連するすべてのクエリ プランをプラン キャッシュから取得する

現在プラン キャッシュにあるクエリ プランのうち、サーバーで統計情報が収集されたすべてのクエリ プランのスナップショットを取得するには、sys.dm_exec_query_stats 動的管理ビューに対してクエリを実行し、キャッシュにあるこれらのプランのプラン ハンドルを取得します。プラン ハンドルは、sys.dm_exec_query_stats の plan_handle 列に格納されます。その後、次のように CROSS APPLY 演算子を使用して、プラン ハンドルを sys.dm_exec_query_plan に渡します。現在プラン キャッシュにある、収集された統計情報に関連する各プランの XML プラン表示の出力は、返されるテーブルの query_plan 列に格納されます。

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO

D. 平均 CPU 時間に基づく上位 5 つのクエリに関する情報を取得する

次の例では、上位 5 つのクエリにかかった平均 CPU 時間とプランを返します。

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO

参照

関連項目

動的管理ビューと動的管理関数
sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_requests
sp_who (Transact-SQL)
sys.dm_exec_text_query_plan

その他の技術情報

XML プラン表示
APPLY の使用
実行プランのキャッシュと再利用
論理操作と物理操作のリファレンス
クエリ パフォーマンス

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

変更履歴

リリース	履歴
2006 年 12 月 12 日	新しい内容 : クエリ プランに 128 以上のプラン レベルが含まれている場合のこの関数の動作を追加。 パラメータ化されたアドホック クエリのクエリ プランの取得に関する情報を追加。 例 D を追加。 変更内容 : キャッシュ内にあるか現在実行中のクエリ プランを指定できることを明示。 アドホック バッチと準備されたバッチの場合に dbid、objectid、および number 列に NULL が返されることを示すために、これらの列の定義を変更。
2005 年 12 月 5 日	変更内容 : Showplan XML スキーマのインストール場所を変更。