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

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse 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 DescriptionDescription
dbiddbid smallintsmallint 在编译对应于此计划的 Transact-SQLTransact-SQL 语句时有效的上下文数据库的 ID。ID of the context database that was in effect when the Transact-SQLTransact-SQL statement corresponding to this plan was compiled. 对于临时和预定义 SQL 语句,指编译这些语句时所在的数据库的 ID。For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.

此列可为空值。Column is nullable.
objectidobjectid intint 此查询计划的对象(如存储过程或用户定义函数)的 ID。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.

此列可为空值。Column is nullable.
numbernumber smallintsmallint 为存储过程编号的整数。Numbered stored procedure integer. 例如,一组的过程订单可能名为应用程序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.

此列可为空值。Column is nullable.
encryptedencrypted bitbit 指示对应的存储过程是否已加密。Indicates whether the corresponding stored procedure is encrypted.

0 = 未加密0 = not encrypted

1 = 已加密1 = encrypted

此列不可为空值。Column is not nullable.
query_planquery_plan xmlxml 包含最后一个已知的运行时使用指定的实际查询执行计划的显示计划表示形式plan_handleContains 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.

此列可为空值。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.

在以下情况下的显示计划输出等效于实际执行计划中返回query_plan返回的表的列sys.dm_exec_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:

在以下情况下简化1 中返回显示计划输出query_plan返回的表的列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:

  • 可以在中找到计划sys.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.

1开头SQL Server 2019 (15.x)SQL Server 2019 (15.x)ctp 版本 2.5 时,这是指显示计划,其中仅包含根节点运算符 (选择)。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_plansFor 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数据类型sys.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

下面的示例查询sys.dm_exec_cached_plans若要查找感兴趣的计划并复制其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.dm_exec_query_plan_statsThen, 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)