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

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

針對計畫控制代碼指定的批次,以 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 網站上取得。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 of the context database that was in effect when the Transact-SQLTransact-SQL statement corresponding to this plan was compiled. 對於隨選和準備的 SQL 陳述式而言,則為編譯陳述式的資料庫識別碼。For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

資料行可為 Null。Column is nullable.
objectidobjectid intint 這個查詢計畫的物件識別碼 (如預存程序或使用者自訂函數)。ID of the object (for example, stored procedure or user-defined function) for this query plan. 若為特定和準備批次,這個資料行是 NullFor 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. 若為特定和準備批次,這個資料行是 NullFor 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 stlxml 包含以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 陳述式、預存程序呼叫和使用者自訂函數呼叫的批次,都會產生一份計畫。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.

  • 尚未快取某些 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_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 執行程式表不會包含在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.databases 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 動態管理檢視的文字資料行,判斷查詢是否參數化。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資料類型所允許的嵌套層級數目有限制,因此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.
您可以使用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

若要執行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. 此時 Management StudioManagement Studio 摘要窗格會顯示 XML 顯示計畫。The XML Showplan displays in the Management StudioManagement Studio summary pane. 若要將 XML 執行程式表儲存至檔案,請以滑鼠右鍵按一下 [ query_plan ] 欄中的 [ ShowPlanXML ],按一下 [將結果儲存為],以< file_name>. .sqlplan; 格式將檔案命名為。例如,Myxmlshowplan.sqlplan. .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 預存程序來擷取正在執行查詢或批次之處理序的伺服器處理序識別碼 (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: ```sql USE master; GO SELECT * FROM sys.dm_exec_requests WHERE session_id = 54; GO ``` **Dm_exec_requests sys**所傳回的資料表會指出執行緩慢的查詢或批次的計畫控制碼為`0x06000100A27E7C1FA821B10600`,您可以使用`sys.dm_exec_query_plan`將其指定為的*plan_handle*引數,如下所示取得 XML 格式的執行計畫。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`. ```sql 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_handle` 的 `sys.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. ```sql 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_handle` 的 `sys.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. ```sql 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 時間排列之前五項查詢的相關資訊Retrieve information about the top five queries by average CPU time 下列範例會傳回前五項查詢的計畫和平均 CPU 時間。The following example returns the plans and average CPU time for the top five queries. ```sql 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)](~/relational-databases/system-dynamic-management-views/system-dynamic-management-views.md) [Dynamic Management Views and Functions (Transact-SQL)](~/relational-databases/system-dynamic-management-views/system-dynamic-management-views.md) [sys.dm_exec_cached_plans (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql.md) [sys.dm_exec_cached_plans (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql.md) [dm_exec_query_stats (Transact-sql)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql.md) [sys.dm_exec_query_stats (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql.md) [sys.dm_exec_requests (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql.md) [sys.dm_exec_requests (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql.md) [sp_who (Transact-sql)](../../relational-databases/system-stored-procedures/sp-who-transact-sql.md) [sp_who (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-who-transact-sql.md) [執行程序邏輯和實體運算子參考](../../relational-databases/showplan-logical-and-physical-operators-reference.md) [Showplan Logical and Physical Operators Reference](../../relational-databases/showplan-logical-and-physical-operators-reference.md) [dm_exec_text_query_plan (Transact-sql)sys.dm_exec_text_query_plan (Transact-SQL)](../../relational-databases/system-dynamic-management-views/sys-dm-exec-text-query-plan-transact-sql.md)