Database administrators are used to dealing with query performance issues. As part of this duty, it is an important aspect to identify the query and troubleshoot the reason for its performance degradation. Normally, we used to enable SET STATISTICS IO and SET STATISTICS TIME before executing any query.

数据库管理员习惯于处理查询性能问题。 作为此职责的一部分，识别查询并解决其性能下降的原因是重要的方面。 通常，我们通常在执行任何查询之前先启用SET STATISTICS IO和SET STATISTICS TIME。

• SET STATISTICS IO: When it is set to ‘ON’, this displays disk activity related statistics while running the query. SET STATISTICS IO：将其设置为“ ON”时，将在运行查询时显示与磁盘活动有关的统计信息。
• SET STATISTICS TIME: It shows the time in milliseconds to parse, compile, and execute the statement. SET STATISTICS TIME：显示解析，编译和执行语句的时间（以毫秒为单位）。

When we enable these settings at the session level and execute the query, we get cumulative information about all the operators once the query is finished. We can examine the estimated or actual execution plan as well, but we do not get detailed information about the particular operator while the query is actually running. Therefore, in a certain case, it might not be useful to get information about statistics after query finishes. If we can get information at the runtime with a breakdown at each operator level, it can get useful for troubleshooting purpose.

当我们在会话级别启用这些设置并执行查询时，一旦查询完成，我们将获得有关所有运算符的累积信息。 我们也可以检查估计的或实际的执行计划，但是在查询实际运行时，我们没有获得有关特定运算符的详细信息。 因此，在某些情况下，查询完成后获取有关统计信息的信息可能没有用。 如果我们可以在运行时获取信息，并在每个操作员级别进行细分，则对于进行故障排除很有用。

In SQL Server 2014, we gained access to the new dynamic management view . This DMV is useful to monitor the real-time statistics of the query while query execution is in progress. We can join this DMV with the other views to get more information around it. We need to enable SET STATISTICS PROFILE ON to start capturing diagnostics information with the sys.dm_exec_query_profiles.

在SQL Server 2014中，我们可以访问新的动态管理视图 。 此DMV有助于在查询执行过程中监视查询的实时统计信息。 我们可以将此DMV与其他视图结合起来以获取有关它的更多信息。 我们需要启用SET STATISTICS PROFILE ON才能开始使用sys.dm_exec_query_profiles捕获诊断信息。

SQL Server Management studio (13.x) 2016 introduced live query statistics functionality in which we can actually see the execution plan of an active query. It actually shows the query progress with real-time statistics of each operator along with the data flow. This is useful for troubleshooting purpose where we can see in real time which operator is taking time to complete the query.

SQL Server Management Studio（13.x）2016引入了实时查询统计功能，在其中我们可以实际看到活动查询的执行计划。 它实际上显示查询进度以及每个操作员的实时统计信息以及数据流。 这对于故障排除很有用，我们可以实时查看哪个操作员花时间来完成查询。

We can enable live query statistics for a particular query similar to an estimated or actual execution plan. Click on the ‘Include Live Query Statistics’

我们可以为特定查询启用实时查询统计信息，类似于估计或实际执行计划。 点击“包括实时查询统计信息”

We get the live query statistics while the query is in executing state.

当查询处于执行状态时，我们将获得实时查询统计信息。

If we launch the ‘Activity Monitor’ in SQL Server Management Studio by right click on the instance name ->

如果我们通过右键单击实例名称->在SQL Server Management Studio中启动“活动监视器”

Usually, live query statistics is disabled for all the sessions. Previously, we enable live query statistics for a particular session only.

通常，所有会话都禁用实时查询统计信息。 以前，我们仅为特定会话启用实时查询统计信息。

We can enable the live query statistics by below methods till SQL Server 2016.

我们可以通过以下方法启用实时查询统计信息，直到SQL Server 2016。

• Execute SET STATISTICS XML ON or SET STATISTICS PROFILE ON before running the query in particular session
  在特定会话中运行查询之前，请执行SET STATISTICS XML ON或SET STATISTICS PROFILE ON。
• query_post_execution_showplan to enable live query statistics for all the session on the instance query_post_execution_showplan来启用实例上所有会话的实时查询统计信息
  

If we enable this extended event, we can see ‘Show Live Execution Plan’ is enabled in the activity monitor as well.

如果启用此扩展事件，则可以在活动监视器中看到也已启用“显示实时执行计划”。

As we know, nothing comes free in this world, the problem with this approach is that if we enable the event live query statistics is captured for each session and this can put an extra burden on the system in terms of CPU, Memory, Disk read and write.

众所周知，这个世界上没有免费的东西，这种方法的问题在于，如果启用了每个会话的事件实时查询统计信息，这会给系统增加CPU，内存和磁盘读取方面的负担和写。

Therefore, in SQL Server 2016, Microsoft introduced the light version of the static profile. We can enable the live lightweight query statistics using below two methods

因此，在SQL Server 2016中，Microsoft引入了静态配置文件的简化版。 我们可以使用以下两种方法启用实时轻量级查询统计信息

• DBCC TRACEON (7412, -1);GO

  

• query_thread_profile. We can find this extended event under the debug category. This channel category is not enabled by default. Therefore, put a check on ‘Debug’ from the channel query_thread_profile。 我们可以在调试类别下找到此扩展事件。 默认情况下，此通道类别未启用。 因此，请从频道中检查“调试”
  

