通过

sys.dm_exec_query_statistics_xml（Transact-SQL）

适用于：SQL Server 2016 (13.x) 及更高版本Azure SQL 数据库Azure SQL 托管实例

返回正在进行的请求的查询执行计划。 使用此 DMV 检索具有暂时性统计信息的显示计划 XML。

语法

sys.dm_exec_query_statistics_xml(session_id)

参数

session_id

要查找批处理的会话 ID。session_id 是 smallint 类型。 session_id 可以从下列动态管理对象中获得：

返回的表

列名称 数据类型 说明
session_id smallint 会话的 ID。 不可为 Null。
request_id int 请求的 ID。 不可为 Null。
sql_handle varbinary(64) 唯一标识查询所属的批处理或存储过程的令牌。 Nullable。
plan_handle varbinary(64) 一个令牌，用于唯一标识当前正在执行的批处理的查询执行计划。 Nullable。
query_plan xml 包含包含 plan_handle 部分统计信息的查询执行计划的运行时 Showplan 表示形式。 显示计划的格式为 XML。 为包含即席 Transact-SQL 语句、存储过程调用以及用户定义函数调用等内容的每个批查询生成一个计划。 Nullable。

局限性

由于使用sys.dm_exec_query_statistics_xml DMV 执行监控存储过程时可能出现随机访问冲突（AV），因此在 SQL Server 2017 (14.x) CU 26 和 SQL Server 2019 (15.x) CU 12 中删除了 Showplan XML 属性的值<ParameterList>ParameterRuntimeValue。 在对长时间运行的存储过程进行疑难解答时，此值可能很有用。 可以使用 跟踪标志 2446 在 SQL Server 2017 （14.x） CU 31、SQL Server 2019 （15.x） CU 19 及更高版本中重新启用此值。 此跟踪标志启用运行时参数值的收集，这会产生额外的开销。

注意

跟踪标志 2446 不应在生产环境中持续启用，但仅用于时间限制的故障排除目的。 使用此跟踪标志会引入额外的和可能重要的 CPU 和内存开销，因为它会创建一个 Showplan XML 片段，其中包含运行时参数信息（无论 sys.dm_exec_query_statistics_xml 是否调用 DMV）。

在 SQL Server 2022（16.x）、Azure SQL 数据库和 Azure SQL 托管实例中，可以使用 FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION中的选项在数据库级别实现相同的功能。

注解

此系统函数从 SQL Server 2016 （13.x） Service Pack 1 开始可用。 有关详细信息，请参阅 KB 3190871。

此系统函数适用于“标准”和“轻型”查询执行统计信息分析基础结构。 有关详细信息，请参阅查询分析基础结构。

在以下情况下，返回的表中query_plan列对于sys.dm_exec_query_statistics_xml没有 Showplan 输出：

  • 如果与指定 session_id 对应的查询计划不再执行， query_plan 则返回的表的列为 null。 例如，如果捕获计划句柄的时间与计划句柄一起使用的时间之间存在时间延迟，则可能会发生此情况 sys.dm_exec_query_statistics_xml

由于 xml 数据类型中允许的嵌套级别数存在限制， sys.dm_exec_query_statistics_xml 因此无法返回满足或超过 128 个嵌套元素级别的查询计划。 在早期版本的 SQL Server 中，这种情况将导致无法返回查询计划，并生成错误 6335。 在 SQL Server 2005 （9.x） Service Pack 2 及更高版本中，该 query_plan 列返回 NULL。

权限

在 SQL Server 2019 （15.x）及更早版本中，VIEW SERVER STATE 需要对服务器具有权限。

在 SQL Server 2022（16.x）及更高版本中，需要对服务器拥有VIEW SERVER PERFORMANCE STATE权限。

需要 VIEW DATABASE STATE SQL 数据库高级层上数据库中的权限。

需要 SQL 数据库标准层和基本层上的服务器管理员或Microsoft Entra 管理员帐户。

示例

A. 查看正在运行的批处理的实时查询计划和执行统计信息

以下示例查询 sys.dm_exec_requests 以查找感兴趣的查询并从输出复制其 session_id。

SELECT *
FROM sys.dm_exec_requests;
GO

然后，为获取实时查询计划和执行统计信息，请使用已复制的 session_id 和系统函数 sys.dm_exec_query_statistics_xml。 在与运行查询的会话不同的会话中运行此查询。

SELECT * FROM sys.dm_exec_query_statistics_xml(< copied session_id >);
GO

或者，合并处理所有正在运行的请求。 在与运行查询的会话不同的会话中运行此查询。

SELECT eqs.query_plan,
       er.session_id,
       er.request_id,
       er.database_id,
       er.start_time,
       er.[status],
       er.wait_type,
       er.wait_resource,
       er.last_wait_type,
       (er.cpu_time / 1000) AS cpu_time_sec,
       (er.total_elapsed_time / 1000) / 60 AS elapsed_time_minutes,
       (er.logical_reads * 8) / 1024 AS logical_reads_KB,
       er.granted_query_memory,
       er.dop,
       er.row_count,
       er.query_hash,
       er.query_plan_hash
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_query_statistics_xml(session_id) AS eqs
WHERE er.session_id <> @@SPID;
GO

相关内容