Jednostki alokacji w SQL Server czyli IN_ROW_DATA, ROW_OVERFLOW_DATA i LOB_DATA

Adrian Chodkowski February 24, 2017 0 Comments

allocation unit, in_row_data, lob_data, row_overflow_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ć:

CREATE DATABASE ALLOCATION_UNITS_DEMO
GO

USE ALLOCATION_UNITS_DEMO
GO

CREATE TABLE DATA_TABLE
(
Col1 INT,
Col2 DATETIME,
Col3 CHAR(200),
Col4 CHAR(5000),
Col5 CHAR(2841)
)

CREATE DATABASE ALLOCATION_UNITS_DEMO

USE ALLOCATION_UNITS_DEMO

CREATE TABLE DATA_TABLE

(

Col1 INT,

Col2 DATETIME,

Col3 CHAR(200),

Col4 CHAR(5000),

Col5 CHAR(2841)

)

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!

CREATE TABLE DATA_TABLE2
(
Col1 INT,
Col2 DATETIME,
Col3 CHAR(200),
Col4 CHAR(5000),
Col5 CHAR(2842)
)

CREATE TABLE DATA_TABLE2

(

Col1 INT,

Col2 DATETIME,

Col3 CHAR(200),

Col4 CHAR(5000),

Col5 CHAR(2842)

)

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:

Msg 1701, Level 16, State 1, Line 38
Creating or altering table 'DATA_TABLE2' failed because the minimum row size would be 8061, 
including 7 bytes of internal overhead.
 This exceeds the maximum allowable table row size of 8060 bytes.

Msg 1701, Level 16, State 1, Line 38

Creating or altering table 'DATA_TABLE2' failed because the minimum row size would be 8061,

including 7 bytes of internal overhead.

This exceeds the maximum allowable table row size of 8060 bytes.

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:

INSERT INTO DATA_TABLE
VALUES (
		1,
		GETDATE(),
		REPLICATE('A',200),
		REPLICATE('A',5000),
		REPLICATE('A',2841)
	)

INSERT INTO DATA_TABLE

VALUES (

GETDATE(),

REPLICATE('A',200),

REPLICATE('A',5000),

REPLICATE('A',2841)

)

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

SELECT AU.type_desc,AU.total_pages,AU.used_pages,AU.data_pages FROM sys.allocation_units AS AU
JOIN sys.partitions AS P
ON P.partition_id=AU.container_id
where object_id=OBJECT_ID('DATA_TABLE')

SELECT AU.type_desc,AU.total_pages,AU.used_pages,AU.data_pages FROM sys.allocation_units AS AU

JOIN sys.partitions AS P

ON P.partition_id=AU.container_id

where object_id=OBJECT_ID('DATA_TABLE')

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):

CREATE TABLE DATA_TABLE3
(
Col1 INT,
Col2 DATETIME,
Col3 CHAR(200),
Col4 CHAR(5000),
Col5 VARCHAR(3000)
)

CREATE TABLE DATA_TABLE3

(

Col1 INT,

Col2 DATETIME,

Col3 CHAR(200),

Col4 CHAR(5000),

Col5 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:

SELECT AU.type_desc,AU.total_pages,AU.used_pages,AU.data_pages FROM sys.allocation_units AS AU
JOIN sys.partitions AS P
ON P.partition_id=AU.container_id
where object_id=OBJECT_ID('DATA_TABLE3')

SELECT AU.type_desc,AU.total_pages,AU.used_pages,AU.data_pages FROM sys.allocation_units AS AU

JOIN sys.partitions AS P

ON P.partition_id=AU.container_id

where object_id=OBJECT_ID('DATA_TABLE3')

Wstawmy teraz przykładowe dane:

INSERT INTO DATA_TABLE3
VALUES (
		1,
		GETDATE(),
		REPLICATE('A',200),
		REPLICATE('A',5000),
		'Testowe dane'
	)

INSERT INTO DATA_TABLE3

VALUES (

GETDATE(),

REPLICATE('A',200),

REPLICATE('A',5000),

'Testowe 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:

UPDATE DATA_TABLE3
SET Col5=REPLICATE('A',3000)

1 2	UPDATE DATA_TABLE3 SET Col5=REPLICATE('A',3000)

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.

CREATE TABLE t1 (id int, name varchar(10))
GO

SELECT AU.type_desc,AU.total_pages,AU.used_pages,AU.data_pages FROM sys.allocation_units AS AU
JOIN sys.partitions AS P
ON P.partition_id=AU.container_id
where object_id=OBJECT_ID('t1')

CREATE TABLE t1 (id int, name varchar(10))

SELECT AU.type_desc,AU.total_pages,AU.used_pages,AU.data_pages FROM sys.allocation_units AS AU

JOIN sys.partitions AS P

ON P.partition_id=AU.container_id

where object_id=OBJECT_ID('t1')

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:

CREATE TABLE DATA_TABLE4
(
Col1 INT,
Col2 DATETIME,
Col3 CHAR(200),
Col4 CHAR(5000),
Col5 VARCHAR(3000),
Col6 VARBINARY(MAX)
)
GO

INSERT INTO DATA_TABLE4
VALUES
(
1,
GETDATE(),
REPLICATE('A',200),
REPLICATE('A',5000),
REPLICATE('A',100),
CAST(REPLICATE('A',5000) AS VARBINARY(5000))
)

SELECT AU.type_desc,AU.total_pages,AU.used_pages,AU.data_pages FROM sys.allocation_units AS AU
JOIN sys.partitions AS P
ON P.partition_id=AU.container_id
where object_id=OBJECT_ID('DATA_TABLE4')

CREATE TABLE DATA_TABLE4

(

Col1 INT,

Col2 DATETIME,

Col3 CHAR(200),

Col4 CHAR(5000),

Col5 VARCHAR(3000),

Col6 VARBINARY(MAX)

)

INSERT INTO DATA_TABLE4

VALUES

(

GETDATE(),

REPLICATE('A',200),

REPLICATE('A',5000),

REPLICATE('A',100),

CAST(REPLICATE('A',5000) AS VARBINARY(5000))

)

SELECT AU.type_desc,AU.total_pages,AU.used_pages,AU.data_pages FROM sys.allocation_units AS AU

JOIN sys.partitions AS P

ON P.partition_id=AU.container_id

where object_id=OBJECT_ID('DATA_TABLE4')

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:

UPDATE DATA_TABLE4
SET Col5=REPLICATE('A',3000)

1 2	UPDATE DATA_TABLE4 SET Col5=REPLICATE('A',3000)

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.