sys.dm_exec_query_plan(Transact-SQL)

适用于:SQL ServerAzure SQL 数据库Azure SQL 托管实例Microsoft Fabric 中的 SQL 数据库

动态管理函数返回 sys.dm_exec_query_plan 计划句柄指定的批次的XML格式Showplan。 计划句柄指定的计划可以处于缓存或正在执行状态。

Showplan 的 XML 架构已发布并在此Microsoft网站上提供。 它还可在安装 SQL Server 的目录中使用。

Transact-SQL 语法约定

Syntax

sys.dm_exec_query_plan(plan_handle)

Arguments

plan_handle

一个令牌,为已执行且其计划位于计划缓存中或当前正在执行的批次唯一标识查询执行计划。 plan_handle为 varbinary(64)。

可以从以下动态管理对象获取:plan_handle

返回的表

列名称 数据类型 说明
dbid smallint 在编译对应于此计划的 Transact-SQL 语句时有效的上下文数据库的 ID。 对于临时和预定义 SQL 语句,指编译这些语句时所在的数据库的 ID。

此列可为空值。
objectid int 此查询计划的对象(如存储过程或用户定义函数)的 ID。 对于临时批次和预备批次,该列为 NULL。

此列可为空值。
number smallint 为存储过程编号的整数。 例如,一组用于 命令 应用的程序可以命名 orderproc;1为 , orderproc;2依此类推。 对于临时批次和预备批次,该列为 NULL。

此列可为空值。
encrypted bit 指示对应的存储过程是否已加密。

0 = 未加密

1 = 已加密

此列不可为空值。
query_plan xml 包含由 S plan_handle指定的查询执行计划的编译时 Showplan 表示。 显示计划的格式为 XML。 为包含即席 Transact-SQL 语句、存储过程调用以及用户定义函数调用等内容的每个批查询生成一个计划。

此列可为空值。

注解

在以下情况下,返回的表中query_plan列对于sys.dm_exec_query_plan没有 Showplan 输出:

  • 如果通过 指定的 plan_handle 查询计划已被从计划缓存中驱逐,则 query_plan 返回的表列为 NULL。 例如,如果捕获计划句柄与使用之间存在时间延迟,就可能发生这种情况 sys.dm_exec_query_plan

  • 有些 Transact-SQL 语句未放入缓存,如大容量操作语句或包含大于 8 KB 的字符串文字的语句。 除非批处理当前正在执行,否则无法通过使用 sys.dm_exec_query_plan 该语句检索到这些语句的 XML Showplan,因为它们不存在于缓存中。

  • 如果 Transact-SQL 批处理或存储过程包含调用用户自定义函数或动态SQL调用,例如使用EXEC字符串,则该用户定义函数的编译后的XML Showplan不会包含在该批处理或存储过程返回 sys.dm_exec_query_plan 的表中。 相反,你必须单独调用对 sys.dm_exec_query_plan 应用户定义函数的计划句柄。

当临时查询使用简单或强制参数化时,列 query_plan 中只包含语句文本,而不包含实际的查询计划。 要返回查询计划,调用 sys.dm_exec_query_plan 准备好参数化查询的计划句柄。 您可以通过引用 sqlsys.syscacheobjects 视图的列还是 sys.dm_exec_sql_text 动态管理视图的文本列来判断查询是参数化的。

注释

由于 xml 数据类型允许的嵌套层数限制, sys.dm_exec_query_plan 无法返回满足或超过128层嵌套元素的查询计划。 在早期版本的 SQL Server 中,这种情况将导致无法返回查询计划,并生成错误 6335。 在 SQL Server 2005 (9.x) Service Pack 2 及更高版本中,该 query_plan 列返回 NULL。
可以使用 sys.dm_exec_text_query_plan (Transact-SQL) 动态管理功能以文本格式返回查询计划的输出。

Permissions

执行 sys.dm_exec_query_plan时,用户必须是 系统管理员 固定服务器角色的成员或拥有 VIEW SERVER STATE 服务器权限。

