查询分析基础结构

项目
11/02/2024

SQL Server 数据库引擎提供了访问查询执行计划的运行时信息的功能。出现性能问题时，最重要的操作之一是准确了解正在执行的工作负载以及如何驱动使用资源。为此，访问实际执行计划是很重要的。

虽然查询完成是实际查询计划可用性的先决条件，但实时查询统计信息可以提供对查询执行过程的实时见解，因为数据是从一个查询计划运算符移动到另一个。实时查询计划显示总体查询进度和操作员级运行时执行统计信息（例如处理的行数、运行时间、操作员进度等）。由于此数据是实时可用的，无需等待完成查询，因此这些执行统计信息对于调试查询性能问题非常有用，例如长时间运行查询以及无限期运行而从未完成过的查询。

标准查询执行统计信息分析基础结构

必须启用查询执行统计信息配置文件基础结构或标准分析，以收集有关执行计划的信息，即行数、CPU 和 I/O 使用情况。以下收集目标会话的执行计划信息的方法利用标准分析基础结构：

注意

单击 SQL Server Management Studio 中的“包含实时查询统计信息”按钮可以利用标准分析基础结构。
在更高版本的 SQL Server 中，如果启用了轻型分析基础结构，则在通过活动监视器查看或直接查询 sys.dm_exec_query_profiles DMV 时通过实时查询统计信息而非标准分析加以利用。

以下为所有会话全局收集执行计划信息的方法利用标准分析基础结构：

query_post_execution_showplan 扩展事件。若要启用扩展事件，请参阅 Monitor System Activity Using Extended Events。
SQL Trace 和 SQL Server Profiler 中的 Showplan XML 跟踪事件。有关此跟踪事件的详细信息，请参阅 Showplan XML 事件类。

当运行使用 query_post_execution_showplan 事件的扩展事件会话时，还会填充 sys.dm_exec_query_profiles DMV，它使用活动监视器或直接查询 DMV，为所有会话启用实时查询统计。有关详细信息，请参阅 Live Query Statistics。

轻型查询执行统计信息分析基础结构

从 SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x) 开始，引入了新的轻型查询执行统计信息基础结构或轻型分析。

注意

本机编译存储过程不支持轻型分析。

轻型查询执行统计信息分析基础结构 v1

适用于：SQL Server（SQL Server 2014 (12.x) SP2 到 SQL Server 2016 (13.x)）。

从 SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x) 开始，通过引入轻型分析，减少了收集执行计划信息的性能开销。和标准分析不同，轻型分析不收集 CPU 运行时信息。但是，轻型分析仍收集行计数和 I/O 使用情况信息。

还引入了一个新的利用轻型分析的 query_thread_profile 扩展事件。此扩展事件公开了每个运算符的执行统计信息，从而可以更深入地了解每个节点和线程的性能。使用此扩展事件的示例会话可以按下面的示例进行配置：

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

注意

有关查询分析的性能开销的详细信息，请参阅博客文章Developers Choice: Query progress - anytime, anywhere（开发人员选择：随时随地查询进度）。

当运行使用 query_thread_profile 事件的扩展事件会话时，还会使用轻型分析填充 sys.dm_exec_query_profiles DMV，它使用活动监视器或直接查询 DMV，为所有会话启用实时查询统计。

轻型查询执行统计信息分析基础结构 v2

适用于：SQL Server（SQL Server 2016 (13.x) SP1 到 SQL Server 2017 (14.x)）。

SQL Server 2016 (13.x) SP1 包括具有最小开销的轻型分析的修订版本。对于“适用范围”中提到的上述版本，使用跟踪标志 7412也可以全局启用轻型分析。引入了新的 DMF sys.dm_exec_query_statistics_xml 以返回正在进行的请求的查询执行计划。

从 SQL Server 2016 (13.x) SP2 CU3 和 SQL Server 2017 (14.x) CU11 开始，如果未全局启用轻型分析，则可以使用新的 USE HINT 查询提示参数 QUERY_PLAN_PROFILE 以查询级别启用轻型分析，且适用于任何会话。当包含此新提示的查询完成时，还会输出新的 query_plan_profile 扩展事件，该事件提供类似于 query_post_execution_showplan 扩展事件的实际执行计划 XML。

注意

即使未使用查询提示，query_plan_profile 扩展事件也会利用轻量分析。

使用 query_plan_profile 扩展事件的示例会话可以像下面的示例一样进行配置：

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

轻型查询执行统计信息分析基础结构 v3

适用于：SQL Server（从 SQL Server 2019 (15.x) 开始）和 Azure SQL 数据库

