次の方法で共有

sys.dm_exec_text_query_plan (Transact-SQL)

  • [アーティクル]

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

  • クエリ プランの出力がテキスト形式で返される。

  • クエリ プランの出力のサイズに制限がない。

  • バッチ内の個々のステートメントを指定できる。

トピック リンク アイコン 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) です。

    次の動的管理オブジェクトからプラン ハンドルを取得できます。

    sys.dm_exec_cached_plans

    sys.dm_exec_query_stats

    sys.dm_exec_requests

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

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

    sys.dm_exec_query_stats

    sys.dm_exec_requests

  • statement_end_offset | -1 | DEFAULT
    バッチまたは保存されるオブジェクトのテキスト内での、行が示すクエリの終了位置 (バイト単位) を示します。

    statement_start_offset のデータ型は int です。

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

返されるテーブル

列名

データ型

説明

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

nvarchar(max)

plan_handle で指定したクエリ実行プランを表す、コンパイル時のプラン表示。 プラン表示はテキスト形式です。 アドホック 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 動的管理ビューの text 列を参照します。

権限

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

使用例

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

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

注意注

この例を実行するには、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 動的管理ビューに対してクエリを実行し、キャッシュにあるすべてのクエリ プランのプラン ハンドルを取得します。 プラン ハンドルは、sys.dm_exec_cached_plans の plan_handle 列に格納されます。 その後、次のように 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 動的管理ビューに対してクエリを実行し、キャッシュにあるこれらのプランのプラン ハンドルを取得します。 プラン ハンドルは、sys.dm_exec_query_stats の plan_handle 列に格納されます。 その後、次のように 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)