Poniżej znajduje się akapit z Dokumentów Microsoft :
Nowe strony przydzielone na stercie jako część operacji DML nie będą używać kompresji PAGE, dopóki sterta nie zostanie odbudowana. Odbuduj stertę, usuwając i ponownie stosując kompresję, lub tworząc i usuwając indeks klastrowany.
Nie mogę zrozumieć, dlaczego tak jest. Jeśli mam stertę z określonym ustawieniem kompresji, dlaczego nie można zastosować jej do strony należącej do tabeli?
Dzięki
Odpowiedzi:
Chociaż nie znam konkretnych mechanizmów wewnętrznych, które są odpowiedzialne za różnice, mogę powiedzieć, że sterty są zarządzane (wewnętrznie) nieco inaczej niż indeksy klastrowane (i ewentualnie również indeksy nieklastrowane):
Usuwanie wierszy ze stosu w taki sposób, że co najmniej jedna strona danych jest pusta (bez przydzielonych wierszy) niekoniecznie zwalnia to miejsce. Prawdopodobnie będziesz musiał albo utworzyć, a następnie upuścić Indeks klastrowany na stole lub wywołanie ALTER TABLE [TableName] REBUILD;(od SQL Server 2014?). Więcej informacji i opcji znajdziesz na stronie Dokumentów Microsoft dla DELETE .
Wstawienie poszczególnych wierszy (tj. Nie opartych na zestawie INSERT) do stosu nie wypełnia stron danych tak dokładnie, jak w przypadku indeksów klastrowych. Indeksy klastrowe będą pasować do wierszy, o ile będzie miejsce na wiersz (dane i obciążenie rzędu) oraz 2-bajtowe obciążenie macierzy szczelin. Strony danych w hałdach nie używają jednak liczby bajtów pozostałych na stronie, ale zamiast tego używają bardzo ogólnego wskaźnika zapełnienia strony i nie ma zbyt wielu zgłaszanych poziomów. Poziomy są następujące: 0%, 20%, 50%, 80% i 100% pełne. I przełączy się na 100%, dopóki będzie jeszcze miejsce na kolejny wiersz (a tak naprawdę, gdyby ta sama liczba wierszy została wstawiona w operacji opartej na zestawie, wypełniłaby stronę tak dużo, jak to możliwe). Oczywiście, podobnie jak w przypadkuDELETE operacji, odbudowanie sterty spowoduje spakowanie tylu wierszy, ile zmieści się na stronie danych.
Teraz rozważ następujące informacje zaczerpnięte z sekcji „Gdy nastąpi kompresja strony” strony Dokumenty Microsoft w celu implementacji kompresji strony :
... Gdy dane są dodawane do pierwszej strony danych, dane są kompresowane w wierszach. ... Gdy strona jest pełna, następny dodawany wiersz inicjuje operację kompresji strony. Cała strona jest sprawdzana; ...
Dlatego wydaje się całkowicie zgodne z tym innym zachowaniem sterty, że wymagałyby ALTER TABELI ODBUDOWYWANIA, TWORZENIA / UPADKU indeksu klastrowego lub zmiany ustawienia kompresji danych (wszystkie odbudowują stertę) przed zapisaniem stron danych optymalnie. Jeśli sterty nie są w pełni świadome „całych stron” (dopóki sterty nie zostaną odbudowane) i nie wiedzą, kiedy strona jest zdecydowanie pełna, to nie wiedzieliby, kiedy rozpocząć operację kompresji strony (w przypadku aktualizacji i pojedynczego -wkładki).
Inną techniką, która jeszcze bardziej ograniczy niektóre sterty przed automatycznym stosowaniem kompresji strony (nawet jeśli mogłyby inaczej) jest to, że zastosowanie kompresji wymagałoby odbudowania wszystkich indeksów nieklastrowanych dla tego sterty (jeśli takie istnieją). Jak podaje link do strony „Kompresja danych”:
Zmiana ustawienia kompresji stosu wymaga przebudowania wszystkich nieklastrowanych indeksów w tabeli, tak aby zawierały wskaźniki do nowych lokalizacji wierszy w stercie.
„Wskaźniki”, o których mowa, to identyfikatory wierszy (RID), które są kombinacją: FileID, PageID oraz slot / position na stronie. Te identyfikatory RID są kopiowane do indeksów nieklastrowanych. Będąc dokładną fizyczną lokalizacją, są czasami szybsze wyszukiwanie niż przejście b-drzewa za pomocą klawiszy Indeks klastrowany. Ale jedną wadą fizycznej lokalizacji jest to, że można ją zmienić i to jest właśnie tutaj problem. Jednak indeksy klastrowe nie cierpią z powodu tego problemu, ponieważ ich wartości klucza są kopiowane do indeksów nieklastrowanych jako wskaźnik z powrotem do indeksu klastrowanego. Kluczowe wartości pozostają takie same, nawet gdy zmienia się ich fizyczna lokalizacja.
Zobacz także:
sekcja „Zarządzanie stertami” na stronie Dokumentów Microsoft dla Stert (tabele bez indeksów klastrowych) :
Aby odbudować stertę w celu odzyskania zmarnowanego miejsca, utwórz indeks klastrowany na sterty, a następnie upuść indeks klastrowany.
sekcja „Uwagi dotyczące korzystania z kompresji wierszy i stron” strony Dokumenty Microsoft dotyczące kompresji danych :
Po skonfigurowaniu sterty do kompresji na poziomie strony strony otrzymują kompresję na poziomie strony tylko w następujący sposób:
- Dane są importowane zbiorczo z włączoną optymalizacją zbiorczą.
- Dane są wstawiane przy użyciu składni INSERT INTO ... WITH (TABLOCK), a tabela nie ma indeksu nieklastrowanego.
- Tabela jest odbudowywana poprzez wykonanie instrukcji ALTER TABLE ... REBUILD z opcją kompresji PAGE.
I wypowiedź cytowana w pytaniu.
Nie każdy mechanizm w SQL Server jest taki, jaki naszym zdaniem powinien być.
Paul Randal stanowczo zalecił zarządzanie tym problemem.
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/