sys.dm_exec_query_plan (Transact-SQL)
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
プラン ハンドルで指定されたバッチのプラン表示を XML 形式で返します。 プラン ハンドルで指定するプランは、キャッシュ内のもの、または現在実行中のものを指定できます。
プラン表示の XML スキーマは公開されており、 この Microsoft Web サイトで使用できます。 SQL Serverがインストールされているディレクトリでも使用できます。
構文
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;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 バッチまたはストアド プロシージャに、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 動的管理ビューの text 列を参照します。
注意
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 Showplan をファイルに保存するには、[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 プラン表示を取得する方法を示します。
注意
この例を実行するには、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であることを示します。これは、 で plan_handle 引数sys.dm_exec_query_planとして指定して、実行プランを 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 動的管理ビューに対してクエリを実行し、キャッシュにあるすべてのクエリ プランのプラン ハンドルを取得します。 プラン ハンドルは、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)
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示