Impatto del Page Split dei livelli intermedi sulle prestazioni

Cosa è il page split ? E' un meccanismo di sincronizzazione grazie al quale SQL Server mantiene aggiornato il contenuto dei nostri indici con quanto è realmente contenuto nella tabella. Per fortuna esiste, ma se si verifica troppo frequentemente potrebbe mettere a dura prova le performance del nostro DB.
Perchè si verifica ?
Si verifica perchè SQL salva i nostri dati in allocazioni base chiamate Data Page che hanno una dimensione di 8k ovvero 8192 byte ciascuna.....
Togliendo alcuni byte di sistema ci restano 8060 byte utilizzabili per archiviare i nostri dati.
Sia le pagine dati sia le pagine di indice sono allocate in questo modo. Il page split si verifica quando è necessario scrivere una riga in una pagina già riempita al massimo della sua capacità. La riga deve rispettare l'ordinamento dato dall'indice e quindi deve per forza essere inserita in quella pagina.....
Questo meccanismo, quindi, ha come controindicazione l'aumento delle operazioni di I/O sia in fase di scrittura che di ricerca.
Le performance dipendono in modo molto pesante dalla quantita di I/O che SQL server è costretto a fare per evadere le nostre query, meno ne facciamo più veloci siamo.

Prendiamo lo script seguente

Create Database PageSplitDb
Go

Use PageSplitDb
Go

-- Creo una tabella la cui riga è SEMPRE lunga 7800 byte
-- in modo da avere solo una riga per pagina
Create table TestSpilt
(
 Chiave char(800), 
 Valore char(7000) 
)      

-- Inserisco 2 Righe 
Insert into dbo.TestSpilt (Chiave,Valore)
 values('0','Valore 0'),('a','Valore a')

-- Creo un indice cluster sulla colonna "chiave"
create clustered index idxClustered on TestSpilt(chiave)

L'indice cluster che creo ha dimensione riga di 800 byte dato che il campo Chiave è dichiarato come char(800).
Ogni singola pagina indice conterrà fino a 10 righe e referenzierà fino ad un massimo di 10 pagine.
8060 byte = spazio disponibile in ogni data page
800 byte = campo chiave

8060 / 800 = 10,075 quindi 10 righe in ogni pagina componente l'indice

Guardiamo come l'indice viene creato con la seguente query:

SELECT index_level
  ,index_type_desc
  ,page_count
  ,record_count
  ,avg_record_size_in_bytes 
  FROM sys.dm_db_index_physical_stats( DB_ID( ) , OBJECT_ID( 'dbo.TestSpilt' ) , NULL , NULL , 'DETAILED' )s
WHERE index_id = ( 
                     SELECT index_id
                       FROM sys.indexes
                       WHERE name = 'idxClustered');  

Dato che ogni riga alloca una pagina ed abbiamo inserito 2 righe il nostro indice sarà composto da 3 Pagine, 2 appartenenti al leaf level (index_level = 0)
e 1 al livello Index 1(root level in questo caso)

Recuperiamo ora i PID (Page id ) delle pagine che compongono il nostro indice cluster.

create table #PageView ( 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 

insert #PageView exec ('DBCC IND (''PageSplitDb'', ''TestSpilt'', 1) with tableresults') 
go 

--Visualizziamo quanti livelli compongono il nostro indice
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 #PageView 
order by isnull(indexLevel,255) desc 
drop table #PageView

Pid 94 = Root Level, la riconoscete perchè il valore della colonna Index_level è il più elevato (vale 1 in questo caso)
Pid 89 e 93 = Data Page (Index Level = 0)

La pagina con Pagetype = 10 è la IAM, Qui trovate la descrizione di cosa rappresenta.
Guardiamo ora cosa contiene la pagina di root level.

-- Se non lo abilito non posso entrare nella data page
Dbcc traceon(3604)
Dbcc page(PageSplitDb,1,94,3) With Tableresults

La colonna "Chiave" che vedete nell'immagine è proprio il campo chiamato "chiave" nella nostra tabella.
Il root level contiene quindi 2 righe :
Row 0 punta la pagina pid 89 che contiene i valori tra il Null e minori di 'a'
Row 1 punta la pagina pid 93 che contiene i valori >= 'a'

A questo punto guardiamo cosa contengono le pagine Dati di indirizzo 89 e 93
La PID 89 contiene la riga '0', 'Valore 0' ;

La PID 93 contiene la riga 'a', 'Valore a' ;:

Cerchiamo ora di causare il page split al livello intermedio.... A dire il vero nel nostro caso è semplice.
Ci basta inserire altre 8 righe. Considerando che per riempire la pagina index page con PID 94 servono 10 righe (Chiave è dichiarato come char(800)) e ne abbiamo già inserite 2 dobbiamo aggiungere altre 8 righe.

Insert into dbo.TestSpilt (Chiave,Valore)
 values('3','Valore 3')
   ,('4','Valore 4')
   ,('5','Valore 5')
   ,('6','Valore 6')
   ,('f','Valore f')
   ,('g','Valore g')
   ,('h','Valore h')
   ,('i','Valore i')

Utilizzando la query usata precedentemente guardiamo quanti livelli compongono l'indice e quante data page abbiamo ora :

L'indice ora è composto da 3 livelli......
Rieseguiamo la query che restitituisce i PID delle pagine e vedremo che la root non è più la Pid 94 ma la Pid 178 !!

Guardiamo cosa contiene la Pid 178

Dbcc page(PageSplitDb,1,178,3)

Il root level contiene ancora 2 righe

Row 0 punta la pagina pid 94 che contiene i valori tra il Null e minori di '6'
Row 1 punta la pagina pid 179 che contiene i valori >= '6'

Guardiamo cosa contiene la pagina Pid 94 del livello intermedio 1

Dbcc page(PageSplitDb,1,94,3)

ecco qui il suo contenuto

Ciascuna riga punta una pagina del leaf level.
Lo stesso discorso lo possiamo riportare per la pid 179.

Provochiamo ora un'ulteriore page split.
Per causare il page split dobbiamo inserire altre righe.
Inserendone 8 con valori compresi tra '0' e '3' causiamo lo il page split della pagina 94 lasciando inalterata la Pid 179.
Perchè ? Perchè l'indice l'abbiamo creato sul campo "chiave" ordinandolo Ascending, quindi i valori che andiamo ad inserire sono da posizionare logicamente nella pagina Pid 94 che contiene i dati tra il Null e minori di '6'.

--Causo Page Split al level 1
Insert into dbo.TestSpilt (Chiave,Valore)
 values('0','Valore 0'),('0','Valore 0'),('1','Valore 1'),('1','Valore 1')
   ,('2','Valore 2'),('2','Valore 2'),('3','Valore 1'),('3','Valore 1')

Vediamo se è vero utilizzando la stessa query di prima

In effetti l'indexlevel 1 ora è composto da 3 Data Page mentre prima erano 2. La data page 179 è inalterata mentra la 94 ha "splittato"
il suo contenuto con la pagina 186

Dbcc page(PageSplitDb,1,179,3)
Dbcc page(PageSplitDb,1,94,3)      
Dbcc page(PageSplitDb,1,186,3)

Qui il contenuto delle 3 page del livello 1

Quante righe dovremmo inserire ora per causare un'ulteriore split a livello 1 ?
Bhè è presto detto....
bastano 6 righe con chiave compresa tra '0' e '1' per splittare nuovamente la Pid 94;
bastano 2 righe con chiave compresa tra '1' e '5' per splittare nuovamente la Pid 186;
bastano 4 righe con chiave compresa tra '6' e 'i' per splittare nuovamente la Pid 179;

Il Page Split quindi interviene in fase di inserimento sulla struttura B-Tree del nostro indice non solo nel leaf level ma anche in tutti i livelli intermedi ove necessario.
Tutto ciò garantisce la sincronizzazione tra la struttura/contenuto del nostro indice e il contenuto della tabella.
Il problema è che aumenta la durata delle operazioni di scrittura dei dati e frammenta l'indice su cui si verifica, rallentando di conseguenza anche le operazioni di lettura.
Abbiamo modo di diminuire il page split ? assolutamente si....
Manteniamo le chiavi che compongono il nostro indice il più piccole possibile e utilizziamo un Fill factor/PadIndex adeguato alle nostre esigenze.
Ovviamente la stessa attenzione va dedicata anche alle colonne non indicizzate che saranno archiviate nella tabella.
Possiamo configurare il fillfactor/Padindex solo in fase di creazione dell'indice stesso.
Inoltre il Page Split causa anche dei Lock di tipo Exclusive (Lock X ) durante l'operazione di aggiornamento della struttura

A questo punto potete eliminare il DB di test...

Drop database PageSplitDb

Spero vi sia stato d'aiuto questo lungo post...
Ciao

Alla prossima....