sys.dm_exec_query_plan (Transact-SQL)

项目
07/05/2024

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例

以 XML 格式返回计划句柄指定的批查询的显示计划。计划句柄指定的计划可以处于缓存或正在执行状态。

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

语法

sys.dm_exec_query_plan(plan_handle)

参数

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	包含使用 plan_handle 指定的查询执行计划的编译时显示计划表示形式。显示计划的格式为 XML。为包含即席 Transact-SQL 语句、存储过程调用以及用户定义函数调用等内容的每个批查询生成一个计划。此列可为空值。

注解

在以下情况下，返回的表的query_plan列中没有返回sys.dm_exec_query_plan显示计划输出：

如果已从计划缓存中逐出了使用 plan_handle 指定的查询计划，则返回表的 query_plan 列为空值。例如，如果在捕获计划句柄与与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 。可以通过引用 sys.syscacheobjects 视图的 sql 列或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）动态管理功能以文本格式返回查询计划的输出。

权限

若要执行sys.dm_exec_query_plan，用户必须是 sysadmin 固定服务器角色的成员或具有VIEW SERVER STATE对服务器的权限。

SQL Server 2022 及更高版本的权限

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

示例

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

若要查看 XML Showplans，请在 SQL Server Management Studio 的查询编辑器中执行以下查询，然后在sys.dm_exec_query_plan返回的表的query_plan列中单击 ShowPlanXML。 XML 显示计划显示在 Management Studio 摘要窗格中。若要将 XML Showplan 保存到文件，请在query_plan列中右键单击 ShowPlanXML，单击“另存为”，将文件命名为 file_name.sqlplan> 格式<;例如 MyXMLShowplan.sqlplan。

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

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

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

注意

若要运行该示例，请使用服务器特定的值替换 session_id 和 plan_handle 的值。

首先，使用 sp_who 存储过程检索正在执行查询或批查询的进程的服务器进程 ID (SPID)。

USE master;  
GO  
exec sp_who;  
GO

由 sp_who 返回的结果集指示 SPID 为 54。可以在 sys.dm_exec_requests 动态管理视图中使用该 SPID，以便使用以下查询来检索计划句柄：

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

sys.dm_exec_requests返回的表指示慢速运行查询或批处理的计划句柄是0x06000100A27E7C1FA821B10600，可以指定为plan_handle参数，以 sys.dm_exec_query_plan XML 格式检索执行计划，如下所示。慢跑查询或批处理的 XML 格式的执行计划包含在返回的sys.dm_exec_query_plan表的query_plan列中。

USE master;  
GO  
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 列中。

USE master;  
GO  
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 列中。

USE master;  
GO  
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

另请参阅

动态管理视图和函数 (Transact-SQL)
sys.dm_exec_cached_plans (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sp_who (Transact-SQL)
Showplan 逻辑运算符和物理运算符参考
 sys.dm_exec_text_query_plan （Transact-SQL）

通过