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

适用于: SQL Server 2019 及更高版本的 AZURE SQL 数据库 Azure Synapse Analytics 并行数据仓库

返回以前缓存的查询计划的上一个已知实际执行计划的等效值。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_handle 为 **varbinary (64) **。plan_handle is varbinary(64).

可以从以下动态管理对象中获取 plan_handleThe plan_handle can be obtained from the following dynamic management objects:

返回的表Table Returned

列名Column Name 数据类型Data Type 说明Description
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.
数字number 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.

此列可为空值。Column is nullable.
encrypted 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_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.

此列可为空值。Column is nullable.

注解Remarks

这是一项可以选择使用的功能。This is an opt-in feature. 若要在服务器级别启用,请使用 跟踪标志 2451。To enable at the server level, use trace flag 2451. 若要在数据库级别启用,请使用 ALTER DATABASE 作用域配置 (transact-sql)中的 LAST_QUERY_PLAN_STATS 选项。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.

的显示计划输出 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

在以下条件下,与 实际执行计划等效 的显示计划输出将在返回的表的 query_plan 列中返回 sys.dm_exec_query_plan_statsUnder 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:

在以下情况下,在返回的表的query_plan列中返回**简化的1 **显示计划输出 sys.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. dm_exec_cached_plans中找到该计划。The 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 表示只包含根节点运算符 (选择) 的显示计划。1 Refers to a Showplan that only contains the root node operator (SELECT).

在以下情况下, 不会返回任何输出 sys.dm_exec_query_plan_statsUnder 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.
    或者OR
  • 最初无法缓存查询计划。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

下面的示例查询 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_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)