主页 / dba / 问题 / 185551
Accepted
Paul White
Asked: 2017-09-11 12:12:10 +0800 CST

唯一索引更新和统计行修改计数器

  • 772

给定下表、唯一聚集索引和统计信息：

CREATE TABLE dbo.Banana
(
    pk integer NOT NULL, 
    c1 char(1) NOT NULL, 
    c2 char(1) NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX pk ON dbo.Banana (pk);

CREATE STATISTICS c1 ON dbo.Banana (c1);
CREATE STATISTICS c2 ON dbo.Banana (c2);

INSERT dbo.Banana 
    (pk, c1, c2) 
VALUES 
    (1, 'A', 'W'), 
    (2, 'B', 'X'), 
    (3, 'C', 'Y'), 
    (4, 'D', 'Z');

-- Populate statistics
UPDATE STATISTICS dbo.Banana;

示例数据

统计行修改计数器在任何更新之前显然显示为零：

-- Show statistics modification counters
SELECT
    stats_name = S.[name], 
    DDSP.stats_id,
    DDSP.[rows],
    DDSP.modification_counter
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP
WHERE
    S.[object_id] = OBJECT_ID(N'dbo.Banana', N'U');

零修改计数器

pk将每行的每列值递增1：

-- Increment pk in every row
UPDATE dbo.Banana 
SET pk += 1;

使用执行计划：

拆分排序 折叠执行计划

它产生以下统计修改计数器：

更新后修改计数器

问题

  1. 拆分、排序和折叠运算符有什么作用？
  2. 为什么pk统计数据显示 2 个修改，但c1显示c25 个？
sql-server update

1 个回答

  1. Best Answer

    在维护唯一索引作为影响（或可能影响）多行的更新的一部分时，SQL Server 始终使用拆分、排序和折叠运算符组合。

    通过问题中的示例，我们可以将更新编写为针对存在的四行中的每一行的单独的单行更新：

    -- Per row updates
UPDATE dbo.Banana SET pk = 2 WHERE pk = 1;
UPDATE dbo.Banana SET pk = 3 WHERE pk = 2;
UPDATE dbo.Banana SET pk = 4 WHERE pk = 3;
UPDATE dbo.Banana SET pk = 5 WHERE pk = 4;

    问题是第一个语句会失败，因为它pk从 1 变为 2，并且已经有一行pk= 2。SQL Server 存储引擎要求唯一索引在处理的每个阶段保持唯一，即使在单个语句中也是如此. 这就是 Split、Sort 和 Collapse 解决的问题。

    分裂分裂

    第一步是将每个更新语句拆分为一个删除，然后是一个插入：

    DELETE dbo.Banana WHERE pk = 1;
INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W');

DELETE dbo.Banana WHERE pk = 2;
INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X');

DELETE dbo.Banana WHERE pk = 3;
INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y');

DELETE dbo.Banana WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

    Split 运算符向流中添加一个操作代码列（此处标记为 Act1007）：

    拆分属性

    操作代码为 1 表示更新，3 表示删除，4 表示插入。

    种类种类

    上面的拆分语句仍然会产生错误的瞬态唯一键违规，因此下一步是按正在更新的唯一索引的键（pk在本例中）对语句进行排序，然后按操作代码进行排序。对于此示例，这仅意味着同一键上的删除 (3) 在插入 (4) 之前排序。结果顺序是：

    -- Sort (pk, action)
DELETE dbo.Banana WHERE pk = 1;
DELETE dbo.Banana WHERE pk = 2;
INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W');
DELETE dbo.Banana WHERE pk = 3;
INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X');
DELETE dbo.Banana WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y');
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

    排序属性

    坍塌坍塌

    前面的阶段足以保证在所有情况下都避免错误的唯一性违规。作为一种优化，Collapse 将同一键值上的相邻删除和插入合并到一个更新中：

    -- Collapse (pk)
DELETE dbo.Banana WHERE pk = 1;
UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2;
UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3;
UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

    pk值 2、3 和 4的删除/插入对已合并为一个更新，在pk= 1 上留下一个删除，在 = 5 上留下一个插入pk。

    Collapse 运算符按键列对行进行分组，并更新操作代码以反映折叠结果：

    折叠属性

    聚簇索引更新聚簇索引更新

    此运算符被标记为更新，但它能够插入、更新和删除。每行的聚簇索引更新采取哪种操作由该行中的操作代码的值决定。运算符有一个 Action 属性来反映这种操作模式：

    聚簇索引更新操作属性

    行修改计数器

    请注意，上面的三个更新不会修改正在维护的唯一索引的键。实际上，我们已经将索引中键列的更新转换为非键列（c1和c2）的更新，加上删除和插入。删除和插入都不会导致错误的唯一键违规。

    插入或删除会影响行中的每一列，因此与每一列关联的统计信息都会增加其修改计数器。对于更新，只有将任何更新列作为前导列的统计信息才会增加其修改计数器（即使值未更改）。

    因此，统计行修改计数器显示 2 次更改，和pk5次更改：c1c2

    -- Collapse (pk)
DELETE dbo.Banana WHERE pk = 1;                         -- All columns modified
UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2;  -- c1 and c2 modified
UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3;  -- c1 and c2 modified
UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4;  -- c1 and c2 modified
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');    -- All columns modified

    注意：只有应用于基础对象（堆或聚集索引）的更改才会影响统计行修改计数器。非聚集索引是二级结构，反映了已经对基础对象所做的更改。它们根本不影响统计行修改计数器。

    如果一个对象有多个唯一索引，则使用单独的拆分、排序、折叠组合来组织对每个索引的更新。SQL Server 通过将 Split 的结果保存到 Eager Table Spool，然后为每个唯一索引重放该集合（将有自己的按索引键排序 + 操作代码和 Collapse）来优化非聚集索引的这种情况。

    对统计更新的影响

    当查询优化器需要统计信息并注意到现有统计信息已过时（或由于架构更改而无效）时，会发生自动统计信息更新（如果启用）。当记录的修改数量超过阈值时，统计信息被认为已过时。

    拆分/排序/折叠安排导致记录的行修改与预期不同。反过来，这意味着可能会比其他情况更早或更晚触发统计更新。

    在上面的示例中，键列的行修改增加 2（净更改）而不是 4（每个受影响的表行一个）或 5（崩溃产生的每个删除/更新/插入一个）。

    此外，逻辑上未被原始查询更改的非键列会累积行修改，这可能是更新的表行数的两倍（每次删除一个，每次插入一个）。

    记录的更改数量取决于新旧键列值之间的重叠程度（因此可以折叠单独的删除和插入的程度）。在每次执行之间重置表，以下查询演示了对具有不同重叠的行修改计数器的影响：

    UPDATE dbo.Banana SET pk = pk + 0; -- Full overlap

    pk = pk + 0

    UPDATE dbo.Banana SET pk = pk + 1;

    pk = pk + 1

    UPDATE dbo.Banana SET pk = pk + 2;

    pk = pk + 2

    UPDATE dbo.Banana SET pk = pk + 3;

    pk = pk + 3

    UPDATE dbo.Banana SET pk = pk + 4; -- No overlap

    pk = pk + 4

    • 16

相关问题