Per i tipi dato con lunghezza a discrezione dell'utente, come per esempio i varchar, varbinary etc... è necessaria una maggiore attenzione.
I dati sono allocati in Data Pages dalla dimensione di 8K, quindi, in teoria, la lunghezza massima di un record inseribile in una data page è di 8kb (Per la precisione, tolti alcuni byte di sistema, a noi ne restano disponibili 8060).
Ma cosa accade se la somma delle dimensioni dei campi contenuti in una tabella supera la soglia fatidica ?
Proviamo a fare un esempio
Creo un database
CREATE DATABASE [DB_Data_Alloc] ON PRIMARY ( NAME = N'Lob_Data_Db', FILENAME = N'C:\SQL 2012\UserDatabase\DB_Data_Alloc_Data.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'Lob_Data_Db_log', FILENAME = N'C:\SQL 2012\UserDatabase\DB_Data_Alloc_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO
Genero la struttura delle tabelle
Use [DB_Data_Alloc] Go create table TAB_IN_ROW_DATA ( id tinyint identity, valore varchar(25) ) Go create table TAB_ROW_OVERFLOW_DATA ( id tinyint identity, valore1 varchar(5000), valore2 varchar(5000) ) Go create table TAB_LOB_DATA ( id tinyint identity, valore varchar(max) ) Go
La tabella TAB_IN_ROW_DATA accetta record la cui lunghezza massima è di 28 byte, quindi in una sola data page riusciamo ad inserire 8060/28 = 287 record, niente male !!!.
id tinyint = 1 byte
valore varchar(25) = 25 byte + 2 di overhead
La tabella TAB_ROW_OVERFLOW_DATA accetta record la cui lunghezza massima è di 10005 byte che sforano gli 8060 della singola datapages.
id tinyint = 1 byte
valore1 varchar(5000) = 5000 byte + 2 di overhead
valore2 varchar(5000) = 5000 byte + 2 di overhead
La tabella TAB_LOB_DATA accetta record la cui lunghezza massima è di 2 GB, molto ma molto più dei canonici 8060 byte.
id tinyint = 1 byte
valore varchar(max) = Accetta dati dalla lunghezza massima di 2 GB
Come è possibile quindi dichiarare tabelle come TAB_ROW_OVERFLOW_DATA e TAB_LOB_DATA ?
L'Engine in questi casi ci viene incontro splittando automaticamente e dove necessario il contenuto dei record su più data pages.
Vediamo come, proviamo ad inserire dei record ad hoc
--Lunghezza record inferiore a 8060 byte
Insert into TAB_IN_ROW_DATA(valore) values('Abc')
Go
--Lunghezza record superiore a 8060 byte
Insert into TAB_ROW_OVERFLOW_DATA(valore1,valore2) values(Replicate('A',5000),Replicate('B',5000))
Go
--Lunghezza record superiore a 8060 byte
Declare @str as varchar(max) = cast(Replicate('A',8000) as varchar(max))+ cast(Replicate('B',8000) as varchar(max))
Insert into TAB_LOB_DATA(valore) values(@str)
Go
Vediamo come SQL Server ha gestito l'allocazione delle data pages per ogni tabella
SELECT
OBJECT_NAME([object_id]), alloc_unit_type_desc, page_count, avg_page_space_used_in_percent
,record_count, min_record_size_in_bytes ,max_record_size_in_bytes,forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , 'DETAILED');
Go
La colonna che ci interessa capire è [alloc_unit_type_desc] che descrive l'Allocation Unit utilizzata per gestire i dati della tabella.
Una Allocation Unit è una collezione di Data pages
IN_ROW_DATA = Allocation Unit contenente le data pages di tabelle che non violano il vincolo di lunghezza max di 8060 byte
ROW_OVERFLOW_DATA = Allocation Unit contenente le data pages di tabelle con colonne a lunghezza variabile che violano il vincolo di lunghezza max di 8060 byte
LOB_DATA = Allocation Unit contenente le data pages di tabelle con colonne di tipo text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) oppure CLR
TAB_IN_ROW_DATA
La tabella TAB_IN_ROW_DATA è gestita mediante una allocation unit di tipo IN_ROW_DATA ed è composta da una sola data page dato che la lunghezza del record è di molto inferiore al limite degli 8060 byte. Nessun surplus di lavoro per l'engine, quindi nessun problema.
TAB_ROW_OVERFLOW_DATA
TAB_ROW_OVERFLOW_DATA è gestita mediante una allocation unit di tipo IN_ROW_DATA ed una di tipo ROW_OVERFLOW_DATA ed è composta da 2 data pages.
Troviamo le data pages contenenti il nostro dato
Select *,sys.fn_PhysLocFormatter( %%Physloc%% ) from TAB_ROW_OVERFLOW_DATA Go
Si trova nel Fileid 1 nella pagina 144 slot 0
Vediamo cosa contiene la pagina 144
Dbcc Traceon(3604)
Go
Dbcc Page('DB_Data_Alloc',1,144,3)
Go
La data page 144 contiene la prima parte del record cioè il campo id ed il campo valore1....
Valore2 è in un'altra data page, la 127. Dato che la lunghezza del record è superiore agli 8kb l'engine ha splittato su 2 data pages in 2 allocation unit di tipo differente il record.
Vediamo cosa contiene la pagina 127
Dbcc Page('DB_Data_Alloc',1,127,3)
Go
Cosa accade quando vado a richiedere i dati della tabella ?
Dipende....
Se richiedo solo una porzione delle colonne, vale a dire id e valore1 l'engine limita l'operazione alla pura lettura della data page nell'allocation unit IN_ROW_DATA, limitando i danni....
Se però richiedo l'intero record o anche solo la porzione allocata nell'allocation unit di tipo ROW_OVERFLOW_DATA, allora l'engine deve leggere 2 data pages... Ho raddoppiato quindi il carico di lavoro...
Set statistics io on Select id,valore1 From TAB_ROW_OVERFLOW_DATA Select id,valore2,valore1 From TAB_ROW_OVERFLOW_DATA Set statistics io off
TAB_LOB_DATA
La tabella TAB_LOB_DATA è gestita mediante una allocation unit di tipo IN_ROW_DATA ed una di tipo ROW_OVERFLOW_DATA ed è composta da 3 data pages, 1 nell'allocation unit IN_ROW_DATA e le altre nell'allocation unit LOB_DATA.
Troviamo le data pages contenenti il nostro dato
Select *,sys.fn_PhysLocFormatter( %%Physloc%% ) from TAB_LOB_DATA Go
Vediamo cosa contiene la pagina 156
Dbcc Page('DB_Data_Alloc',1,156,3)
Go
Contiene solo la colonna id nonché i riferimenti alle 2 data pages contenenti il resto del record....
La data page 150 contiene i primi 8040 byte del campo valore 1, mentre la data page 146 i restanti 7960.
Controlliamole
Dbcc Page('DB_Data_Alloc',1,150,3)
Go
Dbcc Page('DB_Data_Alloc',1,146,3)
Go
Data page 150:
Data page 146
Ma cosa accade in caso di lettura della tabella ?
Come nell'esempio precedente... Dipende....
Se richiedo solo la colonna id l'engine limita l'operazione alla pura lettura della data page nell'allocation unit IN_ROW_DATA, limitando i danni....
Se però richiedo l'intero record o anche solo la porzione allocata nell'allocation unit di tipo LOB_DATA, allora l'engine deve leggere molte datapages in più !! Ho appesantito tantissimo il lavoro di I/O
Set statistics io on Select id From TAB_LOB_DATA Select id,valore From TAB_LOB_DATA Set statistics io off
Pensate alle performance di un ipotetico DB contenente molte righe come quella della tabella TAB_LOB_DATA, avremmo performance molto scadenti. Le ricerche nel contenuto sarebbero inutilizzabili a causa dell'elevatissimo numero di operazioni di I/O che l'engine dovrebbe implementare...
Meno I/O faccio fare più velocemente ottengo i dati !!!
Evitiamo se possibile i tipi di dato text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) e CLR che abbiamo visto richiedere molto I/O per essere gestiti.
Se proprio dobbiamo salvare nel DB dei testi molto lunghi è meglio sfruttare il Filestream e le FileTable che ho descritto in questo post.
Ciao
Luca
Nessun commento:
Posta un commento