SQL Server 2022及更高版本的权限

要求具有对服务器的 VIEW SERVER PERFORMANCE STATE 权限。

Examples

以下示例展示了如何使用 sys.dm_exec_query_plan 动态管理视图。

要查看 XML Showplans,请在 SQL Server Management Studio 的 查询编辑器 中执行以下查询,然后在 Management Studio 摘要窗格中返回的表格列sys.dm_exec_query_plan中选择 ShowPlanXMLquery_plan。 要将 XML Showplan 保存为文件,右键点击列中的 query_planShowPlanXML,选择“另存结果为”,并以该格式<file name>.sqlplan命名文件;例如, MyXMLShowplan.sqlplan。

A. 检索运行速度缓慢的 Transact-SQL 查询或批查询的缓存查询计划

各种类型的 Transact-SQL 批处理(例如即席批处理、存储过程和用户定义的函数)的查询计划将缓存在称为计划缓存的内存区域中。 每个缓存查询计划均由称作计划句柄的唯一标识符进行标识。 你可以用动态管理视图指定这个计划句柄 sys.dm_exec_query_plan ,以获取特定 Transact-SQL 查询或批处理的执行计划。

如果 Transact-SQL 查询或批查询在 SQL Server 的特定连接上运行的时间很长,请检索该查询或批查询的执行计划,以查找导致延迟的原因。 以下示例显示如何检索运行速度缓慢的查询或批查询的 XML 显示计划。

注释

要运行这个例子,将 和 session_idplan_handle 的值替换为你服务器特定的值。

首先,通过存储 sp_who 过程获取执行查询或批处理进程的会话ID(SPID):

exec sp_who;  
GO  

返回 的结果 sp_who 集表明会话 ID 为 54。 您可以使用动态 sys.dm_exec_requests 管理视图的会话 ID 来通过以下查询获取计划句柄:

SELECT * FROM sys.dm_exec_requests  
WHERE session_id = 54;  
GO  

返回的 sys.dm_exec_requests 表表明,该慢速查询或批处理的计划句柄为 0x06000100A27E7C1FA821B10600,你可以指定 plan_handle 为 sys.dm_exec_query_plan 参数,以以下方式检索执行计划。 慢速查询或批处理的执行计划以XML格式存储在由返回sys.dm_exec_query_plan的表列中query_plan。

SELECT * 
FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);  
GO  

B。 从计划缓存中检索每个查询计划

若要检索驻留在计划缓存中的所有查询计划的快照,请通过查询 sys.dm_exec_cached_plans 动态管理视图来检索缓存中所有查询计划的计划句柄。 计划句柄存储在 plan_handle 的 sys.dm_exec_cached_plans 列中。 然后,使用 CROSS APPLY 运算符将计划句柄传递给 sys.dm_exec_query_plan,如下所示。 当前在计划缓存中的每个计划的 XML 显示计划输出位于返回的表的 query_plan 列中。

SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO  

C。 检索服务器已从计划缓存中收集了其查询统计信息的每个查询计划

若要检索服务器已收集了其当前驻留在计划缓存中的统计信息的所有查询计划的快照,请通过查询 sys.dm_exec_query_stats 动态管理视图来检索缓存中这些计划的计划句柄。 计划句柄存储在 plan_handle 的 sys.dm_exec_query_stats 列中。 然后,使用 CROSS APPLY 运算符将计划句柄传递给 sys.dm_exec_query_plan,如下所示。 服务器已收集了其驻留在计划缓存中的统计信息的每个计划的 XML 显示计划输出在返回的表的 query_plan 列中。

SELECT * 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);  
GO  

D. 按平均 CPU 时间检索有关前五个查询的信息

以下示例为前五个查询返回查询计划和平均 CPU 时间。

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],  
   plan_handle, query_plan   
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)  
ORDER BY total_worker_time/execution_count DESC;  
GO