tabele sterty w SQL Server

w ostatnim poście z tej serii blogów omówiłem czym jest indeks B-Tree i krótko wyjaśniłem jego historię.

zanim zagłębimy się w Indeksy, z których korzysta SQL Server, ważne jest, aby ustawić podstawy struktur danych. Najbardziej podstawową tabelą, którą możesz utworzyć, jest sterta. Sterta to nieposortowana struktura stron, które nie są ze sobą powiązane.

kiedy należy używać sterty?

najlepszą praktyką jest unikanie sterty SQL Server, w większości przypadków. Kimberly Tripp wyjaśnia niektóre powody i debatuje tutaj, ale omówię kwestię wysokiego szczebla. Stosy, będące najbardziej podstawową strukturą pamięci masowej, nie mają funkcji poprawiających wydajność dla normalnych obciążeń (OLTP). Niektórzy twierdzą, że stosy są lepsze dla operacji wyodrębniania, przekształcania i ładowania (ETL), ponieważ jest mniej stron do utrzymania podczas operacji zapisu (UPDATE/INSERT/DELETE). W szczególności, stosy mogą być szybsze niż indeks drzewa B, jeśli występują podziały stron i wiele poziomów indeksu musi zostać zaktualizowanych. Ponadto identyfikator wiersza (RID), omówiony poniżej, ma tylko 8 bajtów. Może to działać lepiej niż indeks klastrowy z wieloma kluczowymi kolumnami, ponieważ indeksy nieklastrowe muszą przechowywać klucz klastrowy lub RID dla każdego z indeksowanych wierszy. Zazwyczaj zalecam użycie indeksów klastrowych (więcej na indeksach klastrowych, ponieważ kontynuujemy tę serię), a nie sterty, ale jeśli ich używasz, używaj ich tylko wtedy, gdy udowodniłeś, że będą bardziej wydajne niż indeks klastrowy. Po omówieniu struktury sterty, przedstawię problemy związane z wydajnością korzystania z sterty.

struktura sterty

sterta to grupowanie nieposortowanych stron, które nie są połączone. Anatomia strony jest poza zakresem dla tej serii, ponieważ wszystkie typy indeksowanych i nie indeksowanych tabel używają tej samej struktury strony, ale zachęcam do zajrzenia tutaj i tutaj, aby dowiedzieć się więcej.

sterta składa się z jednej lub więcej stron mapy alokacji indeksów (iam), które wskazują na strony danych, które tworzą stertę. Jedynym wyjątkiem jest sytuacja, gdy masz wiersz, który został zaktualizowany i nie mógł się już zmieścić na swojej stronie. W takim przypadku otrzymasz wskaźnik przekierowania do wiersza, który został przeniesiony do istniejącej strony ze spacją lub nowej strony. Możliwe jest stworzenie łańcucha zapisów przekazywania, jeśli wiersz nadal wymaga przeniesienia w ramach dalszych operacji.

heap-structure-1

odniesienie do obrazu: MSDN

sterta ma jeden wiersz w sys.partycje na partycję i jej index_id będą równe zero. W tym rekordzie first_iam_page wskazuje na pierwszą ze stron mapy alokacji indeksu (iam). Strona IAM mapuje strony dla każdej jednostki alokacji i zarządza fragmentami 4 GB tabeli. Jednostki alokacji obejmują:

  1. IN_ROW_DATA jednostka alokacji
  2. LOB_DATA jednostka alokacji
  3. ROW_OVERFLOW_DATA jednostka alokacji

strona IAM zawiera standardowy 96-bajtowy nagłówek, po którym następuje nagłówek IAM. Nagłówek IAM zaczyna się od ośmiu slotów dla mieszanych alokacji zakresu, a następnie 8000-bajtowej mapy bitowej, aby zlokalizować jednolite zakresy przydzielone do tabeli.

zakres mieszany to taki, który zawiera strony z więcej niż jednej tabeli. Jest to również określane jako wspólny zakres. Celem mieszanego zakresu jest oszczędność miejsca przy tabelach mniejszych niż 64 KB. Zakres jednolity to taki, w którym wszystkie strony w zakresie należą do tej pojedynczej tabeli.

mixed-extent

odniesienie do obrazu: zakres

aby zbadać naszą stronę IAM, najpierw musimy ją zlokalizować.

DBCC IND (’demo’, ’ dbo.demo’,1)

dbcc-ind

dzięki DBCC IND mamy 17 stron danych i 1 stronę IAM. Strony IAM nie odwołują się do siebie, dlatego IAMFID (iam file Id) i IAMPID (iam page Id) są NULL. Oznacza to, że nasza strona IAM znajduje się w pliku ID (PageFID) 1 i jest ID strony (PagePID) 297. DBCC strona pozwoli nam zobaczyć naszą stronę IAM.

DBCC TRACEON(3604);

DBCC PAGE („demo’,1,297,3);

dbcc-page-iam

na Żółto jest osiem przydziałów jednostronicowych w naszym mieszanym zakresie. W kolorze pomarańczowym jednolite zakresy są wyświetlane w zakresach.

wpływ na wydajność

