odbudować na indeks klastrowany, dlaczego rozmiar danych się kurczy?

Kiedy przeprowadziliśmy przebudowę indeksu klastrowego w tabeli zawierającej około 15 GB danych, a rozmiar danych zmniejszył się do 5 GB, jak to możliwe? Jakie „dane” są usuwane?

Rozmiar danych mam na myśli kolumnę „data” DBCC sp_spaceused

Przed przebudową na indeks klastrowany:

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    39169656 KB 15857960 KB 22916496 KB 395200 KB

---

Po przebudowaniu na indeks klastrowany:

name                  rows        reserved    data        index_size  unused
LEDGERJOURNALTRANS    43583730    29076736 KB 5867048 KB  22880144 KB 329544 KB

TSQL do przebudowy:

USE [DAX5TEST]
GO
ALTER INDEX [I_212RECID] ON [dbo].[LEDGERJOURNALTRANS] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE, FILLFACTOR = 85 )
GO

Gdy tabela ma indeks klastrowany, indeksem są dane tabeli (w przeciwnym razie masz tabelę typu sterty). Odbudowa indeksu klastrowego (w rzeczywistości dowolnego indeksu, ale miejsce nie byłoby liczone jako „dane” dla indeksu nieklastrowanego) spowoduje, że częściowo używane strony zostaną scalone w pełniejszą formę.

Podczas wstawiania danych do indeksu (klastrowego lub innego) w kolejności indeksu strony liści są tworzone zgodnie z potrzebami i zawsze będziesz mieć tylko jedną stronę częściową: tę na końcu. Gdy wprowadzasz dane poza kolejnością indeksu, strona musi zostać podzielona, ​​aby dane zmieściły się we właściwym miejscu: w efekcie powstają dwie strony, które są w połowie zapełnione, a nowy wiersz przechodzi do jednej z nich. Z czasem może się to zdarzyć dużo, zużywając sporo dodatkowej przestrzeni, choć w pewnym stopniu przyszłe płytki wypełnią niektóre luki. Strony niebędące liśćmi również zobaczą podobny efekt, ale rzeczywiste strony danych mają znacznie większy rozmiar niż są.

Również usuwanie może powodować częściowe strony. Jeśli usuniesz wszystkie wiersze na stronie, zostanie to policzone jako „nieużywane”, ale jeśli pozostanie jeden lub więcej wierszy danych, nadal będzie liczone jako używane. Nawet jeśli na stronie jest tylko jeden wiersz zawierający 10 bajtów, ta strona liczy się jako 8192 bajtów w liczbie wykorzystanego miejsca. Ponownie przyszłe wstawki mogą wypełnić pewną lukę.

W przypadku wierszy o zmiennej długości aktualizacje mogą również mieć ten sam efekt: ponieważ wiersz staje się mniejszy, może pozostawić miejsce na swojej stronie, które później nie będzie łatwe do ponownego użycia, a jeśli wiersz na prawie pełnej stronie wydłuży się, może wymusić podział strony .

SQL Server nie spędza czasu na normalizowaniu danych przez przestawienie sposobu korzystania ze stron, dopóki nie zostanie to wyraźnie wskazane, na przykład kolejność odbudowywania indeksu, ponieważ takie ćwiczenia na śmiecie mogą być koszmarem wydajności.

Podejrzewam, że właśnie to widzisz, choć powiedziałbym, że przydzielenie wystarczającej ilości miejsca na ~ 2,7 razy więcej niż potrzeba danych jest szczególnie niekorzystnym przypadkiem. Może to sugerować, że masz coś losowego jako jeden ze znaczących kluczy w indeksie (być może kolumna UUID), co oznacza, że ​​prawdopodobnie nie zostaną dodane nowe wiersze w kolejności indeksów i / lub że ostatnio nastąpiła znaczna liczba operacji usuwania.

Przykład podziału strony

Wstawianie w kolejności indeksu z wierszami o stałej długości, z których cztery pasują do strony:

Start with one empty page: 
        [__|__|__|__]
Add the first item in index order:
        [00|__|__|__]
Add the next three
        [00|02|04|06]
Adding the next will result in a new page:
        [00|02|04|06] [08|__|__|__]
And so on...
        [00|02|04|06] [08|10|12|14] [16|18|__|__]

Teraz dodawanie wierszy poza kolejnością indeksu (dlatego użyłem liczb parzystych tylko powyżej): Dodanie 11oznaczałoby albo wydłużenie drugiej strony (niemożliwe, ponieważ mają stały rozmiar), przeniesienie wszystkiego powyżej 11 w górę (zdecydowanie za drogie na duży indeks) lub podział strony w ten sposób:

