Jednostki alokacji w SQL Server czyli IN_ROW_DATA, ROW_OVERFLOW_DATA i LOB_DATA

Czym jest tabela w SQL Server wie chyba każdy, o partycjach również wiele już powiedziano i są one znane, jednakże jeden poziom niżej znajduje się przynajmniej jedna dodatkowa warstwa, którą nazywamy jednostkami alokacji o której traktuje dzisiejszy wpis – zapraszam do lektury.

Podstawowa struktura obiektów w SQL Server została przedstawiona na poniższym diagramie:

Mamy tabelę będącą indeksem klastrowanym lub stertą (tego typu struktura w nomenklaturze SQL Server nazywana jest HoBT czyli Heap or B-Tree), która składa się z minimum jednej partycji.Warto pamiętać o tym, że jeżeli nie stworzymy jawnie schematu partycji dla tabeli to automatycznie stworzona zostanie pojedyncza partycja. Każda partycja z kolei składa się z maksymalnie trzech jednostek alokacji:

  • IN-ROW
  • ROW-OVERFLOW
  • LOB

Wspomniałem, że mogą występować maksymalnie trzy jednostki ponieważ jedyną obligatoryjną opcją jest IN_ROW, która występuje w każdej tabeli jaką stworzymy, pozostałe dwie są tworzone tylko jeżeli spełnione zostaną określone warunki o których powiemy sobie w dalszej części artykułu. Jednostka alokacji jest niczym innym jak strukturą przechowywania danych lub inaczej zestawem stron danych przechowujących określone dane. Idąc po kolei możemy powiedzieć, że jednostka alokacji IN-ROW zawiera strony na których składowane są dane wierszy, których całościowy rozmiar nie przekroczył 8060 bajtów. W strukturze tej przechowywane są kolumny o stałej długości jak np. INT, DATETIME czy też CHAR oraz te typy o zmiennej długości (czy też obiekty binarne), które mieszczą się w podanym limicie rozmiaru. Sprawdźmy to na przykładzie – na samym początku stwórzmy sobie bazę oraz przykładową tabelę na której będziemy działać:

Dlaczego wybrałem akurat takie typy danych? Ponieważ jest to zestaw którego rozmiar w sumie daje maksymalną dostępną wartość dla typów o stałej długości tj.

INT: 4bajty

DATETIME:8 bajtów

CHAR(200): 200 bajtów

CHAR(5000): 5000bajtów

CHAR(2841): 2841 bajtów

4+8+200+5000+2841= 8053

Dlaczego 8053 a nie 8060 tak jak wspomniałem powyżej? Ponieważ 7 bajtów jest zarezerwowane do wewnętrznego użycia. Co się stanie gdy przekroczymy ten limit? Sprawdźmy to!

Zapewne zauważyliście, że powyższa tabela różni się od poprzedniczki jedynie o jeden bajt w rozmiarze Col5. Gdy uruchomimy zapytanie to w rezultacie otrzymamy błąd:

Czy w rezultacie jesteśmy ograniczeni do rozmiaru 8053? Jeśli chodzi o kolumny o stałej długości to rzeczywiście jesteśmy w ten sposób ograniczeni – jednakże mamy do dyspozycji obiekty o zmiennej długości oraz LOB. Na ten moment wstawmy do tabeli dane:

i sprawdźmy jak wyglądała alokacja tych danych używając widoku systemowego sys.allocation_units:

W ramach jednostki alokacji znajduje się 9 stron z czego dwie są używane. Strona, która dodatkowo została stworzona i jest używana to tzw. IAM Page służąca do śledzenia alokacji ekstentów. Przechodząc dalej stwórzmy sobie kolejną tabelę tym razem kolumnie Col5 nadajmy typ varchar(3000):

Rozmiar naszego wiersza znacznie przekracza limit 8060 bajtów jednakże Col5 ma teraz zmienną długość po odpytaniu widoku systemowego możemy zauważyć, że stworzona została jednostka alokacji ROW_OVERFLOW_DATA:

Wstawmy teraz przykładowe dane:

Mimo, iż mamy kolumnę z typem o zmiennej długości to została zaalokowana strona w jednostce IN_ROW_DATA – jest to normalne zachowanie. Jednostki inne niż IN_ROW_DATA zostaną zaalokowane tylko wtedy gdy dane w pojedynczym rekordzie przekroczą dozwolony limit – wtedy też dane o zmiennej długości lub LOB zostaną przeniesione do stron odpowiedniej jednostki alokacji, a na stronie IN_ROW_DATA zostanie utworzony pewnego rodzaju wskaźnik. Napiszmy zapytanie, które zwiększy długość rekordu tak aby wspomniany limit został przekroczony:

Tak więc wartość rekordu o zmiennej długości została przeniesiona do ROW_OVERFLOW_DATA ponieważ całkowita wartość rekordu przekroczyła limit.Samo występowanie kolumny o zmiennej długości nie implikuje powstania ROW_OVERFLOW_DATA -zostanie ona stworzona tylko wtedy gdy nasza tabela zawiera minimum jedną kolumnę z typem o zmiennej długości i teoretycznie rozmiar całego wiersza może przekroczyć wskazany limit np.

Maksymalny rozmiar wiersza w tabeli t1 wynosi INT 4+ VARCHAR(10) +10 czyli 24 czyli nawet jeśli wykorzystamy maksymalny rozmiar kolumny varchar to nie przekroczymy limitu więc dlatego zawsze dane będą przechowywane na stronach IN_ROW_DATA.

Podobnie jak kolumny o zmiennej długości zachowują się kolumny LOB – jeśli nie przekroczą limitu długości wiersza to są przechowywane w IN_ROW_DATA w przeciwnym wypadku wartość takiego pola przechowywana jest w specjalnie dedykowanej jednostce alokacji o nazwie LOB_DATA:

Podobnie jak w poprzednim przypadku po aktualizacji pola o zmiennej długości z długości mieszącej się w IN_ROW_DATA na większą wartość zostanie przeniesiona do ROW_OVERFLOW_DATA:

Podsumowując co gdzie trafia:

IN_ROW_DATA:

  • wszystkie typy o stałej długości mogą należeć tylko i wyłącznie do tej jednostki alokacji czyli: CHAR, NCHAR, DATETIME itp
  • wszystkie typy o zmiennej długości gdzie długość wiersza (a nie typów o zmiennej długości) nie przekracza 8060 bajtów
  • wszystkie typy LOB gdzie długość wiersza (a nie typów LOB) nie przekracza 8060 bajtów

ROW_OVERFLOW_DATA:

  • wszystkie typy o zmiennej długości gdzie długość wiersza do którego one przynależą przekroczyły dozwolony limit 8060 bajtów

LOB_DATA:

  • wszystkie typy LOB gdzie długość wiersza do którego one przynależą przekroczyły dozwolony limit 8060 bajtów

Jak zapewne zauważyliście jednostki alokacji działają w dosyć łatwy do zrozumienia sposób. Warto sobie uzmysłowić, że czasem przenoszenie danych z IN_ROW_DATA do ROW_OVERFLOW_DATA może być kosztowne dlatego bardzo ważna jest architektura naszej bazy danych i odpowiedni dobór typów danych tak aby na pojedynczej stronie mieściło się jak najwięcej wierszy. Mam nadzieję, że świadomość tego jak ważne są typy danych będzie coraz większa i deklaracje typu LastName NVARCHAR(500) czy Description NVARCHAR(MAX) będą pojawiały się coraz rzadziej.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Leave a Comment

Your email address will not be published. Required fields are marked *