Skip to content

sys-dm-exec-query-plan-stats-transact-sql.md

Latest commit

 

History

History
144 lines (107 loc) · 8.2 KB

sys-dm-exec-query-plan-stats-transact-sql.md

File metadata and controls

144 lines (107 loc) · 8.2 KB
title sys.dm_exec_query_plan_stats (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 05/22/2019
ms.prod sql
ms.reviewer
ms.technology system-objects
ms.topic conceptual
f1_keywords
sys.dm_exec_query_plan_stats
sys.dm_exec_query_plan_stats_TSQL
dm_exec_query_plan_stats_TSQL
dm_exec_query_plan_stats
helpviewer_keywords
sys.dm_exec_query_plan_stats management view
ms.assetid fdc7659e-df41-488e-b2b5-0d79734dfacb
author pmasl
ms.author pelopes
manager amitban

sys.dm_exec_query_plan_stats (Transact-SQL)

[!INCLUDEtsql-appliesto-ssver15-xxxx-xxxx-xxx]

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 [!INCLUDEtsql] 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 [!INCLUDEtsql] statements, stored procedure calls, and user-defined function calls.

Column is nullable.

Remarks

This system function is available starting with [!INCLUDEsql-server-2019] CTP 2.4.

This is an opt-in feature and requires trace flag 2451 to be enabled. Starting with [!INCLUDEsql-server-2019] 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 [!INCLUDEsql-server-2019] CTP 2.5, this refers to a Showplan that only contains the root node operator (SELECT). For [!INCLUDEsql-server-2019] 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 [!INCLUDEssNoVersion], this condition prevented the query plan from returning and generates error 6335. In [!INCLUDEssVersion2005] 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)