sys.dm_exec_text_query_plan (Transact-SQL)

  • 项目

为 Transact-SQL 批处理或批处理中的特定语句返回文本格式的显示计划。 执行计划句柄指定的查询计划可处于缓存状态或正在执行状态。 此表值函数与 sys.dm_exec_query_plan (Transact-SQL) 类似，但存在以下差异：

  • 查询计划的输出以文本格式返回。

  • 查询计划的输出无大小限制。

  • 可以指定批处理内的单个语句。

适用范围：SQL Server（SQL Server 2008 至当前版本），Windows Azure SQL Database（初始版本至当前版本）。

主题链接图标 Transact-SQL 语法约定

语法

sys.dm_exec_text_query_plan 
( 
    plan_handle 
    , { statement_start_offset | 0 | DEFAULT }
        , { statement_end_offset | -1 | DEFAULT }
)

参数

  • plan_handle
    为已缓存或当前正在执行的批查询唯一标识查询计划。 plan_handle 的数据类型为 varbinary(64)。

    可以从下列动态管理对象中获得计划句柄：

    sys.dm_exec_cached_plans

    sys.dm_exec_query_stats

    sys.dm_exec_requests

  • statement_start_offset | 0 | DEFAULT
    指示行所说明的查询在其批处理或持久性对象文本中的起始位置（字节）。 statement_start_offset 是 int。 值 0 指示批处理的起始处。 默认值为 0。

    可以从下列动态管理对象中获得语句起始偏移量：

    sys.dm_exec_query_stats

    sys.dm_exec_requests

  • statement_end_offset | -1 | DEFAULT
    指示行所说明的查询在其批处理或持久性对象文本中的结束位置（字节）。

    statement_start_offset 是 int。

    值 -1 指示批处理的结尾处。 默认值为 -1。

返回的表

列名

数据类型

说明

dbid

smallint

在编译对应于此计划的 Transact-SQL 语句时有效的上下文数据库的 ID。 对于临时和预定义 SQL 语句，指编译这些语句时所在的数据库的 ID。

此列可为空值。

objectid

int

此查询计划的对象（如存储过程或用户定义函数）的 ID。 对于即席批查询和已准备好的批查询，此列为 null。

此列可为空值。

number

smallint

为存储过程编号的整数。 例如，用于 orders 应用程序的一组过程可命名为 orderproc;1、orderproc;2 等等。 对于即席批查询和已准备好的批查询，此列为 null。

此列可为空值。

encrypted

bit

指示对应的存储过程是否已加密。

0 = 未加密

1 = 已加密

此列不可为空值。

query_plan

nvarchar(max)

包含使用 plan_handle 指定的查询执行计划的编译时显示计划表示形式。 显示计划采用文本格式。 为包含临时 Transact-SQL 语句、存储过程调用以及用户定义函数调用等内容的每个批处理生成一个计划。

此列可为空值。

注释

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

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

  • 有些 Transact-SQL 语句未放入缓存，如大容量操作语句或包含大于 8 KB 的字符串文字的语句。 由于这些语句不在缓存中，因此无法使用 sys.dm_exec_text_query_plan 来检索这些语句的显示计划。

  • 如果 Transact-SQL 批处理或存储过程包含对用户定义函数或动态 SQL 的调用，例如使用 EXEC (string)，则 sys.dm_exec_text_query_plan 为批处理或存储过程返回的表中不会包含用户定义函数的已编译 XML 显示计划。 而您必须为与用户定义函数对应的 plan_handle 单独调用 sys.dm_exec_text_query_plan。

当临时查询使用简单参数化或强制参数化时，query_plan 列将仅包含语句文本，而非实际查询计划。 若要返回查询计划，则应为预定义参数化查询的计划句柄调用 sys.dm_exec_text_query_plan。 可以通过引用 sys.syscacheobjects 视图的 sql 列或 sys.dm_exec_sql_text 动态管理视图的文本列来确定查询是否已参数化。

权限

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

示例

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

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

备注

若要运行该示例，请使用服务器特定的值替换 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。 以下示例返回指定计划句柄的查询计划，并使用默认值 0 和 -1 返回查询或批处理中的所有语句。

USE master;
GO
SELECT query_plan 
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);
GO

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

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

USE master;
GO
SELECT * 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT);
GO

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

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

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset);
GO

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

以下示例返回前五个查询的查询计划和平均 CPU 时间。 sys.dm_exec_text_query_plan 函数指定默认值 0 和 -1 以返回查询计划中批处理的所有语句。

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_text_query_plan(qs.plan_handle, 0, -1)
ORDER BY total_worker_time/execution_count DESC;
GO

请参阅

参考

sys.dm_exec_query_plan (Transact-SQL)