sys.dm_exec_query_plan_stats (Transact-SQL)

APPLIES TO: yesSQL Server 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_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_handle is varbinary(64).

The plan_handle can be obtained from the following dynamic management objects:

Table Returned

Column Name Data Type Description
dbid smallint ID of the context database that was in effect when the Transact-SQL statement corresponding to this plan was compiled. For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

Column is nullable.
objectid int ID of the object (for example, stored procedure or user-defined function) for this query plan. For ad hoc and prepared batches, this column is null.

Column is nullable.
number smallint Numbered stored procedure integer. For example, a group of procedures for the orders application may be named orderproc;1, orderproc;2, and so on. For ad hoc and prepared batches, this column is null.

Column is nullable.
encrypted bit Indicates whether the corresponding stored procedure is encrypted.

0 = not encrypted

1 = encrypted

Column is not nullable.
query_plan xml Contains the last known runtime Showplan representation of the actual query execution plan that is specified with plan_handle. The Showplan is in XML format. One plan is generated for each batch that contains, for example ad hoc Transact-SQL statements, stored procedure calls, and user-defined function calls.

Column is nullable.

Remarks

This system function is available starting with SQL Server 2019 (15.x) CTP 2.4.

This is an opt-in feature and requires trace flag 2451 to be enabled. Starting with 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.

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:

Under 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:

  • The plan can be found in sys.dm_exec_cached_plans.
    AND
  • The query is simple enough, usually categorized as part of an OLTP workload.

1 Starting with SQL Server 2019 (15.x) CTP 2.5, this refers to a Showplan that only contains the root node operator (SELECT). For SQL Server 2019 (15.x) CTP 2.4 this refers to the cached plan as available through sys.dm_exec_cached_plans.

Under the following conditions, no output is returned from sys.dm_exec_query_plan_stats:

  • The query plan that is specified by using plan_handle has been evicted from the plan cache.
    OR
  • The query plan was not cacheable in the first place. For more information, see Execution Plan Caching and Reuse .

Note

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. In earlier versions of SQL Server, this condition prevented the query plan from returning and generates error 6335. In SQL Server 2005 (9.x) Service Pack 2 and later versions, the query_plan column returns NULL.

Permissions

Requires VIEW SERVER STATE permission on the server.

Examples

A. Looking at last known actual query execution plan for a specific cached plan

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  

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. 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. 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. 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
Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views (Transact-SQL)