Sql-Server

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

  • 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;

使用執行計劃:

拆分排序折疊執行計劃

它產生以下統計修改計數器:

更新後修改計數器

問題

  1. 拆分、排序和折疊運算符有什麼作用?
  2. 為什麼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 屬性來反映這種操作模式:

聚集索引更新操作屬性


行修改計數器

請注意,上面的三個更新不會修改正在維護的唯一索引的鍵。實際上,我們已經將索引中鍵列的更新轉換**為非鍵列(c1c2)的更新,加上刪除和插入。刪除和插入都不會導致錯誤的唯一鍵違規。

插入或刪除會影響行中的每一列,因此與每一列關聯的統計資訊將增加其修改計數器。對於更新,只有將任何更新列作為前導列的統計資訊才會增加其修改計數器(即使值未更改)。

因此,統計行修改計數器顯示 2 次更改pk,以及 5次更改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 通過將拆分的結果保存到急切表假離線,然後為每個唯一索引重放該集合(將有自己的按索引鍵排序 + 操作程式碼和折疊)來優化非聚集索引的這種情況。

對統計更新的影響

當查詢優化器需要統計資訊並註意到現有統計資訊已過時(或由於架構更改而無效)時,會發生自動統計資訊更新(如果啟用)。當記錄的修改數量超過門檻值時,統計數據被視為過期。

拆分/排序/折疊排列導致記錄的行修改與預期**不同。**反過來,這意味著可能會比其他情況更早或更晚地觸發統計資訊更新。

在上面的範例中,鍵列的行修改增加了 2(淨更改)而不是 4(每個受影響的表行一個)或 5(每個由 Collapse 產生的刪除/更新/插入一個)。

此外,在邏輯上未被原始查詢更改的非鍵列會累積行修改,這可能會使更新的表行數增加一倍(每次刪除一個,每個插入一個)。


記錄的更改數量取決於新舊鍵列值之間的重疊程度(以及單獨的刪除和插入可以折疊的程度)。在每次執行之間重置表,以下查詢展示了對具有不同重疊的行修改計數器的影響:

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

引用自:https://dba.stackexchange.com/questions/185551