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

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database

プラン ハンドルで指定されたバッチのプラン表示を XML 形式で返します。Returns the Showplan in XML format for the batch specified by the plan handle. プラン ハンドルで指定するプランは、キャッシュ内のもの、または現在実行中のものを指定できます。The plan specified by the plan handle can either be cached or currently executing.

プラン表示の XML スキーマは、 この Microsoft Web サイトで公開されており、利用できます。The XML schema for the Showplan is published and available at this Microsoft Web site. また、SQL ServerSQL Server がインストールされているディレクトリからも入手できます。It is also available in the directory where SQL ServerSQL Server is installed.

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

構文Syntax

sys.dm_exec_query_plan(plan_handle)  

引数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:

返されるテーブル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 ステートメントおよび準備された 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. たとえば、orders アプリケーションのプロシージャ グループの名前は、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.
暗号encrypted bitbit 対応するプロシージャが暗号化されているかどうか。Indicates whether the corresponding stored procedure is encrypted.

0 = 暗号化されていない0 = not encrypted

1 = 暗号化されている1 = encrypted

NULL 値は許可されません。Column is not nullable.
query_planquery_plan xmlxml Plan_handle で指定されたクエリ実行プランのコンパイル時のプラン表示表現を格納します。Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. プラン表示は XML 形式です。The Showplan is in XML format. アドホック Transact-SQLTransact-SQL ステートメント、ストアド プロシージャ コール、ユーザー定義関数コールなどを含むバッチごとに、1 つのプランが生成されます。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_query_plan で返されるテーブルの query_plan 列にはプラン表示の出力は返されません。Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_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_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_query_plan.

  • 一括操作ステートメントや、8 KB よりも大きなサイズの文字列リテラルを含むステートメントなど、キャッシュされない Transact-SQLTransact-SQL ステートメントがいくつかあります。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_query_plan を使用してこれらのステートメントの XML プラン表示を取得することはできません。XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.

  • Transact-SQLTransact-SQLバッチまたはストアドプロシージャにユーザー定義関数の呼び出しまたは動的 SQL の呼び出し (EXEC (string) の使用など) が含まれている場合、ユーザー定義関数のコンパイル済み XML プラン表示は、バッチまたはストアドプロシージャの sys.dm_exec_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_query_plan for the batch or stored procedure. 代わりに、ユーザー定義関数に対応するプランハンドルに対して、 sys.dm_exec_query_plan の個別の呼び出しを行う必要があります。Instead, you must make a separate call to sys.dm_exec_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_query_plan を呼び出して、準備されたパラメーター化クエリのプラン ハンドルを取得します。To return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. クエリがパラメーター化されたかどうかを判断するには、sys.syscacheobjects ビューの sql 列、または sys.dm_exec_sql_text 動的管理ビューの 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.

注意

Xml データ型で許可されている入れ子になったレベルの数には制限があるため、 sys.dm_exec_query_plan は入れ子になった要素の128レベル以上のクエリプランを返すことはできません。Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_plan cannot return query plans that meet or exceed 128 levels of nested elements. SQL ServerSQL Server の以前のバージョンでは、この条件が原因でクエリ プランが返されず、エラー 6335 が生成されます。In earlier versions of SQL ServerSQL Server, this condition prevented the query plan from returning and generates error 6335. SQL Server 2005 (9.x)SQL Server 2005 (9.x)Service Pack 2 以降のバージョンでは、 query_plan 列には NULL が返されます。In SQL Server 2005 (9.x)SQL Server 2005 (9.x) Service Pack 2 and later versions, the query_plan column returns NULL.
Sys.dm_exec_text_query_plan (transact-sql)動的管理関数を使用すると、クエリプランの出力をテキスト形式で返すことができます。You can use the sys.dm_exec_text_query_plan (Transact-SQL) dynamic management function to return the output of the query plan in text format.

アクセス許可Permissions

Sys.dm_exec_query_plan を実行するには、 sysadmin 固定サーバーロールのメンバーであるか、サーバーに対する権限を持っている必要があり VIEW SERVER STATE ます。To execute sys.dm_exec_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

次の例は、sys.dm_exec_query_plan 動的管理ビューの使用方法を示しています。The following examples show how to use the sys.dm_exec_query_plan dynamic management view.

