sys.dm_exec_query_plan (Transact-SQL)

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

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

Showplan の XML スキーマは公開されており、この Microsoft Web サイト で使用できます。 また、SQL Server がインストールされているディレクトリでも使用できます。

Transact-SQL 構文表記規則

構文

sys.dm_exec_query_plan(plan_handle)  

引数

plan_handle
既に実行されてプランがプラン キャッシュに格納されているバッチ、または現在実行中のバッチに関するクエリ実行プランの一意識別子を指定するトークンです。 plan_handle は varbinary(64) です。

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

• sys.dm_exec_cached_plans (Transact-SQL)

• sys.dm_exec_query_stats (Transact-SQL)

• sys.dm_exec_requests (Transact-SQL)

• sys.dm_exec_procedure_stats (Transact-SQL)

• sys.dm_exec_trigger_stats (Transact-SQL)

返されるテーブル

列名	データ型	説明
dbid	smallint	このプランに対応する Transact-SQL ステートメントがコンパイルされたときに有効であったコンテキスト データベースの ID。 アドホックおよび準備済み SQL ステートメントの場合、ステートメントがコンパイルされたデータベースの ID。 列で 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 ステートメントがいくつかあります。 このようなステートメントの XML プラン表示は、キャッシュに存在しないため、バッチが現在実行中でない限り、 sys.dm_exec_query_plan を使用して取得できません。

• Transact-SQL のバッチまたはストアド プロシージャに、EXEC (string など) を使用したユーザー定義関数の呼び出しや動的 SQL の呼び出しが含まれている場合、ユーザー定義関数のコンパイル済み XML Showplan は、バッチまたはストアド プロシージャの sys.dm_exec_query_plan によって返されるテーブルに含まれません。 代わりに、ユーザー定義関数に対応するプラン ハンドルの sys.dm_exec_query_plan を個別に呼び出す必要があります。

アドホック クエリで単純パラメーター化または強制パラメーター化を使用する場合、 query_plan 列には、実際のクエリ プランではなくステートメント テキストのみが含まれます。 クエリ プランを返すには、準備されたパラメーター化クエリのプラン ハンドルの sys.dm_exec_query_plan を呼び出します。 クエリがパラメーター化されたのは、sys.syscacheobjects ビューの sql 列か、sys.dm_exec_sql_text動的管理ビューのテキスト列かを確認できます。

Note

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

アクセス許可

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

SQL Server 2022 以降でのアクセス許可

サーバーに対する VIEW SERVER PERFORMANCE 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 バッチのクエリ プランは、プラン キャッシュと呼ばれるメモリ領域にキャッシュされます。 キャッシュされた各クエリ プランは、プラン ハンドルと呼ばれる一意の識別子によって識別されます。 このプラン ハンドルは、 sys.dm_exec_query_plan 動的管理ビューで指定して、特定の Transact-SQL クエリまたはバッチの実行プランを取得できます。

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

Note

この例を実行するには、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 形式の実行プランは、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_handle の sys.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_handle の sys.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)
sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL)
プラン表示の論理操作と物理操作のリファレンス
sys.dm_exec_text_query_plan (Transact-SQL)