[00|02|04|06] [08|10|11|__] [12|14|__|__] [16|18|__|__]

Odtąd dodawanie 13i 17nie spowoduje podziału, ponieważ obecnie jest miejsce na odpowiednich stronach:

[00|02|04|06] [08|10|11|__] [12|13|14|__] [16|17|18|__]

ale dodanie 03 spowoduje:

[00|02|03|__] [04|06|__|__] [08|10|11|__] [12|13|14|__] [16|17|18|__]

Jak widać, po tych operacjach wstawiania mamy obecnie przydzielonych 5 stron danych, które mogą zmieścić w sumie 20 wierszy, ale mamy tam tylko 14 wierszy („marnowanie” 30% miejsca).

Przebudowa z domyślnymi opcjami (patrz poniżej „współczynnik wypełnienia”) spowoduje:

[00|02|03|04] [06|08|10|11] [12|13|14|16] [17|18|__|__]

zapisanie jednej strony w tym prostym przykładzie. Łatwo jest zobaczyć, jak skasowanie może mieć podobny efekt jak wstawianie poza kolejnością indeksowania.

Łagodzenie

Jeśli oczekujesz, że dane będą miały dość losową kolejność względem kolejności indeksów, możesz skorzystać z tej FILLFACTORopcji podczas tworzenia lub przebudowywania indeksu, aby powiedzieć SQL Serverowi, aby sztucznie pozostawiał luki do późniejszego wypełnienia - redukując podziały stron w dłuższej perspektywie, ale zajmuje początkowo więcej miejsca. Oczywiście błędne podanie tej wartości może znacznie pogorszyć sytuację, a nie poprawić sytuację, więc postępuj ostrożnie.

Podział strony, szczególnie w indeksie klastrowym, może mieć wpływ na wydajność wstawiania / aktualizacji, dlatego FILLFACTORczasami jest modyfikowany z tego powodu zamiast problemu z zajmowaniem miejsca w bazach danych, które wykazują dużą aktywność zapisu (ale w większości aplikacji, w których odczyty przeważają nad zapisami o kilka rzędów wielkości, na ogół lepiej jest pozostawić współczynnik wypełnienia na poziomie 100%, z wyjątkiem szczególnych przypadków, takich jak indeksy nad kolumnami z efektywnie losową zawartością).

Zakładam, że inne duże nazwiska DB mają podobną opcję, jeśli potrzebujesz w nich także tego poziomu kontroli.

Aktualizacja

Jeśli chodzi o ALTER INDEXstwierdzenie dodane do pytania po tym, jak zacząłem pisać powyższe: Zakładam, że opcje są takie same, jak przy pierwszym budowaniu indeksu (lub ostatniej przebudowie), ale jeśli nie, to opcja kompresji może być bardzo znacząca, jeśli zostanie dodana czas dookoła. Również w tym stwierdzeniu współczynnik wypełnienia jest ustawiony na 85%, a nie 100%, więc każda strona liścia będzie ~ 15% pusta natychmiast po odbudowie.

Po odbudowaniu indeksu dosłownie umieszcza wszystkie dane na nowych stronach. Podejrzewam, że zdarzyło się, że usunąłeś dużo danych przed przebudową, np. Usunąłeś kolumnę, zaktualizowałeś kolumnę o zmiennej szerokości, aby mieć mniej danych, zmieniłeś rozmiar kolumny o stałej szerokości lub usunął wiele wierszy. Każda z tych operacji może pozostawić dużo pustych miejsc na stronach, które nie zostaną odzyskane do czasu odbudowy. Kolumna „Dane” sp_spaceusednie mierzy rzeczywistych danych, ale liczbę stron o wielkości 8 000 użytych do przechowywania danych. Te strony są teraz bardziej pełne z powodu przebudowy, więc ta sama ilość danych mieści się na mniejszej liczbie stron.

Procedura sp_spaceusedskładowana nie sprawdza całkowitego kulmulacyjnego rozmiaru wierszy w bazie danych. Raportuje wielkość przydzielonego miejsca do przechowywania tych danych w skumulowanym rozmiarze zakresów przydzielonych dla danych.

Jeśli dostępna jest znaczna ilość wolnego miejsca, na przykład z wielu usuniętych wierszy, wówczas przebudowa indeksu klastrowego zmniejszyłaby przestrzeń na stronach i zakresach, aby była bardziej wydajna (tj. Mniejsza) ze względu na wydajność.

Tak więc żadne dane nie powinny były zostać odrzucone, ale proces odbudowy sprawił, że wolne miejsce, które zostało osadzone na stronach danych, znów było dostępne.