wykonywanie różnych operacji języka manipulacji danymi (DML) na stercie ma ten wpływ.

  • Wstaw – nowe wiersze mogą być umieszczone na pierwszej dostępnej stronie z wystarczającą ilością miejsca. Tak więc za każdym razem, gdy wstawiany jest nowy wiersz, prawdopodobnie zostanie on dodany do ostatniej strony.
  • aktualizacja-wiersze mogą pozostać na tej samej stronie, jeśli pasują do strony po aktualizacji, jeśli nie zostaną usunięte z bieżącej strony i umieszczone na pierwszej dostępnej stronie z wystarczającą ilością miejsca, a wskaźnik przekierowania jest zapisany w oryginalnej stronie.
  • Usuń-dane nie są nadpisywane, przestrzeń jest po prostu oznaczana jako dostępna do ponownego użycia. Może to spowodować, że tabela będzie zużywać znacznie więcej miejsca na dysku niż jest to konieczne.
  • SELECT-skanowanie tabeli w całej tabeli będzie musiało być odczytane dla większości zapytań. Wyjątkiem jest sytuacja, gdy dostępny jest odpowiedni indeks nieklastrowy. W dalszej części tej serii omówimy indeksy nieklastrowe.

czyta

bez żadnego wspierającego indeksu, sterta wymaga pełnego skanowania tabeli dla dowolnego zapytania. Dzieje się tak dlatego, że struktura fizyczna nie jest posortowana i nie ma linkowania do stron obsługujących skanowanie zakresu.

Ustaw statystyki IO na

DBCC IND (’demo’,’dbo.demo’,1)

wybierz COUNT ( * )
z dbo.demo

powtarzanie naszego polecenia DBCC Ind z góry przypomina nam, że mamy 18 stron dla tej tabeli. 17 stron danych i 1 strona IAM.

– usunięto wyniki DBCC Ind, ponieważ jest już wyświetlany powyżej.
-18 wierszy, wskazuje na 18 stron.

(dotyczy 18 wierszy)
wykonanie DBCC zakończone. Jeśli DBCC wydrukował komunikaty o błędach, skontaktuj się z administratorem systemu.

RowCount
—-
5031
(1 wiersz (- y) dotknięte)

tabela „Demo”. Liczba skanowania 1, odczyt logiczny 17, odczyt fizyczny 0, odczyt logiczny 0, odczyt fizyczny 0, odczyt fizyczny 0.
(1 wiersz(y) dotyczy)

zwróć uwagę na 17 logicznych odczytów z naszego zapytania. Dzieje się tak dlatego, że strona IAM nie jest liczona w metryce logicznego odczytu i wykonaliśmy Pełne skanowanie tabeli z 18 stronami.

zapisuje i przekazuje wskaźniki

jak wspomniano powyżej, Wstawianie i usuwanie jest dość proste. Wstawianie odbywa się na pierwszej stronie z dostępnym miejscem, nawet jeśli oznacza to utworzenie nowej strony. Usuwanie odbywa się poprzez dealokację przestrzeni zajmowanej przez wiersz.

aktualizacje są tam, gdzie wszystko idzie z górki. Po zaktualizowaniu typu danych o stałej długości aktualizacja zostanie wprowadzona. Jeśli typ danych zmiennej długości zostanie zaktualizowany, a długość zwiększona, istnieje szansa, że wiersz nie będzie już pasował do tej samej strony. Gdy wiersz przestaje pasować, wskaźnik przekierowania jest umieszczany w miejscu, w którym znajdował się oryginalny wiersz, a wiersz jest przenoszony na nową stronę. Proces ten ma negatywny wpływ na wydajność odczytu. Zademonstrujmy.

pierwotnie wszystkie nasze rekordy w dbo.tabela demonstracyjna zawierała Varchary z 9 ciągami znaków.

wybierz TOP 5 *
z dbo.demo

dane tabeli demo

aby utworzyć kilka wskaźników do przodu, zaktualizujemy wszystkie nasze wiersze i zwiększymy długość kolumny varlena.

update dbo.demo
set varLen = replicate (’a’,100)

mamy teraz 95 stron w naszej stercie, ponieważ wszystkie nasze wiersze zostały przeniesione na nowe strony z pozostawionymi wskaźnikami do przodu.

DBCC IND (’demo’, ’ dbo.demo’,1)

dbcc-output-1

jak można sobie wyobrazić, ze sterty wymagające pełnego skanu tabeli, będziemy musieli przeczytać o wiele więcej stron dla tej samej ilości rekordów teraz.

Ustaw statystyki IO na

wybierz COUNT (*)
z dbo.demo
(1 wiersz (y) dotknięty)

tabela 'Demo’. Liczba skanowania 1, odczyt logiczny 4405, odczyt fizyczny 0, odczyt z wyprzedzeniem 0, odczyt logiczny lob 0, odczyt fizyczny LOB 0, odczyt z wyprzedzeniem LOB 0.

nasze odczyty drastycznie wzrosły, ponieważ trzeba było czytać oryginalne strony, a następnie podążać za wskazówkami do przodu. Wpływ na wydajność i marnowanie miejsca w pamięci i na dysku jest powodem, dla którego potrzebujemy klastrowych indeksów.

następnym razem

struktura danych sterty nie jest indeksem i jest często najgorszą strukturą danych dla tabel w SQL Server. Omówiliśmy to dzisiaj jako podstawę zrozumienia, która będzie przydatna podczas omawiania indeksów. W następnej części tej serii omówione zostaną klastrowe indeksy, czyli struktura drzewa B+dla danych tabelarycznych.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.