Dlaczego moje nieklastrowane indeksy zajmują więcej miejsca podczas usuwania wierszy?

Mam dużą tabelę z 7,5 miliardami wierszy i 5 indeksami. Kiedy usuwam około 10 milionów wierszy, zauważam, że indeksy nieklastrowane wydają się zwiększać liczbę stron, na których są przechowywane.

Napisałem zapytanie przeciwko, dm_db_partition_statsaby zgłosić różnicę (po - przed) na stronach:

Indeks 1 jest indeksem klastrowym, Indeks 2 jest kluczem podstawowym. Pozostałe są nieklastrowane i nie unikalne.

Dlaczego strony rosną w indeksach nieklastrowanych?
Spodziewałem się, że liczby w najgorszym razie pozostaną takie same.
Widzę, że liczniki wydajności zgłaszają wzrost podziału strony podczas usuwania.

Czy podczas usuwania rekord widmo musi przejść na inną stronę? Czy ma to związek z „unikatami”?

Jesteśmy w trakcie wdrażania RCSI, ale teraz RCSI jest wyłączony.

Jest to główny węzeł w grupie dostępności. Wiem, że migawka jest używana w jakiś sposób na urządzeniach pomocniczych. Byłbym zaskoczony, gdyby to było istotne. Planuję zagłębić się w to (szukam danych wyjściowych strony dbcc), aby dowiedzieć się więcej. Mam nadzieję, że ktoś widział coś podobnego.

Jeden możliwy scenariusz, który mnie bardzo bawi:

• Wiersze zostały pierwotnie zapisane, gdy baza danych nie miała włączonej opcji Read Committed Snapshot (RCSI), Snapshot Isolation (SI) lub grup dostępności (AG)
• Włączono RCSI lub SI albo bazę danych dodano do grupy dostępności
• Podczas usuwania dodano 14-bajtowy znacznik czasu do usuniętych wierszy w celu obsługi odczytów RCSI / SI / AG

Ponieważ ten serwer jest podstawowym w AG, ma to wpływ tak samo jak pomocnicze. Informacje o wersji są dodawane na podstawowym - strony danych są dokładnie takie same na pierwotnych i wtórnych. Urządzenia pomocnicze wykorzystują magazyn wersji do dokonywania odczytów, podczas gdy wiersze są aktualizowane przez AG, ale urządzenia pomocnicze nie zapisują własnych wersji znacznika czasu na stronie. Po prostu dziedziczą wersje z pracy podstawowej.

Aby zademonstrować wzrost, wziąłem eksport bazy danych przepełnienia stosu (który nie ma włączonej obsługi RCSI) i utworzyłem kilka indeksów w tabeli Posts. Sprawdziłem rozmiary indeksów za pomocą sp_BlitzIndex @Mode = 2 (skopiuj / wklej do arkusza kalkulacyjnego i trochę wyczyściłem, aby zmaksymalizować gęstość informacji):

Następnie usunąłem około połowy wierszy:

BEGIN TRAN;
DELETE dbo.Posts WHERE Id % 2 = 0;
GO

Zabawne jest, że podczas usuwania plików danych rosło także, aby uwzględnić znaczniki czasu! Raport użycia dysku SSMS pokazuje wydarzenia wzrostowe - oto tylko przykład ilustrujący:

(Uwielbiam wersję demonstracyjną, w której usuwanie powoduje, że baza danych rośnie.) Gdy usuwanie było uruchomione, ponownie uruchomiłem sp_BlitzIndex. Pamiętaj, że indeks klastrowy ma mniej wierszy, ale jego rozmiar już się zwiększył o około 1,5 GB. Indeksy nieklastrowane na AcceptedAnswerId dramatycznie wzrosły - są to indeksy o niewielkiej wartości, która w większości jest zerowa, więc ich rozmiary indeksów prawie się podwoiły!

Nie muszę czekać na zakończenie usuwania, aby to udowodnić, więc zatrzymam demo. Chodzi o to, że: gdy wykonujesz duże usunięcia w tabeli, która została zaimplementowana przed włączeniem RCSI, SI lub AG, indeksy (w tym klastrowane) mogą faktycznie rosnąć, aby uwzględnić dodanie znacznika czasu magazynu wersji.