sys.dm_exec_text_query_plan (Transact-SQL)

  • [アーティクル]

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

Transact-SQL バッチ、またはバッチ内の特定のステートメントのプラン表示をテキスト形式で返します。 プラン ハンドルによって指定されたクエリ プランは、キャッシュすることも、現在実行中にすることもできます。 このテーブル値関数は sys.dm_exec_query_plan (Transact-SQL) と似ていますが、以下の点で異なります。

  • クエリ プランの出力がテキスト形式で返される。
  • クエリ プランの出力のサイズに制限はありません。
  • バッチ内の個々のステートメントを指定できます。

適用対象: SQL Server (SQL Server 2008 (10.0.x) 以降)、Azure SQL Database。

Transact-SQL 構文表記規則

構文

sys.dm_exec_text_query_plan   
(   
    plan_handle   
    , { statement_start_offset | 0 | DEFAULT }  
        , { statement_end_offset | -1 | DEFAULT }  
)

引数

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

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

statement_start_offset | 0 | DEFAULT
バッチまたは保存されるオブジェクトのテキスト内での、行が示すクエリの開始位置 (バイト単位) を示します。 statement_start_offset は int です。値 0 はバッチの先頭を表します。 既定値は0です。

ステートメントの開始オフセットは、次の動的管理オブジェクトから取得できます。

statement_end_offset | -1 | DEFAULT
行がバッチオブジェクトまたは永続化オブジェクトのテキスト内で記述するクエリの終了位置をバイト単位で示します。

statement_start_offset は int です。

値 -1 はバッチの最後を表します。 既定値は -1 です。

返されるテーブル

列名 データ型 説明
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 nvarchar(max) plan_handle で指定したクエリ実行プランを表す、コンパイル時のプラン表示。 Showplan はテキスト形式です。 アドホック Transact-SQL ステートメント、ストアド プロシージャ コール、ユーザー定義関数コールなどを含むバッチごとに、1 つのプランが生成されます。

列で NULL 値を使用できます。

解説

次の条件では、sys.dm_exec_text_query_planの返されたテーブルの plan 列にプラン表示出力は返されません。

  • plan_handle を使用して指定したクエリ プランがプラン キャッシュから削除された場合、返されるテーブルの query_plan 列は NULL になります。 たとえば、プラン ハンドルがキャプチャされてから、 sys.dm_exec_text_query_planで使用されるまでに時間の遅延がある場合に、この条件が発生する可能性があります。

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

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

アドホック クエリで簡易または強制のパラメーター化を行う場合、query_plan 列にはステートメント テキストのみが格納され、実際のクエリ プランは格納されません。 クエリ プランを返すには、準備されたパラメーター化クエリのプラン ハンドルの sys.dm_exec_text_query_plan を呼び出します。 クエリがパラメーター化されたのは、sys.syscacheobjects ビューの sql 列か、sys.dm_exec_sql_text動的管理ビューのテキスト列かを確認できます。

アクセス許可

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

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

サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。

例

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

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

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されていることを示します。 次の例は、指定したプラン ハンドルのクエリ プランを返し、既定値 0 および -1 を使用してクエリまたはバッチ内のすべてのステートメントを返します。

USE master;  
GO  
SELECT query_plan   
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);  
GO

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

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

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

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

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

USE master;  
GO  
SELECT * FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);  
GO

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

次の例では、上位 5 つのクエリのクエリ プランと平均 CPU 時間を返します。 sys.dm_exec_text_query_plan関数は、クエリ プラン内のバッチ内のすべてのステートメントを返す既定値 0 と -1 を指定します。

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_text_query_plan(qs.plan_handle, 0, -1)  
ORDER BY total_worker_time/execution_count DESC;  
GO

参照

sys.dm_exec_query_plan (Transact-SQL)