为什么窗口聚合函数的逻辑读取如此之高?

问题描述

我发现在使用常见子表达式假脱机的执行计划中，对于大型表，报告的逻辑读取会变得相当高.

I've found that in execution plans using common subexpression spools that the reported logical reads get quite high for large tables.

经过反复试验，我发现一个公式似乎适用于下面的测试脚本和执行计划.工作表逻辑读取数 = 1 + NumberOfRows * 2 + NumberOfGroups * 4

After some trial and error I've found a formula that seems to hold for the test script and execution plan below. Worktable logical reads = 1 + NumberOfRows * 2 + NumberOfGroups * 4

我不明白为什么这个公式成立.这比我认为有必要查看计划的要多.任何人都可以详细说明这方面的情况吗?

I don't understand the reason why this formula holds though. It is more than I would have thought was necessary looking at the plan. Can anyone give a blow by blow account of what's going on that accounts for this?

或者如果失败了，有没有办法跟踪每次逻辑读取中读取的页面，以便我自己解决?

Or failing that is there any way of tracing what page was read in each logical read so I can work it out for myself?

SET STATISTICS IO OFF; SET NOCOUNT ON;

IF Object_id('tempdb..#Orders') IS NOT NULL
  DROP TABLE #Orders;

CREATE TABLE #Orders
  (
     OrderID    INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
     CustomerID NCHAR(5) NULL,
     Freight    MONEY NULL,
  );

CREATE NONCLUSTERED INDEX ix
  ON #Orders (CustomerID)
  INCLUDE (Freight);

INSERT INTO #Orders
VALUES (N'ALFKI', 29.46),
       (N'ALFKI', 61.02),
       (N'ALFKI', 23.94),
       (N'ANATR', 39.92),
       (N'ANTON', 22.00);

SELECT PredictedWorktableLogicalReads =
        1 + 2 * Count(*) + 4 * Count(DISTINCT CustomerID)
FROM   #Orders;

SET STATISTICS IO ON;

SELECT OrderID,
       Freight,
       Avg(Freight) OVER (PARTITION BY CustomerID) AS Avg_Freight
FROM   #Orders;

输出

PredictedWorktableLogicalReads
------------------------------
23

Table 'Worktable'. Scan count 3, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Orders___________000000000002'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

附加信息:

查询调整和优化 书籍和Paul White 的这篇博文.

总而言之，计划顶部的段迭代器向它发送的行添加一个标志，指示它何时是新分区的开始.主段假脱机从段迭代器中一次获取一行并将其插入到 tempdb 中的工作表中.一旦它得到表明一个新组已经开始的标志，它就会向嵌套循环运算符的顶部输入返回一行.这会导致在工作表中的行上调用流聚合，计算平均值，然后在为新组截断工作表之前将该值与工作表中的行连接回来.段假脱机发出一个虚拟行，以便处理最后一组.

In summary the segment iterator at the top of the plan adds a flag to the rows it sends indicating when it is the start of a new partition. The primary segment spool gets a row at a time from the segment iterator and inserts it into a work table in tempdb. Once it gets the flag saying that a new group has started it returns a row to the top input of the nested loops operator. This causes the stream aggregate to be invoked over the rows in the work table, the average is computed then this value is joined back with the rows in the work table before the work table is truncated ready for the new group. The segment spool emits a dummy row in order to get the final group processed.

据我所知，工作表是一个堆(或者它会在计划中表示为索引假脱机).但是，当我尝试复制相同的过程时，它只需要 11 次逻辑读取.

As far as I understand the worktable is a heap (or it would be denoted in the plan as an index spool). However when I try and replicate the same process it only needs 11 logical reads.

CREATE TABLE #WorkTable
  (
     OrderID    INT,
     CustomerID NCHAR(5) NULL,
     Freight    MONEY NULL,
  )

DECLARE @Average MONEY

PRINT 'Insert 3 Rows'

INSERT INTO #WorkTable
VALUES      (1, N'ALFKI', 29.46) /*Scan count 0, logical reads 1*/

INSERT INTO #WorkTable
VALUES      (2, N'ALFKI', 61.02) /*Scan count 0, logical reads 1*/

