sys.dm_exec_query_plan (Transact-SQL)

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース

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

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

トピック リンク アイコン Transact-SQL 構文表記規則

構文

sys.dm_exec_query_plan(plan_handle)  

引数

plan_handle
実行されたバッチとそのプランがプラン キャッシュに存在するか、現在実行中のバッチのクエリ実行プランを一意に識別するトークンです。 plan_handle varbinary(64) です

この plan_handle、 次の動的管理オブジェクトから取得できます。

返されるテーブル

列名 データ型 説明
Dbid smallint このプランに対応する Transact-SQL ステートメントがコンパイルされたときに有効であったコンテキスト データベースの ID。 アドホック SQL ステートメントおよび準備された SQL ステートメントの場合、ステートメントがコンパイルされたデータベースの ID。

NULL 値は許可されます。
objectid int ストアド プロシージャやユーザー定義関数など、クエリ プランのオブジェクトの ID。 アドホック バッチおよび準備されたバッチの場合、この列の値は NULL です。

NULL 値は許可されます。
number smallint ストアド プロシージャに付けられた番号 (整数)。 たとえば、orders アプリケーションのプロシージャ グループの名前は、orderproc;1orderproc;2 のように指定されることがあります。 アドホック バッチおよび準備されたバッチの場合、この列の値は NULL です。

NULL 値は許可されます。
暗号化 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 プラン表示を取得することはできません。

  • バッチまたはストアド プロシージャにユーザー定義関数の呼び出しまたは動的 SQL の呼び出し (EXEC ( 文字列) を使用するなど) が含まれている場合、ユーザー定義関数のコンパイル済み XML プラン表示は、バッチまたはストアド プロシージャの sys.dm_exec_query_plan によって返されるテーブルには含まれません。 Transact-SQL 代わりに、ユーザー定義関数に対応するプランハンドル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 の以前のバージョンでは、この条件が原因でクエリ プランが返されず、エラー 6335 が生成されます。 SQL Server 2005 (9.x)Service Pack 2 以降のバージョンでは、この列query_plan NULL が返されます。
Transact-sys.dm_exec_text_query_plan (動的SQL)を使用して、クエリ プランの出力をテキスト形式で返します。

アクセス許可

を実行 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] を右クリックし、[結果を名前を付けて保存] をクリックし、.sqlplan という形式でファイルに名前を付け <file_name> (MyXMLShowplan.sqlplan など) します。

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

アドホック バッチ、ストアド プロシージャ、ユーザー定義関数などの各種 Transact-SQL バッチのクエリ プランは、プラン キャッシュと呼ばれるメモリ領域にキャッシュされます。 キャッシュされたそれぞれのクエリ プランは、プラン ハンドルと呼ばれる一意識別子で識別されます。 sys.dm_exec_query_plan 動的管理ビューでは、このプラン ハンドルを指定して、特定の 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 によって返されるテーブルは、実行速度の遅いクエリまたはバッチのプラン ハンドルが で、次のように plan_handle 引数として を指定して XML 形式で実行プランを取得できます。 0x06000100A27E7C1FA821B10600 sys.dm_exec_query_plan 実行速度の遅いクエリまたはバッチの 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 動的管理ビューに対してクエリを実行し、キャッシュにあるすべてのクエリ プランのプラン ハンドルを取得します。 プラン ハンドルは、plan_handlesys.dm_exec_cached_plans 列に格納されます。 その後、次のように CROSS APPLY 演算子を使用して、プラン ハンドルを sys.dm_exec_query_plan に渡します。 現在プラン キャッシュにある各プランの XML プラン表示の出力は、返されるテーブルの query_plan 列に格納されます。

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

C. サーバーがプラン キャッシュからクエリ統計を収集したクエリ プランを取得します

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

USE master;  
GO  
SELECT * 
FROM sys.dm_exec_query_stats AS 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  

参照

動的管理ビューと動的管理関数 (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (transact-sql SQL)
sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL)
プラン表示の論理操作と物理操作のリファレンス
sys.dm_exec_text_query_plan (transact-sql SQL)