sys.databases dm_exec_query_plan_stats (Transact-sql)sys.dm_exec_query_plan_stats (Transact-SQL)

適用于: 是SQL Server 2019 和更新版本Azure SQL Database否 Azure Synapse Analytics (SQL DW )否平行處理資料倉儲APPLIES TO: yesSQL Server 2019 and later yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

傳回先前快取之查詢計劃的最後已知實際執行計畫的對等。Returns the equivalent of the last known actual execution plan for a previously cached query plan.

語法Syntax

sys.dm_exec_query_plan_stats(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 last known runtime Showplan representation of the actual 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

SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.4 開始提供此系統函數。This system function is available starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.4.

這是選擇加入的功能,需要啟用追蹤旗標 2451。This is an opt-in feature and requires trace flag 2451 to be enabled. SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.5 開始,若要在資料庫層級完成此作業,請參閱 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 中的 LAST_QUERY_PLAN_STATS 選項。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.5, to accomplish this at the database level, see the LAST_QUERY_PLAN_STATS option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

此系統函數可在輕量查詢執行統計資料分析基礎結構底下運作。This system function works under the lightweight query execution statistics profiling infrastructure. 如需詳細資訊,請參閱查詢分析基礎結構For more information, see Query Profiling Infrastructure.

Sys 的執行程式表輸出 dm_exec_query_plan_stats 包含下列資訊:The Showplan output by sys.dm_exec_query_plan_stats contains the following information:

  • 在快取計畫中找到的所有編譯時間資訊All the compile-time information found in the cached plan
  • 執行時間資訊,例如每個運算子的實際資料列數目、查詢 CPU 時間與執行時間總計、溢出警告、實際 DOP、使用的記憶體上限和授與的記憶體Runtime information such as the actual number of rows per operator, the total query CPU time and execution time, spill warnings, actual DOP, the maximum used memory and granted memory

在下列情況下,會在dm_exec_query_plan_stats的傳回資料表的query_plan資料行中,傳回相當於實際執行計畫的顯示計畫輸出:Under the following conditions, a Showplan output equivalent to an actual execution plan is returned in the query_plan column of the returned table for sys.dm_exec_query_plan_stats:

  • 您可以在 sys.databases 中找到此方案dm_exec_cached_plansThe plan can be found in sys.dm_exec_cached_plans.
    ANDAND
  • 正在執行的查詢很複雜或耗用資源。The query being executed is complex or resource consuming.

在下列情況下,會在傳回之資料表的query_plan資料行中,傳回一個簡化的1 執行程式表輸出,以供dm_exec_query_plan_statsUnder the following conditions, a simplified 1 Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_plan_stats:

  • 您可以在 sys.databases 中找到此方案dm_exec_cached_plansThe plan can be found in sys.dm_exec_cached_plans.
    ANDAND
  • 查詢夠簡單,通常會分類為 OLTP 工作負載的一部分。The query is simple enough, usually categorized as part of an OLTP workload.

1SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.5 開始,這是指只包含根節點運算子(SELECT)的執行程式表。1 Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.5, this refers to a Showplan that only contains the root node operator (SELECT). 對於SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.4,這是指透過sys.dm_exec_cached_plans提供的快取計畫。For SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.4 this refers to the cached plan as available through sys.dm_exec_cached_plans.

在下列情況下,從sys. dm_exec_query_plan_stats****不會傳回任何輸出Under the following conditions, no output is returned from sys.dm_exec_query_plan_stats:

  • 使用plan_handle所指定的查詢計劃已從計畫快取中收回。The query plan that is specified by using plan_handle has been evicted from the plan cache.
    OROR
  • 查詢計劃無法在第一個位置快取。The query plan was not cacheable in the first place. 如需詳細資訊,請參閱執行計畫快取和重複使用For more information, see Execution Plan Caching and Reuse .

注意

由於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,這種情況會使查詢計劃無法傳回,並產生錯誤 6335In 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.

權限Permissions

需要伺服器的 VIEW SERVER STATE 權限。Requires VIEW SERVER STATE permission on the server.

範例Examples

A.A. 查看特定快取計畫的最後已知實際查詢執行計畫Looking at last known actual query execution plan for a specific cached plan

下列範例會查詢dm_exec_cached_plans sys.databases ,以尋找有趣的計畫,並從plan_handle輸出複製其。The following example queries sys.dm_exec_cached_plans to find the interesting plan and copy its plan_handle from the output.

SELECT * FROM sys.dm_exec_cached_plans;  
GO  

然後,若要取得最後一個已知的實際查詢執行計畫,請plan_handle使用以系統函數sys.databases dm_exec_query_plan_stats複製的。Then, to obtain the last known actual query execution plan, use the copied plan_handle with system function sys.dm_exec_query_plan_stats.

SELECT * FROM sys.dm_exec_query_plan_stats(< copied plan_handle >);  
GO  

B.B. 正在查看所有快取計畫的最後已知實際查詢執行計畫Looking at last known actual query execution plan for all cached plans

SELECT *   
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps;  
GO  

C.C. 查看特定快取計畫和查詢文字的最後已知實際查詢執行計畫Looking at last known actual query execution plan for a specific cached plan and query text

SELECT *   
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps
WHERE st.text LIKE 'SELECT * FROM Person.Person%';  
GO  

D.D. 查看觸發程式的快取事件Look at cached events for trigger

SELECT *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle)
WHERE objtype ='Trigger';
GO

另請參閱See Also

追蹤旗標Trace Flags
動態管理檢視與函數 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
(Transact-sql)執行相關的動態管理檢視Execution Related Dynamic Management Views (Transact-SQL)