查询分析基础结构Query Profiling Infrastructure

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server 数据库引擎SQL Server Database Engine 提供了访问查询执行计划的运行时信息的功能。The SQL Server 数据库引擎SQL Server Database Engine provides the ability to access runtime information on query execution plans. 出现性能问题时,最重要的操作之一是准确了解正在执行的工作负载以及如何驱动使用资源。One of the most important actions when a performance issue occurs, is to get precise understanding on the workload that is executing and how resource usage is being driven. 为此,访问实际执行计划是很重要的。For this, access to the actual execution plan is important.

虽然查询完成是实际查询计划可用性的先决条件,但实时查询统计信息可以提供对查询执行过程的实时见解,因为数据是从一个查询计划运算符移动到另一个。While query completion is a prerequisite for the availability of an actual query plan, live query statistics can provide real-time insights into the query execution process as the data flows from one query plan operator to another. 实时查询计划显示总体查询进度和操作员级运行时执行统计信息(例如处理的行数、经过的时间、操作员进度等)。由于此数据是实时可用的,无需等待完成查询,因此这些执行统计信息对于调试查询性能问题非常有用,例如长时间运行查询以及无限期运行而从未完成过的查询。The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc. Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues, such as long running queries, and queries that run indefinitely and never finish.

标准查询执行统计信息分析基础结构The standard query execution statistics profiling infrastructure

必须启用查询执行统计信息配置文件基础结构或标准分析,以收集有关执行计划的信息,即行数、CPU 和 I/O 使用情况 。The query execution statistics profile infrastructure, or standard profiling, must be enabled to collect information about execution plans, namely row count, CPU and I/O usage. 以下收集目标会话的执行计划信息的方法利用标准分析基础结构: The following methods of collecting execution plan information for a target session leverage the standard profiling infrastructure:

备注

单击 SQL Server Management StudioSQL Server Management Studio 中的“包含实时查询统计信息”按钮可以利用标准分析基础结构 。Clicking the button Include Live Query Statistics in SQL Server Management StudioSQL Server Management Studio leverages the standard profiling infrastructure.
在更高版本的 SQL ServerSQL Server 中,如果启用了轻量级分析基础结构,则在通过活动监视器查看或直接查询 sys.dm_exec_query_profiles DMV 时通过实时查询统计信息而非标准分析加以利用。In higher versions of SQL ServerSQL Server, if the lightweight profiling infrastructure is enabled, then it is leveraged by live query statistics instead of standard profiling when viewed through Activity Monitor or directly querying the sys.dm_exec_query_profiles DMV.

以下为所有会话全局收集执行计划信息的方法利用标准分析基础结构: The following methods of collecting execution plan information globally for all sessions leverage the standard profiling infrastructure:

当运行使用 query_post_execution_showplan 事件的扩展事件会话时,还会填充 sys.dm_exec_query_profiles DMV,它使用活动监视器或直接查询 DMV,为所有会话启用实时查询统计。When running an extended event session that uses the query_post_execution_showplan event, then the sys.dm_exec_query_profiles DMV is also populated, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV. 有关详细信息,请参阅 Live Query StatisticsFor more information, see Live Query Statistics.

轻型查询执行统计信息分析基础结构The lightweight query execution statistics profiling infrastructure

SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,引入了新的轻型查询执行统计信息概要分析基础结构或轻型分析 。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), a new lightweight query execution statistics profiling infrastructure, or lightweight profiling was introduced.

备注

本机编译存储过程不支持轻型分析。Natively compiled stored procedures are not supported with lightweight profiling.

轻型查询执行统计信息分析基础结构 v1Lightweight query execution statistics profiling infrastructure v1

适用对象SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 到 SQL Server 2016 (13.x)SQL Server 2016 (13.x))。Applies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 through SQL Server 2016 (13.x)SQL Server 2016 (13.x)).

SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 和 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,通过引入轻型分析,减少了收集执行计划信息的性能开销。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), the performance overhead to collect information about execution plans was reduced with the introduction of lightweight profiling. 和标准分析不同,轻型分析不收集 CPU 运行时信息。Unlike standard profiling, lightweight profiling does not collect CPU runtime information. 但是,轻型分析仍收集行计数和 I/O 使用情况信息。However, lightweight profiling still collects row count and I/O usage information.

还引入了一个新的利用轻型分析的 query_thread_profile 扩展事件。A new query_thread_profile extended event was also introduced that leverages lightweight profiling. 此扩展事件公开了每个运算符的执行统计信息,从而可以更深入地了解每个节点和线程的性能。This extended event exposes per-operator execution statistics allowing more insight on the performance of each node and thread. 使用此扩展事件的示例会话可以按下面的示例进行配置:A sample session using this extended event can be configured as in the below example:

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(开发人员之选:随时随地查询进度)。For more information on the performance overhead of query profiling, see the blog post Developers Choice: Query progress - anytime, anywhere.

