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ć:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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) ) |
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!
1 2 3 4 5 6 7 8 |
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:
1 2 3 4 |
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:
1 2 3 4 5 6 7 8 |
INSERT INTO DATA_TABLE VALUES ( 1, 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:
1 2 3 4 |
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):
1 2 3 4 5 6 7 8 |
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:
1 2 3 4 |
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:
1 2 3 4 5 6 7 8 |
INSERT INTO DATA_TABLE3 VALUES ( 1, 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:
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.
1 2 3 4 5 6 7 |
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') |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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') |
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:
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.
- Azure Data Factory – Lookup i Foreach - January 17, 2021
- Nagrania naszych sesji z SQLDay 2019! - December 29, 2020
- SQLDay 2020! - November 28, 2020
Ostatnie komentarze