XML プラン表示を表示するには、SQL Server Management StudioSQL Server Management Studio のクエリ エディターで次のクエリを実行した後、sys.dm_exec_query_plan によって返されるテーブルの query_plan 列で [ShowPlanXML] をクリックします。To view the XML Showplans, execute the following queries in the Query Editor of SQL Server Management StudioSQL Server Management Studio, then click ShowPlanXML in the query_plan column of the table returned by sys.dm_exec_query_plan. XML プラン表示は、Management StudioManagement Studio の概要ペインに表示されます。The XML Showplan displays in the Management StudioManagement Studio summary pane. XML プラン表示をファイルに保存するには、[ query_plan ] 列の [ showplan XML ] を右クリックし、[結果に名前を付け て保存] をクリックします。ファイルの名前を sqlplan のように指定します ( <file_name> 例: myxmlshowplan. sqlplan)。To save the XML Showplan to a file, right-click ShowPlanXML in the query_plan column, click Save Results As, name the file in the format <file_name>.sqlplan; for example, MyXMLShowplan.sqlplan.

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

アドホック バッチ、ストアド プロシージャ、ユーザー定義関数などの各種 Transact-SQLTransact-SQL バッチのクエリ プランは、プラン キャッシュと呼ばれるメモリ領域にキャッシュされます。Query plans for various types of Transact-SQLTransact-SQL batches, such as ad hoc batches, stored procedures, and user-defined functions, are cached in an area of memory called the plan cache. キャッシュされたそれぞれのクエリ プランは、プラン ハンドルと呼ばれる一意識別子で識別されます。Each cached query plan is identified by a unique identifier called a plan handle. sys.dm_exec_query_plan 動的管理ビューでは、このプラン ハンドルを指定して、特定の Transact-SQLTransact-SQL クエリまたはバッチの実行プランを取得できます。You can specify this plan handle with the sys.dm_exec_query_plan dynamic management view to retrieve the execution plan for a particular Transact-SQLTransact-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. 次の例では、実行速度の遅いクエリまたはバッチに対して XML プラン表示を取得する方法を示します。The following example shows how to retrieve the XML 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 ます。これは、次のように sys.dm_exec_query_plan XML 形式で実行プランを取得するために、で plan_handle 引数として指定できます。The table that is returned by sys.dm_exec_requests indicates that the plan handle for the slow-running query or batch is 0x06000100A27E7C1FA821B10600, which you can specify as the plan_handle argument with sys.dm_exec_query_plan to retrieve the execution plan in XML format as follows. 実行速度の遅いクエリまたはバッチの XML 形式の実行プランは、sys.dm_exec_query_plan によって返されるテーブルの query_plan 列に格納されます。The execution plan in XML format for the slow-running query or batch is contained in the query_plan column of the table returned by sys.dm_exec_query_plan.

USE master;  
GO  
SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B.B. プランキャッシュからすべてのクエリプランを取得するRetrieve 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_query_plan に渡します。Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_query_plan as follows. 現在プラン キャッシュにある各プランの XML プラン表示の出力は、返されるテーブルの query_plan 列に格納されます。The XML 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_query_plan(cp.plan_handle);  
GO  

C.C. サーバーがクエリ統計情報を収集したすべてのクエリプランをプランキャッシュから取得します。Retrieve 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_query_plan に渡します。Then use the CROSS APPLY operator to pass the plan handles to sys.dm_exec_query_plan as follows. 現在プラン キャッシュにある、収集された統計情報に関連する各プランの XML プラン表示の出力は、返されるテーブルの query_plan 列に格納されます。The XML Showplan output for each plan for which the server has gathered statistics 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_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D.D. 平均 CPU 時間に基づく上位 5 つのクエリに関する情報を取得するRetrieve information about the top five queries by average CPU time

次の例では、上位 5 つのクエリにかかった平均 CPU 時間とプランを返します。The following example returns the plans and average CPU time for the top five queries.

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  

参照See Also

動的管理ビューと動的管理関数 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-sql) sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL) sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL) sp_who (Transact-SQL)
プラン表示の論理操作と物理操作のリファレンス Showplan Logical and Physical Operators Reference
sys.dm_exec_text_query_plan (Transact-sql)sys.dm_exec_text_query_plan (Transact-SQL)