当运行使用 query_thread_profile 事件的扩展事件会话时,还会使用轻型分析填充 sys.dm_exec_query_profiles DMV,它使用活动监视器或直接查询 DMV,为所有会话启用实时查询统计。When running an extended event session that uses the query_thread_profile event, then the sys.dm_exec_query_profiles DMV is also populated using lightweight profiling, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV.

轻型查询执行统计信息分析基础结构 v2Lightweight query execution statistics profiling infrastructure v2

适用对象SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 到 SQL Server 2017 (14.x)SQL Server 2017 (14.x))。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 through SQL Server 2017 (14.x)SQL Server 2017 (14.x)).

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 包括具有最小开销的轻型分析的修订版本。SP1 includes a revised version of lightweight profiling with minimal overhead. 对于“适用范围” 中提到的上述版本,使用跟踪标志 7412也可以全局启用轻型分析。Lightweight profiling can also be enabled globally using trace flag 7412 for the versions stated above in Applies to. 引入了新的 DMF sys.dm_exec_query_statistics_xml 以返回正在进行的请求的查询执行计划。A new DMF sys.dm_exec_query_statistics_xml is introduced to return the query execution plan for in-flight requests.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11 开始,如果未全局启用轻型分析,则可以使用新的 USE HINT查询提示参数 QUERY_PLAN_PROFILE 以查询级别启用轻型分析,且适用于任何会话。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11, if lightweight profiling is not enabled globally then the new USE HINT query hint argument QUERY_PLAN_PROFILE can be used to enable lightweight profiling at the query level, for any session. 当包含此新提示的查询完成时,还会输出新的 query_plan_profile 扩展事件,该事件提供类似于 query_post_execution_showplan 扩展事件的实际执行计划 XML。When a query that contains this new hint finishes, a new query_plan_profile extended event is also output that provides an actual execution plan XML similar to the query_post_execution_showplan extended event.

备注

即使未使用查询提示,query_plan_profile 扩展事件也会利用轻量分析 。The query_plan_profile extended event also leverages lightweight profiling even if the query hint is not used.

使用 query_plan_profile 扩展事件的示例会话可以像下面的示例一样进行配置 :A sample session using the query_plan_profile extended event can be configured like the example below:

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);

轻型查询执行统计信息分析基础结构 v3Lightweight query execution statistics profiling infrastructure v3

适用对象SQL ServerSQL Server(从 SQL Server 2019(预览版)SQL Server 2019 preview 开始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019(预览版)SQL Server 2019 preview)

SQL Server 2019(预览版)SQL Server 2019 preview 包括一个新修订的轻型分析版本,用于收集所有执行的行计数信息。includes a newly revised version of lightweight profiling collecting row count information for all executions. 默认情况下,SQL Server 2019(预览版)SQL Server 2019 preview 中已启用轻型分析,跟踪标志 7412 无效。Lightweight profiling is enabled by default on SQL Server 2019(预览版)SQL Server 2019 preview and trace flag 7412 has no effect. 可以使用 LIGHTWEIGHT_QUERY_PROFILING 数据库范围配置 ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF; 在数据库级别禁用轻量级分析。Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

引入了新的 DMF sys.dm_exec_query_plan_stats 以返回大多数查询的最后已知实际执行计划的等效项,称为“最后查询计划统计信息” 。A new DMF sys.dm_exec_query_plan_stats is introduced to return the equivalent of the last known actual execution plan for most queries, and is called last query plan statistics. 可以使用 LAST_QUERY_PLAN_STATS 数据库范围配置 ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON; 在数据库级别启用最后查询计划统计信息。The last query plan statistics can be enabled at the database level using the LAST_QUERY_PLAN_STATS database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

新的 query_post_execution_plan_profile 扩展事件基于轻型分析收集实际执行计划的等效项,与使用标准分析的 query_post_execution_showplan 不同 。A new query_post_execution_plan_profile extended event collects the equivalent of an actual execution plan based on lightweight profiling, unlike query_post_execution_showplan which uses standard profiling. 可以像如下所示对使用 query_post_execution_plan_profile 扩展事件的示例会话进行配置 :A sample session using the query_post_execution_plan_profile extended event can be configured like the example below:

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 - 使用标准分析的扩展事件会话Example 1 - Extended Event session using standard profiling

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 - 使用轻型分析的扩展事件会话Example 2 - Extended Event session using lightweight profiling

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);

