Strony danych i ekstenty w SQL Server

Wszystkie dane w SQL Server fizycznie składowane są w stronach danych. Dziś właśnie zajmiemy się tą tematyką – postaram się ją przybliżyć na takim poziomie abyśmy mogli w późniejszym okresie dotykać tematów już nieco bardziej skomplikowanych.

Tak więc każda dana w SQL Server fizycznie składowana jest na stronach danych. Każda z nich jest ujednolicona pod kątem wielkości i posiada rozmiar 8 KB – SQL Server nie pozwala na modyfikację tej wielkości. 8 stron tworzy tzw. ekstent. Z prostego wyliczenia możemy się dowiedzieć, że skoro 8 stron o wielkości 8KB tworzy ekstent to ma on wielkość 64KB.

W momencie gdy tworzony jest plik bazy danych odpowiednia ilość ekstentów, a co za tym idzie stron, jest alokowana. W momencie gdy plik się rozszerza poprzez jego modyfikację bezpośrednią albo poprzez opcję automatycznego wzrostu (ang. AUTOGROW) – nowe strony są dodawane na końcu istniejącego pliku. Istnieje oczywiście operacja odwrotna czyli usunięcie stron danych z pliku – dzieje się to podczas operacji SHRINK, gdzie strony z  pliku są usuwane rozpoczynając od końca pliku.

W SQL Server wyróżniamy dwa zasadnicze typy ekstentów:

• uniform extent
• mixed extent

Pierwszy z nich czyli ekstent typu uniform jest to taki ekstent gdzie wszystkie strony wchodzące w jego skład zawierają dane jednego obiektu. Odwrotna sytuacja występuje w przypadku ekstentu typu mixed, gdzie strony wchodzącego w jego skład zawierają dane więcej niż 1 obiektu. Koncepcja ta została przedstawiona na poniższej grafice.

 

W momencie gdy tworzymy tabelę czy też index to SQL Server alokuje strony w mixed extent  – tak więc obiekty, które nie przekraczają 8 stron danych są alokowane właśnie w tego typu ekstentach. W momencie gdy dany obiekt przekracza 8 stron danych to alokowane są dla niego uniform ekstenty. SQL Server musi na bieżąco monitorować stan alokacji ekstentów i dlatego też posiada dwie specjalne strony spełniające to zadanie.

Pierwszą z nich jest strona o nazwie Global Allocation Map (GAM Page). Strona ta zawiera informacje o ekstentach, które zostały alokowane. Posiada ona bit kontrolny dla każdego ekstentu, który obejmuje swoim zasięgiem. Jeżeli ten bit jest równy 0 to oznacza to, że dany ekstent jest w użyciu, jeżeli bit jest równy 1 oznacza to, że dany ekstent jest wolny. Strona GAM istnieje w każdym fizycznym pliku osobno i obejmuje 64 tysiące bitów tj. swoim zasięgiem pokrywa 64 tysiące ekstentów czyli około 4GB danych. Można więc wywnioskować, że plik bazy danych posiada jedną stronę GAM na każde 4GB danych.

Drugą stroną kontrolną jest tzw. Shared Global Allocation Map (SGAM Page). Strona ta zawiera informacje o ekstentach, które są używane jako mixed  i mają minimum jedną stronę pustą. Podobnie jak strona GAM również SGAM posiada bit kontrolny, który jeżeli posiada wartość 1 oznacza to, że jest typu mixed i zawiera wolne strony, jeżeli bit posiada wartość 0 oznacza to, że ekstent nie jest typu mixed lub ma wszystkie strony zajęte.

Podsumujmy te informacje w tabeli:

Ekstent	bit na stronie GAM	bit na stronie SGAM
Pusty, nieużywany	1	0
Uniform lub pełny Mixed	0	0
Mixed z wolnymi stronami	0	1

Napiszmy pokrótce plan działania SQL Server w momencie gdy chce on alokować ekstent – składa się on tylko z dwóch kroków:

1. Pierwszym krokiem jest odszukanie na stronie GAM ekstentu z bitem ustawionym na 1 i ustawienie go na 0
2. Jeżeli alokowany ekstent ma być typu mixed to ustawiany jest również odpowiadający bit na stronie SGAM na wartość 1 (w przypadku gdy alokowany ekstent jest typu uniform to strona SGAM nie jest modyfikowana)

W przypadku gdy SQL server chce znaleźć mixed extent z wolnymi stronami, używa on strony SGAM aby zlokoalizować ekstent z bitem ustawionym na 1. W przypadku gdy nie może takiego znaleźć to oznacza, że plik danych jest pełny i trzeba go rozszerzyć.

Pierwsza strona GAM to trzecia strona w ramach danej bazy danych (czyli strona o indeksie 2 ze względu, iż numeracja jest prowadzona od 0). Pierwsza strona SGAM to czwarta strona w ramach danej bazy danych (czyli strona numer 3). Oczywiście w każdej chwili znając numery stron możemy je podejrzeć. Aby to zrobić musimy najpierw w ramach sesji włączyć flagę 3604 aby rezultat zapytania był dla nas widoczny.

DBCC TRACEON(3604)
GO

następnie użyjemy polecenia DBCC PAGE pozwalająca podejrzeć fizyczną stronę. Jako pierwszy parametr podajemy nazwę bazy danych, następnie identyfikator pliku oraz tryb wyświetlania (3 oznacza szczegółowe wyświetlenie danych w wierszach oraz nagłówek strony)

DBCC PAGE('DataPages',1,2,3)

W rezultacie otrzymaliśmy całą stronę – interesującą dla nas jest sekcja jak na poniższym zrzucie:

Informacje na zrzucie mówią nam o tym, że ekstent który zaczyna się od strony 1:0, aż do ekstentu, która się zaczyna na stronie 1:208 są zaalokowane. Następnie ekstent 1:216 jest niezaalokowany itd. Podejrzyjmy zatem stronę zaalokowaną i niezaalokowaną.

DBCC PAGE('DataPages',1,224,3)

Jak widać na powyższym zrzucie w sekcji Allocation status wymieniona jest strona GAM w której dana strona jest zaalokowana (strona 1:2) oraz bit równy 1 tj. Allocated.

Sprawdźmy jak alokowane są strony dla danej tabeli.Na samym początku stwórzmy bazę o nazwie DataPages – domyślne ustawienia co do rozmiaru plików i ich poszerzania są dla nas wystarczające.

USE master
GO

CREATE DATABASE DataPages
GO

Następnie w ramach tej bazy stwórzmy tabelę o nazwie testTable zawierającą jedno pole o nazwie Col1 typu char(8000). Typ ten nie został wybrany przypadkowo ponieważ 1 znak typu char zajmuje jeden bajt tak więc jeden wiersz powinien zajmować jedną stronę.

USE DataPages
GO

CREATE TABLE testTable
(
col1 CHAR(8000)
)

Następnie wstawmy do tej tabeli jeden wiersz:

INSERT INTO dbo.testTable
        ( col1 )
SELECT REPLICATE('a',8000)
GO

Następnie możemy podejrzeć strony zaalokowane do naszej tabeli. aby to zrobić użyjemy zapytania DBCC IND, która jako pierwszy parametr przyjmuje nazwę bazy danych, następnie nazwę tabeli wraz ze schematem, trzecim parametrem jest numer strony (jeśli chcemy wszystkie strony podajemy -1).

DBCC IND(
	'DataPages',
	'dbo.testTable'
	,-1
	)

W rezultacie otrzymujemy zestaw stron gdzie każdy wiersz to jedna strona. Jak widać zostały zaalokowane dwie strony – przyjrzymy się poszczególnym właściwościom jakie widzimy używając komendy DBCC IND

Pierwszą właściwością jest PageFID czyli identyfikator pliku w którym znajduje się strona, PagePID czyli identyfikator strony w ramach pliku. Obie te liczby pozwalają zidentyfikować stronę w ramach konkretnej bazy danych. Może tutaj również pojawiać się pytanie dlaczego mamy dwie strony skoro dodaliśmy jeden wiersz, który teoretycznie powinien zająć jedną stronę? Dzieje się tak dlatego że jedną z nich jest strona IAM Page identyfikowana przez właściwość PageType równą 10 o której powiemy sobie w kolejnym artykule.

W dalszej części stwórzmy sobie w naszej bazie dwie dodatkowe tabele o tej samej strukturze co tabela testTable:

CREATE TABLE testTable2
(
col1 CHAR(8000)
)
GO

CREATE TABLE testTable3
(
col1 CHAR(8000)
)

Następnie wstawmy do nich po 4 wiersze (gdzie jeden wiersz zajmuje całą stronę):

INSERT INTO dbo.testTable2
        ( col1 )
SELECT REPLICATE('a',8000)
GO 3

INSERT INTO dbo.testTable3
        ( col1 )
SELECT REPLICATE('a',8000)
GO 3

Jak widać użyłem w przykładzie komendy GO 3 która oznacza, iż wykonamy tą powyższe zapytanie TSQL 3-krotnie. Teraz podejrzyjmy strukturę nowych tabel z wykorzystaniem DBCC IND.

DBCC IND(
	'DataPages',
	'dbo.testTable2'
	,-1
	)
DBCC IND(
	'DataPages',
	'dbo.testTable3'
	,-1
	)

W rezultacie otrzymaliśmy następujący wynik (pokazujemy tylko część rezultatu otrzymanego przez powyższe komendy aby nie zaciemniać obraz):

Jak widać po numerach stron (PagePID) ekstenty tych obiektów nie są typu uniform tylko mixed – co za tym idzie ekstenty te zawierają strony danych pochodzące z tych właśnie dwóch obiektów. Następnie wstawmy do każdej z tabel dodatkowo po 10 wierszy (używając powyższej komendy i GO 10). Następnie w analogiczny sposób do powyższego wyświetlmy strukturę stron.

Każda z tabel ma teraz po 14 wierszy czyli SQL Server alokuje dla nich 15 stron (14 stron dla każdego z wierszy + 1 strona IAM). Można zauwazyć, że zgodnie z tym co napisaliśmy wyżej pierwsze 8 stron w każdej z tabel jest częścią ekstentu mixed (numery stron nie są uporządkowane), a kolejne 6 wchodzi w skład ekstentu typu uniform (numery stron są uporządkowane).

Ten sposób alokacji ekstentów w SQL Server jest warty zapamiętania gdyż wiąże się z nim wiele ciekawych zjawisk. Jednym z nich jest fragmentacja o której opowiemy w ramach innego artykułu natomiast można uprościć, iż chcemy aby nasze obiekty były jak najmniej pofragmentowane, a jak najbardziej uporządkowane. Jak można przypuszczać patrząc na cały artykuł małe obiekty które są alokowane w ramach mixed ekstentów będą miały bardzo wysoki stopień fragmentacji i nie jest to nic złego, wynika to po prostu ze sposobu ich alokacji przez SQL Server. Wiedza to pozwoli nam uniknąć dezorientacji w momencie gdy po przebudowaniu tabeli ona nadal wskazuje na wysoki stopień fragmentacji. Mam nadzieję, że temat stron i ekstentów jest teraz dla Was jasny.