唯一索引更新和統計行修改計數器

September 12, 2017

給定下表、唯一聚集索引和統計資訊：
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為每一行將每一列的值遞增一：
-- Increment pk in every row
UPDATE dbo.Banana 
SET pk += 1;
使用執行計劃：
它產生以下統計修改計數器：
問題
拆分、排序和折疊運算符有什麼作用？
為什麼pk統計數據顯示 2 個修改，但c1顯示c25 個？

在維護唯一索引作為影響（或可能影響）多行的更新的一部分時，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，並且已經有一行 where pk= 2。SQL Server 儲存引擎要求唯一索引在處理的每個階段都保持唯一，即使在單個語句中也是如此. 這是拆分、排序和折疊解決的問題。
分裂分裂
第一步是將每個更新語句拆分為一個刪除，然後是一個插入：
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 次更改pk，以及 5次更改c1和c2：
-- 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 通過將拆分的結果保存到急切表假離線，然後為每個唯一索引重放該集合（將有自己的按索引鍵排序 + 操作程式碼和折疊）來優化非聚集索引的這種情況。
對統計更新的影響
當查詢優化器需要統計資訊並註意到現有統計資訊已過時（或由於架構更改而無效）時，會發生自動統計資訊更新（如果啟用）。當記錄的修改數量超過門檻值時，統計數據被視為過期。
拆分/排序/折疊排列導致記錄的行修改與預期**不同。**反過來，這意味著可能會比其他情況更早或更晚地觸發統計資訊更新。
在上面的範例中，鍵列的行修改增加了 2（淨更改）而不是 4（每個受影響的表行一個）或 5（每個由 Collapse 產生的刪除/更新/插入一個）。
此外，在邏輯上未被原始查詢更改的非鍵列會累積行修改，這可能會使更新的表行數增加一倍（每次刪除一個，每個插入一個）。
記錄的更改數量取決於新舊鍵列值之間的重疊程度（以及單獨的刪除和插入可以折疊的程度）。在每次執行之間重置表，以下查詢展示了對具有不同重疊的行修改計數器的影響：
UPDATE dbo.Banana SET pk = pk + 0; -- Full overlap
UPDATE dbo.Banana SET pk = pk + 1;
UPDATE dbo.Banana SET pk = pk + 2;
UPDATE dbo.Banana SET pk = pk + 3;
UPDATE dbo.Banana SET pk = pk + 4; -- No overlap