Spesso mi rendo conto che capire come SQL Server gestisca le sue strutture ad albero, le datapages, le IAM, gli extent, ... non sia così semplice.
Mi piacerebbe allora, con questo post, costruire un esempio che possa raccontare in maniera semplice il meccanismo con cui il database engine mantiene e gestisce le informazioni delle nostre tabelle.
Per farlo costruiremo un database di test che andrà a contenere una sola tabella con una struttura molto semplice.
set nocount on
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'testRootPage')
DROP DATABASE testRootPage
GO
CREATE DATABASE testRootPage
GO
USE testRootPage
GO
Definito il database andiamo a costruire la tabella che utilizzeremo nel nostro esempio.
Prima di proseguire ricordiamo tre concetti:
- SQL Server memorizza i dati in pagine, unità elementari ognuna di 8 Kb
- SQL Server mantiene i dati memorizzato in una struttura ad albero bilanciato (BTree+)
- Parlando di indici cluster (per ridurre il campo di attenzione): al livello foglia (leaf level) vengono mantenute la pagine contenenti i dati (le righe della tabella). Nei livelli superiori solo la chiave dell'indice (quindi il valore stesso della chiave che è stata definita, che sia composta da una o più colonne)
La nostra tabella la disegniamo in modo tale da poter contenere una singola riga per ogni pagina dati.
Inoltre, sempre per semplicità, definiamo una colonna idRecord, che utilizzeremo come chiave, con una dimensione di 93 bytes in modo che abbia, con i 7 bytes di overhead necessari alla gestione dei metadati, una dimensione di riga sulle pagine "non leaf-level" di 100 bytes (concetto numero 3 poco sopra - ovvero: in ogni pagina indice, quindi in tutte i livelli tranne quello foglia, le pagine potranno contenere tante righe quante ne stanno in 8060 bytes. Quindi 8060 bytes disponibili / 100 bytes per la dimensione della chiave = 80 righe per pagina.).
create table test
(
idRecord char(93) not null,
note char (7500)
)
GO
-- definisco la PK cluster sulla colonna idRecord
alter table test add
constraint tkTest primary key clustered (idRecord)
go
Inserisco cento righe così da ottenere 100 data pages a livello foglia.
declare @i int = 100
while @i > 0
begin
insert test (idRecord, note) values ('abcde' + CAST(@i as varchar(10)), 'valore che scrivo nella riga')
set @i -= 1
end
go
select COUNT(*) as numRows from test
go
La select count torna numRows = 100, ovvero significa che ho 100 righe al livello foglia del nostro albero BTree (e, quindi, dovrei avere 100 data pages).
Vediamo se è vero analizzando come è stato costruito l'albero dei nostri dati:
select
index_type_desc, alloc_unit_type_desc, index_depth, index_level,
page_count, record_count, avg_record_size_in_bytes
from sys.dm_db_index_physical_stats(db_id(N'testRootPage'), object_id('dbo.test'), NULL, NULL, 'DETAILED')
order by index_level desc
go
Con il risultato ottenuto posso vedere che la struttura ad albero costruita dal database engine per memorizzare i miei dati è costituta da 3 livelli.
Il livello della ROOT è il livello più alto (2 nel mio esempio) fino ad arrivare a 0 per i dati. Ovvero:
- la pagina di root (index_level = 2, page_count = 1, record_count =2)
- un secondo livello (index_level = 1, page_count = 2, record_count = 100)
- il livello foglia, ovvero i dati (index_level = 0, page_count = 100, record count = 100)
Riepilogando:
- nella pagina di root ho due righe (quindi so che la root avrà due figli ed infatti il livello successivo è fatto di due pagine)
- nel livello 1 ho due pagine che contengono un totale di 100 righe (infatti il livello foglia è fatto di 100 pagine)
- nel livello foglia ho 100 pagine dati (una per record)
A questo punto voglio visualizzare informazioni su tutte le pagine utilizzate.
Per farlo utilizzo il comando DBCC IND anando a memorizzare il suo risultato all'interno di una tabella temporanea (per avere una successiva semplicità di interrogazione):
if object_id('tempdb..#ind') is not null
drop table #ind
go
create table #ind
(
PageFID tinyint,
PagePID int,
IAMFID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int
)
go
/* eseguo il comando */
insert #ind
exec ('DBCC IND (''testRootPage'', ''test'', 1) with tableresults')
go
/* interrogo il risultato */
select
pageFID, pagePID, pageType, indexLevel,
case
when pageType = 10 then 'IAM'
when pageType = 2 then 'index page'
when pageType = 1 then 'data pages'
else 'non definito'
end as pageType,
nextPagePID, prevPagePID
from #ind
order by isnull(indexLevel,255) desc
go
Questo il risultato della SELECT:
Ovvero 104 righe rappresentanti:
- una riga per la pagina IAM
- una riga per la pagina di ROOT
- due righe per le due pagine del primo livello
- cento righe per il livello foglia (le pagine dati)
Cosa posso leggere sul risultato?
- pagina numero 55: IAM
- pagina numero 248: pagina di ROOT del nostro albero BTree+
- pagine numero 249, 77: pagine del livello 1
- ... tutte le pagine dati ...
Se volessi rappresentare l'albero in forma grafica:
Nota: l'immagine, per problemi di spazio, non contiene tutte le pagine del livello foglia (100).
Ora che conosco "l'indirizzo" delle mie pagine (conosco il PageFID ed il PagePID, ovvero ID del file e ID della pagina) posso andare a metterci il naso per vederne il contenuto.
Per esempio apriamo la pagina di ROOT per vedere le informazioni che contiene.
Per farlo utilizziamo il comando (non documentato):
dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
Le opzioni di stampa (printopt) del risultato:
0 - solo page header
1 - page header + per-row hex dumps e dump page slot array
2 - page header + page hex dump
3 - page header + detailed per-row
Prima di lanciare il comando, devo fare in modo che il risultato mi venga presentato a video (e non tracciato soltanto nel log) abilitando il flag 3604 come:
DBCC TRACEON (3604)
DBCC Page (testRootPage, 1, 248, 3)
Questo il contenuto della pagina di ROOT:
In pratica abbiamo soltanto due righe, come già mi aspettavo (quando abbiamo analizzato poco sopra come è stato costruito l'albero dei nostri dati l'engine ci aveva già detto che la ROOT aveva due record).
Vedo: ChildPageId = 77 e 249 (che sono proprio le pagine del livello 1)
Se visualizzo il contenuto delle pagine del livello 1 potrò quindi ottenere l'elenco delle pagine figlie, ovvero delle foglie di ciascun nodo.
La pagina 77:
DBCC Page (testRootPage, 1, 77, 3)
La pagina 249:
DBCC Page (testRootPage, 1, 249, 3)
Se voglio mettere il naso nelle pagine del livello foglia, ovvero dove sono contenuti i dati, ricorro sempre al comando DBCC PAGE:
DBCC Page (testRootPage, 1, 270, 3)
Nel risultato, che mi mostra il contenuto della pagina, posso anche vedere il contenuto del record:
