SQL Server中的查询跟踪列值

Sometimes, when I saw expressions like ‘Expr1002’ or ‘WindowCount1007’ or something similar in the columns Output List of a query plan, I asked myself, is there a way to project those columns into the final result to look at the values. That question first came to me out of curiosity when I was playing with window aggregate functions and a Window Spool plan operator in SQL Server 2012, I wanted to look into the Window Spool to understand, how it performs an aggregation.

有时，当我在查询计划的“输出列表”列中看到类似“ Expr1002”或“ WindowCount1007”之类的表达式时，我问自己是否有办法将这些列投影到最终结果中以查看值。 当我在SQL Server 2012中使用窗口聚合函数和Window Spool计划运算符时，出于好奇，这个问题首先出现在我的身上，我想研究一下Window Spool以了解它如何执行聚合。

Interestingly, that SQL Server 2016 CTP3.0 allows us to look deep inside into the iterator and observe the data flowing through it. Let’s turn on an “x-ray machine” and take a look.

有趣的是，SQL Server 2016 CTP3.0允许我们深入了解迭代器并观察通过它的数据。 让我们打开“ X射线机”，看看。

I will use the query that calculates running total in the DB opt.

我将在数据库opt中使用计算运行总计的查询。

use opt;
go
select sum(a) over(order by a) from t1 where a < 5;

The query plan uses Window Spool to implement running total logic.

该查询计划使用Window Spool来实现运行中的总逻辑。

Now let’s look inside. As an x-ray machine this time we will use new Extended Event (XE): query_trace_column_values. This event is located in the debug channel, so I suppose it is not documented (meaning officially supported), however, it has the following description: “query_trace_column_values – Trace output column values of each row on each query plan operator.”
From the description, we may see that this is what we need. The usage of this XE is a little bit tricky and demands several conditions to be met:

现在让我们看看里面。 这次作为X射线机，我们将使用新的扩展事件（XE）： query_trace_column_values 。 该事件位于调试通道中，因此我想它没有记录（表示正式支持），但是它具有以下描述： “ query_trace_column_values –跟踪每个查询计划运算符上每一行的输出列值。”
从描述中，我们可能会看到这就是我们所需要的。 此XE的用法有些棘手，需要满足一些条件：

• event is produced when the query is compiled or re-compiled;
  编译或重新编译查询时产生事件；
• set statistics xml should be turned on;
  设置统计信息xml应该打开；
• undocumented trace flag 2486 should be turned on on the session level.
  未记录的跟踪标志2486应该在会话级别上打开。

Next we start XE session, open Live Data window (right click on the session name “Watch Live Data”) and re-run the query with the mentioned conditions.

接下来，我们开始XE会话，打开“实时数据”窗口（右键单击会话名称“ Watch Live Data”），然后在上述条件下重新运行查询。

use opt;
go
dbcc traceon(2486);
set statistics xml on;
select sum(a) over(order by a) from t1 where a < 5 option(recompile);
set statistics xml off;
dbcc traceoff(2486);

This query produced the following result in the Live Data window:

此查询在“实时数据”窗口中产生以下结果：

There were 64 events produced for this execution. We may multiply the number of rows in each plan operator (except Compute Scalar because it might be not a “classical” iterator, but a placeholder for defining expression that is calculated somewhere else in the plan).

此执行产生了64个事件。 我们可以将每个计划运算符中的行数相乘（“计算标量”除外，因为它可能不是“经典”迭代器，而是用于定义在计划中其他地方计算的表达式的占位符）。

select
	4*1 + -- (Clustered Index Seek, 4 rows, 1 output column: a)
	4*2 + -- (Segment, 4 rows, 2 output columns: a, Segment1005)
	4*3 + -- (Segment, 4 rows, 3 output columns: a, Segment1005, Segment1006)
	8*4 + -- (Window Spool, 8 rows, 4 output columns: a, Segment1005, Segment1006, WindowCount1007)
	4*2   -- (Stream Aggregate, 4 rows, 2 output columns: Expr1003, Expr1004)

And that gives us perfect 64 events for each row, each operator, and each column. How cool is that? =)

这样一来，我们就可以为每行，每个运算符和每一列提供完美的64个事件。 多么酷啊？ =）

To analyze data in a more convenient way we may add or remove columns from the extended event grid and sort rows, to get something like this:

为了以更方便的方式分析数据，我们可以在扩展事件网格中添加或删除列，并对行进行排序，以获得类似以下内容：

In a similar way you may watch other internal columns values, for example, Bookmark column:

您可以通过类似的方式查看其他内部列的值，例如Bookmark列：

Unfortunately, it seems that this event works only for the Row Execution mode, I tried and got no luck with Batch execution.

不幸的是，似乎该事件仅适用于行执行模式，我尝试过并没有对批处理执行感到满意。

Before you start using this XE imagining your own scenarios and exploring SQL Server behavior I should mention once again, that this is not documented and maybe verbose. Also, firing an event, each and every time the column of the row is processed is not free, I didn’t measure the impact of enabling this event, but I think it should be considered. Because of all that mentioned above, use it carefully and in the test environment only.

在开始使用此XE想象自己的场景并探索SQL Server行为之前，我应该再次提到，这没有记录，可能很冗长。 另外，引发事件时，每次处理该行的列并不是免费的，我没有衡量启用此事件的影响，但我认为应该考虑一下。 由于上述所有原因，请谨慎使用它，并且仅在测试环境中使用。

I also would like to thank Microsoft dev team for keep adding those cool features and making server more transparent.

我还要感谢Microsoft开发团队不断添加这些很酷的功能并使服务器更加透明。

翻译自: https://www.sqlshack.com/query-trace-column-values/