SQL Server 2019 (15.x) 和 Azure SQL 数据库包括一个新修订的轻型分析版本，用于收集所有执行的行计数信息。 SQL Server 2019 (15.x) 和 Azure SQL 数据库上默认启用了轻型分析。从 SQL Server 2019 (15.x) 开始，跟踪标志 7412 不再有效。可以使用 LIGHTWEIGHT_QUERY_PROFILING 数据库范围配置ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF; 在数据库级别禁用轻量级分析。

引入了新的 DMF sys.dm_exec_query_plan_stats 以返回大多数查询的最后已知实际执行计划的等效项，称为“最后查询计划统计信息”。可以使用 LAST_QUERY_PLAN_STATS 数据库范围配置ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON; 在数据库级别启用最后查询计划统计信息。

新的 query_post_execution_plan_profile 扩展事件基于轻型分析收集实际执行计划的等效项，与使用标准分析的 query_post_execution_showplan 不同。 SQL Server 2017 (14.x) 从 CU14 开始也提供此事件。可以像如下所示对使用 query_post_execution_plan_profile 扩展事件的示例会话进行配置：

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

示例 1 - 使用标准分析的扩展事件会话

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

示例 2 - 使用轻型分析的扩展事件会话

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

查询分析基础结构使用指南

下表总结了用于全局（在服务器级别）或在单个会话中启用标准分析或轻型分析的操作。此外还包括其操作可用的最早版本。

范围	标准分析	轻型分析
全局	XEvent 会话与 `query_post_execution_showplan` XE;从 SQL Server 2012 （11.x）开始	跟踪标志 7412；从 SQL Server 2016 (13.x) SP1 开始
全局	包含 `Showplan XML` 跟踪事件的 SQL 跟踪和 SQL Server Profiler；从 SQL Server 2000 开始	XEvent 会话与 `query_thread_profile` XE;从 SQL Server 2014 （12.x） SP2 开始
全局	-	XEvent 会话与 `query_post_execution_plan_profile` XE;从 SQL Server 2017 （14.x） CU14 和 SQL Server 2019 （15.x）开始
会话	使用 `SET STATISTICS XML ON`；从 SQL Server 2000 开始	将 `QUERY_PLAN_PROFILE` 查询提示与 XE 的 `query_plan_profile` XEvent 会话一起使用;从 SQL Server 2016 （13.x） SP2 CU3 和 SQL Server 2017 （14.x） CU11 开始
会话	使用 `SET STATISTICS PROFILE ON`；从 SQL Server 2000 开始	-
会话	单击 SSMS 中的“实时查询统计信息”按钮；从 SQL Server 2014 (12.x) SP2 开始	-

注解

重要

由于执行引用 sys.dm_exec_query_statistics_xml 的监控存储过程时可能出现随机访问冲突，请确保在 SQL Server 2016 (13.x) 和 SQL Server 2017 (14.x) 中安装 KB 4078596。

从轻型分析 v2 开始，其开销很低，任何尚未受 CPU 限制的服务器都可连续运行轻型分析，并允许数据库专业人员随时使用任何正在运行的执行，例如使用活动监视器或直接查询 sys.dm_exec_query_profiles，并获取运行时统计信息的查询计划。

有关查询分析的性能开销的详细信息，请参阅博客文章Developers Choice: Query progress - anytime, anywhere（开发人员选择：随时随地查询进度）。

注意

利用轻型分析的扩展事件将使用来自标准分析的信息，以防早已启用了标准分析基础结构。例如，使用 query_post_execution_showplan 的扩展事件会话正在运行，而另一个使用 query_post_execution_plan_profile 的会话已启动。第二个会话仍将使用来自标准分析的信息。

注意

在 SQL Server 2017 (14.x) 上，轻型分析功能默认关闭，但在启动依赖于 query_post_execution_plan_profile 的 XEvent 跟踪时激活，然后在跟踪停止时再次停用。因此，如果在 SQL Server 2017 (14.x) 实例上经常启动和停止基于 query_post_execution_plan_profile 的 Xevent 跟踪，则强烈建议使用跟踪标志 7412 在全局级别激活轻量级分析，避免重复产生激活/停用开销。

另请参阅

监视和优化性能
 性能监视和优化工具
 打开活动监视器 (SQL Server Management Studio)
活动监视器
 相关视图、函数和过程
 使用扩展事件监视系统活动
 sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
跟踪标志
 Showplan 逻辑运算符和物理运算符参考
 实际执行计划
 实时查询统计信息

通过