Unikalne aktualizacje indeksów i liczniki modyfikacji wierszy statystyk

Biorąc pod uwagę poniższą tabelę, unikalny indeks klastrowy i statystyki:

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;

Liczniki modyfikacji wierszy statystyk oczywiście pokazują zero przed aktualizacjami:

-- 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');

Zwiększanie pkwartości każdej kolumny o jedną dla każdego wiersza:

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

Korzysta z planu wykonania:

Tworzy następujące liczniki modyfikacji statystyk:

pytania

Co robią operatorzy podziału, sortowania i zwijania?
Dlaczego pkstatystyki pokazują 2 modyfikacje, c1a c2pokazują 5?

— Paul White 9
źródło

SQL Server zawsze używa kombinacji operatorów Podziel, Sortuj i Zwiń podczas utrzymywania unikalnego indeksu w ramach aktualizacji, która wpływa (lub może wpływać) na więcej niż jeden wiersz.

Pracując na przykładzie w pytaniu, możemy zapisać aktualizację jako osobną aktualizację dla jednego wiersza dla każdego z czterech obecnych wierszy:

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

Problem polega na tym, że pierwsza instrukcja zawiedzie, ponieważ zmienia się pkz 1 na 2, a już istnieje wiersz, w którym pk= 2. Aparat pamięci masowej SQL Server wymaga, aby unikalne indeksy pozostały unikalne na każdym etapie przetwarzania, nawet w ramach pojedynczej instrukcji . Jest to problem rozwiązany przez Podziel, Sortuj i Zwiń.

Rozdzielać

Pierwszym krokiem jest podzielenie każdej instrukcji aktualizacji na usunięcie, a następnie wstawienie:

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');

Operator podziału dodaje kolumnę kodu akcji do strumienia (tutaj oznaczoną Act1007):

Kod akcji to 1 dla aktualizacji, 3 dla usunięcia i 4 dla wstawki.

Sortować

Powyższe instrukcje podzielone nadal powodowałyby fałszywe przejściowe naruszenie unikatowego klucza, więc następnym krokiem jest posortowanie instrukcji według kluczy aktualizowanego indeksu unikalnego ( pkw tym przypadku), a następnie według kodu akcji. W tym przykładzie oznacza to po prostu, że usunięcia (3) na tym samym kluczu są porządkowane przed wstawkami (4). Wynikowe zamówienie to:

-- 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');

Zawalić się

Poprzedni etap jest wystarczający, aby zagwarantować unikanie fałszywych naruszeń wyjątkowości we wszystkich przypadkach. W ramach optymalizacji Zwiń łączy sąsiednie usunięcia i wstawia tę samą wartość klucza do aktualizacji:

-- 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');

Pary usuwania / wstawiania dla pkwartości 2, 3 i 4 zostały połączone w aktualizację, pozostawiając pojedyncze usunięcie na pk= 1 i wstawienie na pk= 5.

Operator zwinięcia grupuje wiersze według kolumn kluczowych i aktualizuje kod akcji, aby odzwierciedlić wynik zwinięcia:

Aktualizacja indeksu klastrowego

Ten operator jest oznaczony jako Aktualizacja, ale może wstawiać, aktualizować i usuwać. To, która akcja jest wykonywana przez aktualizację indeksu klastrowanego na wiersz, zależy od wartości kodu akcji w tym wierszu. Operator ma właściwość Action odzwierciedlającą ten tryb działania:

Liczniki modyfikacji wierszy

Należy zauważyć, że trzy powyższe aktualizacje nie modyfikują kluczy utrzymywanego unikalnego indeksu. W efekcie mamy przekształcona aktualizacje do głównych kolumn w indeksie pod aktualizacjach zakaz kluczowych kolumn ( c1i c2), a także usunąć i wkładki. Ani usunięcie, ani wstawka nie może spowodować fałszywego naruszenia klucza unikalnego.

Wstawianie lub usuwanie wpływa na każdą kolumnę w wierszu, więc statystyki powiązane z każdą kolumną będą miały zwiększone liczniki modyfikacji. W przypadku aktualizacji tylko statystyki z dowolną zaktualizowaną kolumną jako kolumną wiodącą mają zwiększane liczniki modyfikacji (nawet jeśli wartość nie ulegnie zmianie).

Liczniki modyfikacji wierszy statystyk pokazują zatem 2 zmiany pk, oraz 5 dla c1i 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

Uwaga: Tylko zmiany zastosowane do obiektu podstawowego (sterty lub indeksu klastrowego) wpływają na liczniki modyfikacji wierszy statystyk. Indeksy nieklastrowane są strukturami drugorzędnymi, odzwierciedlającymi zmiany dokonane już w obiekcie podstawowym. Nie wpływają one w ogóle na liczniki modyfikacji wierszy statystyk.

Jeśli obiekt ma wiele unikalnych indeksów, do porządkowania aktualizacji dla każdego z nich używana jest osobna kombinacja Podziel, Sortuj, Zwiń. SQL Server optymalizuje ten przypadek dla indeksów nieklastrowanych, zapisując wynik podziału w chętnej buforze tabel, a następnie odtwarzając ten zestaw dla każdego unikalnego indeksu (który będzie miał swój własny Sortuj według kluczy indeksu + kodu akcji i Zwiń).

Wpływ na aktualizacje statystyk

Automatyczne aktualizacje statystyk (jeśli włączone) występują, gdy optymalizator zapytań potrzebuje informacji statystycznych i zauważa, że istniejące statystyki są nieaktualne (lub nieprawidłowe z powodu zmiany schematu). Statystyki uważa się za nieaktualne, gdy liczba zarejestrowanych modyfikacji przekracza próg.

Układ Podziel / Sortuj / Zwiń powoduje zapisanie różnych modyfikacji wierszy, niż można by się spodziewać. To z kolei oznacza, że aktualizacja statystyk może zostać uruchomiona wcześniej lub później niż w innym przypadku.

W powyższym przykładzie modyfikacje wierszy dla kolumny kluczowej zwiększają się o 2 (zmiana netto) zamiast o 4 (po jednym dla każdego wiersza w tabeli) lub o 5 (po jednym dla każdego usunięcia / aktualizacji / wstawki utworzonego przez Zwiń).

Ponadto w kolumnach niekluczowych, które logicznie nie zostały zmienione przez pierwotne zapytanie, gromadzone są modyfikacje wierszy, które mogą liczyć nawet dwukrotność zaktualizowanych wierszy tabeli (jeden dla każdego usunięcia i jeden dla każdej wstawki).

Liczba zarejestrowanych zmian zależy od stopnia nakładania się starych i nowych wartości kluczowych kolumn (a więc stopnia, w jakim oddzielne usunięcia i wstawki można zwinąć). Po zresetowaniu tabeli między każdym wykonaniem następujące zapytania pokazują wpływ na liczniki modyfikacji wierszy z różnymi nakładkami:

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

— Paul White 9
źródło