sys.dm_exec_query_plan(Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース

sys.dm_exec_query_plan動的管理関数は、プランハンドルで指定されたバッチのShowplanをXML形式で返します。 プラン ハンドルによって指定されたプランは、キャッシュすることも、現在実行中にすることもできます。

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

Transact-SQL 構文表記規則

Syntax

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)

返されるテーブル

注釈

次の条件下では、返されるテーブルのquery_plan列にsys.dm_exec_query_planのShowplan出力は返されません。

• plan_handleを使って指定されたクエリプランがプランキャッシュから追い出されている場合、返されたテーブルのquery_plan列はNULLされます。 例えば、この条件は、プランハンドルが取得されてから使用された時間の間に時間遅延がある場合に発生します sys.dm_exec_query_plan

• 一括操作ステートメントや、8 KB よりも大きなサイズの文字列リテラルを含むステートメントなど、キャッシュされない Transact-SQL ステートメントがいくつかあります。 このような文のXML Showplanは、バッチが現在実行中でない限り sys.dm_exec_query_plan で取得できません。キャッシュには存在しないためです。

• Transact-SQL バッチやストアドプロシージャにユーザー定義関数の呼び出しや動的SQLへの呼び出しが含まれている場合(例えばEXECを使った文字列)、そのユーザー定義関数のコンパイル済み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動的管理ビューのテキスト列を参照することで判断できます。

Permissions

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

SQL Server 2022以降バージョンの権限

サーバーに対する VIEW SERVER PERFORMANCE STATE 権限が必要です。

Examples

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

XMLショープランを見るには、SQL Server Management Studioのクエリ エディターで以下のクエリを実行し、管理スタジオのサマリーペインでsys.dm_exec_query_plan返されたテーブルのquery_plan列でShowPlanXMLを選択してください。 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 プラン表示を取得する方法を示しています。

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

exec sp_who;  
GO  

sp_whoが返す結果セットは、セッションIDが54であることを示します。 sys.dm_exec_requests動的管理ビューでセッションIDを使って、以下のクエリでプランハンドルを取得することができます:

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

sys.dm_exec_requestsが返すテーブルは、遅いクエリやバッチのプランハンドルが0x06000100A27E7C1FA821B10600であることを示しており、plan_handleの引数としてsys.dm_exec_query_planを付けてXML形式の実行計画を取得することができます。 遅いクエリやバッチの実行計画は、sys.dm_exec_query_planが返すテーブルのquery_plan列にXML形式で含まれています。

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 列に格納されます。

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 列にあります。

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  

関連するコンテンツ

• システム動的管理のビューと機能
• sys.dm_exec_cached_plans(Transact-SQL)
• sys.dm_exec_query_stats(Transact-SQL)
• sys.dm_exec_requests(Transact-SQL)
• sys.sp_who(Transact-SQL)
• 論理操作と物理操作のリファレンス
• sys.dm_exec_text_query_plan(Transact-SQL)