主页 / dba / 问题 / 345758
Accepted
SE1986
Asked: 2025-03-17 22:47:29 +0800 CST

查询更改后，聚集索引更新运算符执行过多的逻辑读取

  • 772

如果我在 Stack Overflow2010 数据库中有以下查询

UPDATE  dbo.Posts
SET     Title =
        CASE 
            WHEN CreationDate <= '2008-01-01T00:00:00'
            THEN 'A'
            ELSE 'B'
        END
FROM    dbo.Posts

简要的STATISTICS IO 输出如下

Table 'Posts'. Scan count 1, logical reads 445699, physical reads 375822, page server reads 0, read-ahead reads 445521, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1878895 rows affected)

执行计划如下

如果我创建一个表来存储我想要在比较中使用的值：

CREATE TABLE dbo.Canary
(
    TheDate DATETIME
)

INSERT INTO dbo.Canary VALUES ('2008-01-01T00:00:00')

然后如果我将查询更改如下：

UPDATE  dbo.Posts
SET     Title =
        CASE 
            WHEN CreationDate <= Canary.TheDate
            THEN 'A'
            ELSE 'B'
        END
FROM    dbo.Posts
        CROSS JOIN dbo.Canary

STATISTICS IO 输出是

Table 'Canary'. Scan count 1, logical reads 1, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 16787757, physical reads 3127, page server reads 0, read-ahead reads 784795, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 6291, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1878895 rows affected)

执行计划如下

我们可以看到逻辑读取的数量大幅增加，从 445k 增加到 16m。我花了一些时间才在执行计划中找到这个问题的根源，但我使用实际 I/O 统计信息/实际逻辑读取属性对其进行了跟踪，并可以看到额外的读取是在聚集索引更新运算符上，我还可以看到此运算符现在在所有执行中都有一个实际行数值，而第一个查询的计划没有。

这里发生了什么？聚集索引更新中发生了什么，导致读取次数增加？

如果要使用这种“配置表中的值”模式，我知道查询应该使用一个变量，但是，这是一个来自供应商应用程序的查询，所以我想反馈查询以这种方式编写后发生的情况。

sql-server

1 个回答

  1. Best Answer

    当您添加时，CROSS JOIN dbo.Canary您不再获得 行集共享的好处，并且UPDATE操作员需要自己找到要更新的每一行，而不仅仅是使用计划读取部分中扫描的位置。

    在此处输入图片描述

    对查询的这一更改可能意味着读取端有许多行需要折叠为一行，UPDATE并且将具有聚合的流聚合ANY添加到计划中。

    我推测，在这种情况下，添加中间的流聚合会阻止行集共享。

    如果删除聚合（例如，使用下面的方法或添加唯一索引Canary并执行唯一查找），则行集共享会再次发生。

    UPDATE  dbo.Posts
SET     Title =
        CASE 
            WHEN CreationDate <= Canary.TheDate
            THEN 'A'
            ELSE 'B'
        END
FROM    dbo.Posts
        CROSS JOIN (SELECT TOP 1 * FROM dbo.Canary) Canary

    要在非生产环境中查看执行计划是否使用行集共享，您可以使用未记录的跟踪标志（ ）并查看执行计划中运算符DBCC TRACEON (8666)的属性。UPDATE

    在此处输入图片描述

    （编辑：我没有安装 StackOverflow 数据库，因此只需Posts为上述内容创建了一个空表。我错过了您发布的执行计划，并且在您的情况下看到从多到一的折叠是由中间不同的排序运算符完成的。我想这种TOP 1方法也应该摆脱这一点。您的问题计划的另一个方面是它是并行的。如果它仍然保持并行，您可能需要添加一个MAXDOP 1提示，因为这也意味着行不再直接从扫描流式传输到更新，并阻止行集共享。）

    • 13

相关问题