sys.dm_exec_text_query_plan (Transact-SQL)sys.dm_exec_text_query_plan (Transact-SQL)

適用対象: ○SQL Server (2008 以降) ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

プラン表示をテキスト形式で返します、Transact-SQLTransact-SQLバッチまたはバッチ内の特定のステートメント。Returns the Showplan in text format for a Transact-SQLTransact-SQL batch or for a specific statement within the batch. クエリ プランでは、プラン ハンドルでできますキャッシュまたは現在実行されているを指定します。The query plan specified by the plan handle can either be cached or currently executing. このテーブル値関数に似ていますsys.dm_exec_query_plan (TRANSACT-SQL)が、次の相違点。This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL), but has the following differences:

  • クエリ プランの出力がテキスト形式で返される。The output of the query plan is returned in text format.
  • クエリ プランの出力は、サイズの制限はありません。The output of the query plan is not limited in size.
  • バッチ内の個々 のステートメントを指定することができます。Individual statements within the batch can be specified.

適用対象: SQL ServerSQL Server (SQL Server 2008:SQL Server 2008 から SQL Server 2017SQL Server 2017)、Azure SQL データベースAzure SQL DatabaseApplies to: SQL ServerSQL Server (SQL Server 2008:SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL データベースAzure SQL Database.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

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

引数Arguments

plan_handleplan_handle
実行されるバッチのクエリ実行プランを一意に識別するトークンと、そのプランがプラン キャッシュ内に存在または現在実行しています。Is a token that uniquely identifies a query execution plan for a batch that has executed and its plan resides in the plan cache, or is currently executing. plan_handlevarbinary (64) します。plan_handle is varbinary(64).

Plan_handle次の動的管理オブジェクトから取得できます。The plan_handle can be obtained from the following dynamic management objects:

statement_start_offset | 0 | DEFAULTstatement_start_offset | 0 | DEFAULT
バッチまたは保存されるオブジェクトのテキスト内での、行が示すクエリの開始位置 (バイト単位) を示します。Indicates, in bytes, the starting position of the query that the row describes within the text of its batch or persisted object. statement_start_offsetintします。値 0 は、バッチの先頭を示します。statement_start_offset is int. A value of 0 indicates the beginning of the batch. 既定値は 0 です。The default value is 0.

ステートメントの開始オフセットは、次の動的管理オブジェクトから取得できます。The statement start offset can be obtained from the following dynamic management objects:

statement_end_offset | -1 | DEFAULTstatement_end_offset | -1 | DEFAULT
バッチまたは保存されるオブジェクトのテキスト内で、行が示すクエリの終了位置をバイト単位で示します。Indicates, in bytes, the ending position of the query that the row describes within the text of its batch or persisted object.

statement_start_offsetintします。statement_start_offset is int.

値 -1 はバッチの最後を表します。A value of -1 indicates the end of the batch. 既定値は-1 です。The default value is -1.

返されるテーブルTable Returned

列名Column name データ型Data type 説明Description
dbiddbid smallintsmallint このプランに対応する Transact-SQLTransact-SQL ステートメントがコンパイルされたときに有効であったコンテキスト データベースの ID。ID of the context database that was in effect when the Transact-SQLTransact-SQL statement corresponding to this plan was compiled. アドホックおよび準備された SQL ステートメントのステートメントがコンパイルされたデータベースの ID。For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

列が null 値を許容します。Column is nullable.
objectidobjectid intint このクエリ プランのオブジェクト (たとえば、ストアド プロシージャまたはユーザー定義関数) の ID。ID of the object (for example, stored procedure or user-defined function) for this query plan. アドホックおよび準備されたバッチでは、この列はnullします。For ad hoc and prepared batches, this column is null.

列が null 値を許容します。Column is nullable.
numbernumber smallintsmallint 番号付きストアド プロシージャの整数。Numbered stored procedure integer. ための手順のグループなど、注文アプリケーションが付けられてorderproc; 1orderproc; 2など。For example, a group of procedures for the orders application may be named orderproc;1, orderproc;2, and so on. アドホックおよび準備されたバッチでは、この列はnullします。For ad hoc and prepared batches, this column is null.

列が null 値を許容します。Column is nullable.
encryptedencrypted bitbit 対応するストアド プロシージャを暗号化するかどうかを示します。Indicates whether the corresponding stored procedure is encrypted.

0 = 暗号化されていません。0 = not encrypted

1 = 暗号化1 = encrypted

列値が許容されません。Column is not nullable.
query_planquery_plan nvarchar(max)nvarchar(max) 指定されているクエリの実行プランのコンパイル時のプラン表示形式を格納してplan_handleします。Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. プラン表示はテキスト形式です。The Showplan is in text format. 含まれているなどのアドホック バッチごとに 1 つのプランが生成されたTransact-SQLTransact-SQLステートメント、ストアド プロシージャの呼び出し、およびユーザー定義関数の呼び出し。One plan is generated for each batch that contains, for example ad hoc Transact-SQLTransact-SQL statements, stored procedure calls, and user-defined function calls.

列が null 値を許容します。Column is nullable.

コメントRemarks

次の条件では、プラン表示出力が返されない、プランの返されたテーブルの列sys.dm_exec_text_query_plan:Under the following conditions, no Showplan output is returned in the plan column of the returned table for sys.dm_exec_text_query_plan:

  • 場合は、クエリ プランを使用して指定plan_handleがプラン キャッシュから削除された、 query_plan返されるテーブルの列が null です。If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. プラン ハンドルがキャプチャされた、と共に使用された場合との間の遅延時間がある場合にこの状態が発生するなど、 sys.dm_exec_text_query_planします。For example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_text_query_plan.

  • いくつかTransact-SQLTransact-SQL一括操作ステートメントや 8 KB のサイズより大きい文字列リテラルを含むステートメントなど、ステートメントはキャッシュされません。Some Transact-SQLTransact-SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. 使用してこのようなステートメントのプラン表示を取得することはできませんsys.dm_exec_text_query_planキャッシュが存在しないためです。Showplans for such statements cannot be retrieved by using sys.dm_exec_text_query_plan because they do not exist in the cache.

  • 場合、Transact-SQLTransact-SQLバッチまたはストアド プロシージャは、ユーザー定義関数への呼び出しやたとえば EXEC を使用して、動的 SQL への呼び出しが含まれます (文字列)、XML プラン表示の表に、ユーザー定義関数が含まれていないコンパイルによって返されるsys.dm_exec_text_query_planバッチやストアド プロシージャ。If a Transact-SQLTransact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_text_query_plan for the batch or stored procedure. 代わりに、別の呼び出しを行う必要がありますsys.dm_exec_text_query_planplan_handleユーザー定義関数に対応します。Instead, you must make a separate call to sys.dm_exec_text_query_plan for the plan_handle that corresponds to the user-defined function.

アドホック クエリを使用する場合単純または強制パラメーター化query_planステートメント テキストのみと実際のクエリ プランではない列が含まれます。When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. クエリ プランを返すを呼び出すsys.dm_exec_text_query_plan準備されたパラメーター化されたクエリのプラン ハンドル。To return the query plan, call sys.dm_exec_text_query_plan for the plan handle of the prepared parameterized query. 参照することによって、クエリがパラメーター化されたかどうかを指定できます、 sqlの列、 sys.syscacheobjectsビューまたはの text 列、 sys.dm_exec_sql_text動的管理ビュー。You can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view.

アクセス許可Permissions

実行するsys.dm_exec_text_query_plan、ユーザーのメンバーである必要があります、 sysadmin固定サーバー ロールまたはサーバーの VIEW SERVER STATE 権限があります。To execute sys.dm_exec_text_query_plan, a user must be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission on the server.

使用例Examples

A.A. 実行速度の遅い TRANSACT-SQL クエリまたはバッチに対するキャッシュされたクエリ プランを取得します。Retrieving the cached query plan for a slow-running Transact-SQL query or batch

Transact-SQLTransact-SQL クエリまたは SQL ServerSQL Server バッチが、特定の との接続において長時間実行されている場合は、このクエリやバッチの実行プランを取得して、遅延の原因を調べることができます。If a Transact-SQLTransact-SQL query or batch runs a long time on a particular connection to SQL ServerSQL Server, retrieve the execution plan for that query or batch to discover what is causing the delay. 次の例では、実行速度の遅いクエリまたはバッチに対する実行プランを取得する方法を示します。The following example shows how to retrieve the Showplan for a slow-running query or batch.

注意

この例を実行するには、値を置き換えますsession_idplan_handleサーバーに特定の値を使用します。To run this example, replace the values for session_id and plan_handle with values specific to your server.

まず、sp_who ストアド プロシージャを使用して、クエリまたはバッチを実行しているプロセスのサーバー プロセス ID (SPID) を取得します。First, retrieve the server process ID (SPID) for the process that is executing the query or batch by using the sp_who stored procedure:

USE master;  
GO  
EXEC sp_who;  
GO  

によって返される結果セットsp_who、spid の値があることを示します54します。The result set that is returned by sp_who indicates that the SPID is 54. sys.dm_exec_requests 動的管理ビューで、この SPID を使用して次のクエリを実行すると、プラン ハンドルを取得できます。You can use the SPID with the sys.dm_exec_requests dynamic management view to retrieve the plan handle by using the following query:

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

によって返されるテーブルsys.dm_exec_requests実行速度の遅いクエリまたはバッチのプラン ハンドルがあることを示します0x06000100A27E7C1FA821B10600します。The table that is returned by sys.dm_exec_requests indicates that the plan handle for the slow-running query or batch is 0x06000100A27E7C1FA821B10600. 次の例は、指定したプラン ハンドルのクエリ プランを返し、既定値 0 および -1 を使用してクエリまたはバッチ内のすべてのステートメントを返します。The following example returns the query plan for the specified plan handle and uses the default values 0 and -1 to return all statements in the query or batch.

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

B.B. すべてのクエリ プランをプラン キャッシュから取得します。Retrieving every query plan from the plan cache

プラン キャッシュにあるすべてのクエリ プランのスナップショットを取得するには、sys.dm_exec_cached_plans 動的管理ビューに対してクエリを実行し、キャッシュにあるすべてのクエリ プランのプラン ハンドルを取得します。To retrieve a snapshot of all query plans residing in the plan cache, retrieve the plan handles of all query plans in the cache by querying the sys.dm_exec_cached_plans dynamic management view. プラン ハンドルは、plan_handlesys.dm_exec_cached_plans 列に格納されます。The plan handles are stored in the plan_handle column of sys.dm_exec_cached_plans. CROSS APPLY 演算子を使用し、プラン ハンドルを渡すsys.dm_exec_text_query_plan次のようにします。Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_text_query_plan as follows. プラン表示の出力は、現在プラン キャッシュ内の各プランは、query_plan返されるテーブルの列。The Showplan output for each plan currently in the plan cache is in the query_plan column of the table that is returned.

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.C. 対象のサーバーは、プラン キャッシュからクエリの統計情報を収集がすべてのクエリ プランを取得します。Retrieving every query plan for which the server has gathered query statistics from the plan cache

現在プラン キャッシュにあるクエリ プランのうち、サーバーで統計情報が収集されたすべてのクエリ プランのスナップショットを取得するには、sys.dm_exec_query_stats 動的管理ビューに対してクエリを実行し、キャッシュにあるこれらのプランのプラン ハンドルを取得します。To retrieve a snapshot of all query plans for which the server has gathered statistics that currently reside in the plan cache, retrieve the plan handles of these plans in the cache by querying the sys.dm_exec_query_stats dynamic management view. プラン ハンドルは、plan_handlesys.dm_exec_query_stats 列に格納されます。The plan handles are stored in the plan_handle column of sys.dm_exec_query_stats. CROSS APPLY 演算子を使用し、プラン ハンドルを渡すsys.dm_exec_text_query_plan次のようにします。Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_text_query_plan as follows. 各プランのプラン表示出力は、返されるテーブルの query_plan 列に格納されます。The Showplan output for each plan is in the query_plan column of the table that is returned.

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.D. 平均 CPU 時間の上位 5 クエリに関する情報の取得Retrieving information about the top five queries by average CPU time

次の例では、上位 5 つのクエリの平均 CPU 時間とクエリ プランを返します。The following example returns the query plans and average CPU time for the top five queries. Sys.dm_exec_text_query_plan関数は、既定値 0 およびクエリ プランで、バッチ内のすべてのステートメントを返すには-1 を指定します。The sys.dm_exec_text_query_plan function specifies the default values 0 and -1 to return all statements in the batch in the query plan.

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  

参照See Also

sys.dm_exec_query_plan (TRANSACT-SQL)sys.dm_exec_query_plan (Transact-SQL)