Now, we can see this extended event as shown here.

现在，我们可以看到此扩展事件，如下所示。

Therefore, from SQL Server 2016 SP1 we can enable this lightweight profiling and it can capture live query statistics for all the sessions.

因此，从SQL Server 2016 SP1，我们可以启用此轻量级分析，并且可以捕获所有会话的实时查询统计信息。

Note: this option will be available only if the query is in executing state.

注意：仅当查询处于执行状态时，此选项才可用。

Once we have enabled the trace flag or the extended event profile in SQL Server 2016 a new system view sys.dm_exec_query_statistics_xml is available which can be used to get the show plan statistics in XML format.

一旦在SQL Server 2016中启用了跟踪标志或扩展事件配置文件，便可以使用新的系统视图sys.dm_exec_query_statistics_xml，该视图可用于获取XML格式的展示计划统计信息。

We need to pass the session id for which we want to view the query plan.

我们需要传递要查看其查询计划的会话ID。

Syntax for the sys.dm_exec_query_statistics_xml is:

sys.dm_exec_query_statistics_xml的语法为：

sys.dm_exec_query_statistics_xml(session_id)

sys.dm_exec_query_statistics_xml（session_id）

We can combine the system view with other DMV like sys.dm_exec_requests using the below query and get more information from our session, query.

我们可以使用以下查询将系统视图与其他DMV（例如sys.dm_exec_requests）组合起来，并从会话中获取更多信息。

 SELECTDMV.session_id,DMV.command,DMV.wait_type,DMV.status,DMV.dop,SXML.plan_handle,SXML.Query_planFROM sys.dm_exec_query_statistics_xml(57) AS SXML --Pass the session id here. Like I mentioned 57JOIN sys.dm_exec_requests AS DMVON SXML.session_id = DMV.session_id 

SQL Server 2019中的轻量级配置文件 (Lightweight profiling in SQL Server 2019)

SQL Server 2019 has introduced many new features and enhancements over existing features. As we have seen above, until SQL Server 2017, we have to enable the trace flag 7412 or extended event session to get used of lightweight profiling.

SQL Server 2019引入了许多新功能并对现有功能进行了增强。 如上文所述，在SQL Server 2017之前，我们必须启用跟踪标志7412或扩展事件会话才能使用轻量级概要分析。

Now in SQL Server 2019, by default, this lightweight profiling is enabled. That means we do not have to do any extra efforts to view the live query statistics for any diagnostics or troubleshooting purpose.

现在在SQL Server 2019中，默认情况下会启用此轻量级配置文件。 这意味着我们无需为了进行任何诊断或故障排除而付出额外的努力来查看实时查询统计信息。

In SQL Server 2019, however, we can control this behavior using the database scoped configuration. If we right click on the database and go to option. Under options, we can see a group ‘Database Scoped Configurations’. Currently, we do not see any database scoped configuration for the lightweight profiling.

但是，在SQL Server 2019中，我们可以使用数据库范围的配置来控制此行为。 如果我们右键单击数据库，然后转到选项。 在选项下，我们可以看到“数据库范围的配置”组。 当前，我们看不到用于轻量级配置文件的任何数据库范围的配置。

Note: As of the date of writing, we have to be in compatibility level as SQL Server vNext (150) that is new compatibility level for SQL Server 2019.

注意：从撰写本文之日起，我们必须处于与SQL Server vNext（150）兼容的级别，这是SQL Server 2019的新兼容性级别。

We can turn on or off this behavior by LIGHTWEIGHT_QUERY_PROFILING in database scoped configuration using the query.

我们可以使用查询在数据库范围的配置中通过LIGHTWEIGHT_QUERY_PROFILING来打开或关闭此行为。

For example, if we want to turn it ‘ON’, execute the below query.

例如，如果我们想将其打开，请执行以下查询。

ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = ON

We get the below syntax error message in SQL Server 2019 CTP 2.0 or CTP 2.1 version.

我们在SQL Server 2019 CTP 2.0或CTP 2.1版本中收到以下语法错误消息。

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ‘LIGHTWEIGHT_QUERY_PROFILING’.

Msg 102，第15级，状态1，第1行

“ LIGHTWEIGHT_QUERY_PROFILING”附近的语法不正确。

Note: As per SQL Server 2019 CTP 2.1 , ‘Currently, the lightweight query-profiling infrastructure (LWP) cannot be controlled at the individual database level and remains enabled for all databases by default. For more information on LWP’.

注意：根据SQL Server 2019 CTP 2.1 ，``当前，轻量级查询分析基础结构（LWP）无法在单个数据库级别进行控制，并且默认情况下对所有数据库保持启用状态。 有关LWP的更多信息。

This issue will be fixed in the future release of SQL Server 2019.

此问题将在SQL Server 2019的将来版本中修复。

结论 (Conclusion)

We have explored in this article that SQL Server 2019 support lightweight profiling without any extra configuration steps such as enable trace flag or extended event session. This looks like a promising feature and we can control this property using the database scooped configuration which will be available in a future release of SQL Server 2019.

我们在本文中探讨了SQL Server 2019支持轻量级配置文件，而无需任何其他配置步骤，例如启用跟踪标志或扩展事件会话。 这看起来像是一个很有前途的功能，我们可以使用数据库挖出的配置来控制此属性，该配置将在将来SQL Server 2019版本中提供。

翻译自: