sys.dm_exec_query_plan_stats (Transact-SQL)

Artikel
02/27/2023

Applies to: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance

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

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 isn't 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 is an opt-in feature. To enable at the server level, use Trace Flag 2451. To enable at the database level, use 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.

The Showplan output by sys.dm_exec_query_plan_stats contains the following information:

All the compile-time information found in the cached plan
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

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:

The plan can be found in sys.dm_exec_cached_plans.

AND
The query being executed is complex or resource consuming.

Under the following conditions, a simplified ¹ 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.

¹ Refers to a Showplan that only contains the root node operator (SELECT).

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 wasn't cacheable in the first place. For more information, see Execution Plan Caching and Reuse.

Note

A limitation in the number of nested levels allowed in the xml data type, means that 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.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Examples

A. Look 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. Look 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. Look 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

Dela via