sys.dm_exec_query_plan (Transact-SQL)

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

已发布显示计划的 XML 架构，并可在此 Microsoft 网站中找到。 也可在 SQL Server 安装目录中找到该架构。

Transact-SQL 语法约定

语法

sys.dm_exec_query_plan ( plan_handle )

参数

• plan_handle
  为已缓存或当前正在执行的批查询唯一标识查询计划。

  plan_handle 的数据类型为 varbinary(64)。 可以从下列动态管理对象中获得 plan_handle：

  sys.dm_exec_cached_plans

  sys.dm_exec_query_stats

  sys.dm_exec_requests

返回的表

注释

在以下条件下，不会在为 sys.dm_exec_query_plan 返回的表的 query_plan 列中返回显示计划输出：

• 如果已从计划缓存中逐出了使用 plan_handle 指定的查询计划，则返回表的 query_plan 列为空值。 例如，如果在捕获计划句柄的时间与将其用于 sys.dm_exec_query_plan 的时间之间存在时间延迟，则可能会出现该情况。

• 有些 Transact-SQL 语句未放入缓存，如大容量操作语句或包含大于 8 KB 的字符串文字的语句。 除非当前正在执行此批查询，否则由于这些语句不在缓存中，而无法使用 sys.dm_exec_query_plan 来检索这些语句的 XML 显示计划。

• 如果 Transact-SQL 批查询或存储过程包含对用户定义函数或动态 SQL 的调用，例如使用 EXEC (string)，则 sys.dm_exec_query_plan 为批查询或存储过程返回的表中不会包含用户定义函数的已编译 XML 显示计划。 而您必须单独为与用户定义函数对应的计划句柄调用 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 Service Pack 2 和更高版本中，query_plan 列返回 NULL。 可以使用 sys.dm_exec_text_query_plan (Transact-SQL) 动态管理函数以文本格式返回查询计划的输出。

权限

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

示例

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

若要查看 XML 显示计划，请在 SQL Server Management Studio 的查询编辑器中执行以下查询，然后在 sys.dm_exec_query_plan 返回的表的 query_plan 列中单击 ShowPlanXML。 XML 显示计划显示在 Management Studio 摘要窗格中。 若要将 XML 显示计划保存到文件中，请右键单击 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 显示计划。

首先，使用 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，您可以使用 sys.dm_exec_query_plan 将该句柄指定为 plan_handle 参数，用于检索 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 动态管理视图来检索缓存中所有查询计划的计划句柄。 计划句柄存储在 sys.dm_exec_cached_plans 的 plan_handle 列中。 然后，使用 CROSS APPLY 运算符将计划句柄传递给 sys.dm_exec_query_plan，如下所示。 当前在计划缓存中的每个计划的 XML 显示计划输出位于返回的表的 query_plan 列中。

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

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

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

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats 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)

sys.dm_exec_text_query_plan (Transact-SQL)

概念

Showplan 逻辑运算符和物理运算符参考