查询分析基础结构使用指南Query Profiling Infrastruture usage guidance

下表总结了用于全局(在服务器级别)或在单个会话中启用标准分析或轻型分析的操作。The following table summarizes the actions to enable either standard profiling or lightweight profiling, both globally (at the server level) or in a single session. 此外还包括其操作可用的最早版本。Also includes the earliest version for which the action is available.

范围Scope 标准分析Standard Profiling 轻型分析Lightweight Profiling
GlobalGlobal 包含 query_post_execution_showplan XE 的 xEvent 会话;从 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 开始xEvent session with the query_post_execution_showplan XE; Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) 跟踪标志 7412;从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 开始Trace Flag 7412; Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1
GlobalGlobal 包含 Showplan XML 跟踪事件的 SQL 跟踪和 SQL Server Profiler;从 SQL Server 2000 开始SQL Trace and SQL Server Profiler with the Showplan XML trace event; Starting with SQL Server 2000 包含 query_thread_profile XE 的 xEvent 会话;从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 开始xEvent session with the query_thread_profile XE; Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2
GlobalGlobal - 包含 query_post_execution_plan_profile XE 的 xEvent 会话;从 SQL Server 2019(预览版)SQL Server 2019 preview 开始xEvent session with the query_post_execution_plan_profile XE; Starting with SQL Server 2019(预览版)SQL Server 2019 preview
SessionSession 使用 SET STATISTICS XML ON;从 SQL Server 2000 开始Use SET STATISTICS XML ON; Starting with SQL Server 2000 QUERY_PLAN_PROFILE 查询提示与包含 query_plan_profile XE 的 xEvent 会话一起使用;从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 和 SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11 开始Use the QUERY_PLAN_PROFILE query hint together with an xEvent session with the query_plan_profile XE; Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11
SessionSession 使用 SET STATISTICS PROFILE ON;从 SQL Server 2000 开始Use SET STATISTICS PROFILE ON; Starting with SQL Server 2000 -
SessionSession 单击 SSMS 中的实时查询统计信息按钮;从 SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 开始Click the Live Query Statistics button in SSMS; Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 -

RemarksRemarks

重要

由于在执行引用 sys.dm_exec_query_statistics_xml 的监视存储过程时可能存在随机 AV,因此请确保在 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中安装了 KB 4078596Due to a possible random AV while executing a monitoring stored procedure that references sys.dm_exec_query_statistics_xml, ensure KB 4078596 is installed in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).

从轻型分析 v2 开始,其开销很低,任何尚未受 CPU 限制的服务器都可连续运行轻型分析,并允许数据库专业人员随时使用任何正在运行的执行,例如使用活动监视器或直接查询 sys.dm_exec_query_profiles,并获取运行时统计信息的查询计划 。Starting with lightweight profiling v2 and its low overhead, any server that is not already CPU bound can run lightweight profiling continuously, and allow database professionals to tap into any running execution at any time, for example using Activity Monitor or directly querying sys.dm_exec_query_profiles, and get the query plan with runtime statistics.

有关查询分析的性能开销的详细信息,请参阅博客文章Developers Choice:Query progress - anytime, anywhere(开发人员之选:随时随地查询进度)。For more information on the performance overhead of query profiling, see the blog post Developers Choice: Query progress - anytime, anywhere.

备注

利用轻型分析的扩展事件将使用来自标准分析的信息,以防早已启用了标准分析基础结构。Extended Events that leverage lightweight profiling will use information from standard profiling in case the standard profiling infrastructure is already enabled. 例如,使用 query_post_execution_showplan 的扩展事件会话正在运行,而另一个使用 query_post_execution_plan_profile 的会话已启动。For example, an extended event session using query_post_execution_showplan is running, and another session using query_post_execution_plan_profile is started. 第二个会话仍将使用来自标准分析的信息。The second session will still use information from standard profiling.

另请参阅See Also

监视和优化性能 Monitor and Tune for Performance
性能监视和优化工具 Performance Monitoring and Tuning Tools
打开活动监视器 (SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
活动监视器 Activity Monitor
相关视图、函数和过程 Monitoring Performance By Using the Query Store
使用扩展事件监视系统活动 Monitor System Activity Using Extended Events
sys.dm_exec_query_statistics_xml sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles sys.dm_exec_query_profiles
跟踪标志 Trace flags
Showplan 逻辑运算符和物理运算符参考 Showplan Logical and Physical Operators Reference
实际执行计划 actual execution plan
实时查询统计信息 Live Query Statistics
Developers Choice:Query progress - anytime, anywhere(开发人员之选:随时随地查询进度)Developers Choice: Query progress - anytime, anywhere