INSERT INTO #WorkTable
VALUES      (3, N'ALFKI', 23.94) /*Scan count 0, logical reads 1*/
PRINT 'Calculate AVG'

SELECT @Average = Avg(Freight)
FROM   #WorkTable /*Scan count 1, logical reads 1*/
PRINT 'Return Rows - With the average column included'

/*This convoluted query is just to force a nested loops plan*/
SELECT *
FROM   (SELECT @Average AS Avg_Freight) T /*Scan count 1, logical reads 1*/
       OUTER APPLY #WorkTable
WHERE  COALESCE(Freight, OrderID) IS NOT NULL
       AND @Average IS NOT NULL

PRINT 'Clear out work table'

TRUNCATE TABLE #WorkTable

PRINT 'Insert 1 Row'

INSERT INTO #WorkTable
VALUES      (4, N'ANATR', 39.92) /*Scan count 0, logical reads 1*/
PRINT 'Calculate AVG'

SELECT @Average = Avg(Freight)
FROM   #WorkTable /*Scan count 1, logical reads 1*/
PRINT 'Return Rows - With the average column included'

SELECT *
FROM   (SELECT @Average AS Avg_Freight) T /*Scan count 1, logical reads 1*/
       OUTER APPLY #WorkTable
WHERE  COALESCE(Freight, OrderID) IS NOT NULL
       AND @Average IS NOT NULL

PRINT 'Clear out work table'

TRUNCATE TABLE #WorkTable

PRINT 'Insert 1 Row'

INSERT INTO #WorkTable
VALUES      (5, N'ANTON', 22.00) /*Scan count 0, logical reads 1*/
PRINT 'Calculate AVG'

SELECT @Average = Avg(Freight)
FROM   #WorkTable /*Scan count 1, logical reads 1*/
PRINT 'Return Rows - With the average column included'

SELECT *
FROM   (SELECT @Average AS Avg_Freight) T /*Scan count 1, logical reads 1*/
       OUTER APPLY #WorkTable
WHERE  COALESCE(Freight, OrderID) IS NOT NULL
       AND @Average IS NOT NULL

PRINT 'Clear out work table'

TRUNCATE TABLE #WorkTable

PRINT 'Calculate AVG'

SELECT @Average = Avg(Freight)
FROM   #WorkTable /*Scan count 1, logical reads 0*/
PRINT 'Return Rows - With the average column included'

SELECT *
FROM   (SELECT @Average AS Avg_Freight) T
       OUTER APPLY #WorkTable
WHERE  COALESCE(Freight, OrderID) IS NOT NULL
       AND @Average IS NOT NULL

DROP TABLE #WorkTable

推荐答案

工作表的逻辑读取计数不同:每个行读取有一个逻辑读取".这并不意味着工作台在某种程度上比真正的"线轴工作台效率低(恰恰相反)；逻辑读取只是在不同的单位.

Logical reads are counted differently for worktables: there is one 'logical read' per row read. This does not mean that worktables are somehow less efficient than a 'real' spool table (quite the reverse); the logical reads are just in different units.

我相信这种想法是计算工作表逻辑读取的散列页面不是很有用，因为这些结构是服务器内部的.报告在逻辑读取计数器中假脱机的行使数字对分析更有意义.

I believe the thinking was that counting hashed pages for worktable logical reads would not be very useful because these structures are internal to the server. Reporting rows spooled in the logical reads counter makes the number more meaningful for analysis purposes.

这种洞察力应该使您的公式有效的原​​因变得清晰.两个辅助线轴被完全读取两次 (2 * COUNT(*))，主线轴发出(组值数 + 1)行，如我的博客条目中所述，给出 (COUNT(DISTINCT CustomerID) + 1) 组件.加一是主线轴发出的额外行，表示最后一组已结束.

This insight should make the reason your formula works clear. The two secondary spools are fully read twice (2 * COUNT(*)), and the primary spool emits (number of group values + 1) rows as explained in my blog entry, giving the (COUNT(DISTINCT CustomerID) + 1) component. The plus one is for the extra row emitted by the primary spool to indicate the final group has ended.

保罗

这篇关于为什么窗口聚合函数的逻辑读取如此之高?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！