Ciao Marco
Dunque, facciamo un passo indietro: un indice è una struttura in cui dati sono ordinati. Senza questa caratteristica l'indice non ha nessuna
utilità.
L'ordinamento dei dati deve quindi essere mantenuto,
ma questo non può essere fatto a scapito delle performance. Per quasto
motivo è stato introdotto un livello di astrazione per cui si parla di
ordinamento logico ed ordinamento fisico.
L'ordinamento
logico ci permette di sapere in quale ordine dobbiamo leggere le pagine
(1, 2, 3 e via dicendo), in quanto tale ordinamento potrebbe non essere
quello in cui le pagine si trovano fisicamente (l'ordinamento fisico
potrebbe essere infatti 2, 3, 1...).
Per capire per quale
motivo questo accade, è possibile pensare a questa situazione:
supponiamo di avere un indice composto da dieci pagine. Dobbiamo ora
inserire un nuovo dato e, per mantenere l'utilità dell'indice dobbiamo
inserirlo nella posizione corretta, seguendo l'ordinamento imposto
dall'indice stesso. Nel nosotro caso ipotizziamo di dover inserire un
dato nella pagina 5. La pagina 5, però, è già piena. Se dovessimo
mantere l'ordinamento fisico, dovremmo quindi creare una nuova pagina
alla fine di quelle già presenti, diciamo la pagina 11. In questa
pagina dovremmo copiare i dati della pagina 10, nella pagina 10
copieremmo i dati della pagina 9, e via dicendo fino a quando non
arriviamo a fare sufficente spazio nella pagina 5 che a questo punto
può contenere il dato che dobbiamo inserire.
Il tutto è perfettamente funzionante ma, come potrai immaginare, molto molto lento.
Potremmo quindi trovare un modo più furbo per ottenere la stessa cosa.
Nel momento in cui ci troviamo a dover inserire un nuovo dato nella
pagina 5 e questa è piena, potremmo creare una nuova pagina, diciamo la
11, e copiarci dentro i dati della pagina 5 in modo da far spazio alla
nuova riga che dobbiamo inserire. Fatto ciò, dovremmo semplicemente
ricordarci che la sequenza in cui dobbiamo leggere le nostre pagine in
modo che l'ordinamento sia preservato è
1 2 3 4 5 11 6 7 8 9 10
In pratica abbiamo un ordinamento logico che differisce dall'ordinamento fisico.
Questo è quello che accade proprio in SQL Server, che può quindi
accedere ai dati sempre in modo ordinato anche se tale ordine non è
fisicamente replicato sul disco.
L'esempio che ti ho fatto è quello più evidente, ma tieni anche conto
che anche nel caso in cui noi dovessimo invece inserire un dato in
fondo alla nostra sequenza, ossia inserire qualcosa dopo la pagina 10,
andando quindi a creare la pagina 11, non è detto che questa sia
fisicamente allocata nella giusta sequenza, in quanto la scelta di
quale pagina libera utilizzare dipende dalla dimensione della tabella.
Se la tabella è più grossa di 64Kb (quindi di 8 pagine) SQL Server sta
utilizzando degli Uniform Extent e quindi è possibile (ma non è detto)
che la pagina sia messa nella sequenza giusta (a meno che non sia
necessario allocare un nuovo extent), ma se la tabella è più piccola di
64Kb SQL Server utilizza dei mixed extent, e quindi - in pratica - la
pagina viene presa da dove c'è posto senza troppe preoccupazioni
(questo perchè la tabella è talmente piccola che non ha praticamente
senso preoccuparsi di ottimizzare l'accesso sequenziale alle pagine
della stessa).
L'ordinamento logico delle pagine è gestito sia dal b-tree dell'indice
(quando un indice esiste) sia dall'header posto nelle pagine stesse,
dove ogni pagina memorizza il numero della pagina precedente e di
quella successiva (questa struttura si chiama double-linked list).
La posizione fisica delle pagine è invece memorizzata nelle pagine IAM
(index allocation map, che a scapito del nome, servono anche per
tabelle senza indici) che permettono di sapere in quali extent (e
quindi in quali pagine) è memorizzato un oggetto.
Un discorso
molto simile avviene per le righe nelle singole pagine, dove la
sequenza in cui le righe devono essere lette per risultare nell'ordine
corretto è definita nella sezione dedicata ai Row-Offset, posta in
fondo alla pagina.
Ovviamente più l'ordine logico differisce da quello fisico, più le
performance di accesso ai dati (in particolare per le operazioni di
"scan") sono compromesse. Quando un indice viene manutenuto (ALTER
INDEX REBUILD, ad esempio), l'indice viene distrutto e ricreato,
facendo quindi in modo che ordinamento logico e fisico siano indentici,
in modo da avere le performance ottimali.
Come vedi il discorso è complesso :-), quindi un esempio che replichi la situazione descritta in precedenza credo che possa essere di aiuto:
USE [SolidQCourseDemo]
GO
/*
Tabella di prova fatta in modo tale che in una pagina ci possa stare una sola riga
*/
IF (OBJECT_ID('dbo.TestLP') IS NOT NULL) DROP TABLE dbo.TestLP;
GO
CREATE TABLE dbo.TestLP
(
id INT NOT NULL,
bigcolumn CHAR(8000) NOT NULL
)
GO
CREATE CLUSTERED INDEX IXC ON dbo.TestLP(id);
GO
/*
Inserimento valori di prova.
Notare il "buco" tra i valori 4 e 6. La riga con id=5 è assente.
*/
INSERT INTO dbo.TestLP VALUES (1, 'A')
INSERT INTO dbo.TestLP VALUES (2, 'B')
INSERT INTO dbo.TestLP VALUES (3, 'C')
INSERT INTO dbo.TestLP VALUES (4, 'D')
INSERT INTO dbo.TestLP VALUES (6, 'F')
INSERT INTO dbo.TestLP VALUES (7, 'G')
INSERT INTO dbo.TestLP VALUES (8, 'H')
INSERT INTO dbo.TestLP VALUES (9, 'I')
INSERT INTO dbo.TestLP VALUES (10, 'L')
INSERT INTO dbo.TestLP VALUES (11, 'M')
GO
/*
Analisi dell'indice: è fatto esattamente da 10 pagine (a livello foglia)
*/
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.TestLP'), NULL, NULL, 'DETAILED') WHERE index_level = 0
GO
/*
Inseriamo il valore "mancante"
*/
INSERT INTO dbo.TestLP VALUES (5, 'Z')
GO
/*
Facciamo una semplice select.
Siccome esiste un indice cluster, SQL Server restituisce i dati utilizzando le informazioni
presenti nella double-linked list, e quindi restituisce i dati ordinati (questa assunzione è valida solamente
se SQL Server non parallelizza la scansione dell'indice.)
*/
SELECT * FROM dbo.TestLP
GO
/*
Distruggiamo l'indice cluster
*/
DROP INDEX IXC ON dbo.TestLP
GO
/*
Rifacciamo la select.
Siccome NON esiste un indice cluster, SQL Server restituisce i dati utilizzando le informazioni
presenti nella IAM e quindi i dati vengono restituiti nella sequenza fisica in cui si trovano
*/
SELECT * FROM dbo.TestLP
GO
/*
Ricreiamo l'indice e distruggiamolo immediatamente per verificare che
la creazione di un'indice faccia in modo che l'ordine logico e quello
fisico siano lo stesso
*/
CREATE CLUSTERED INDEX IXC ON dbo.TestLP(id);
DROP INDEX IXC ON dbo.TestLP;
GO
/*
Rifacciamo la select.
Siccome NON esiste un indice cluster, SQL Server restituisce i dati utilizzando le informazioni
presenti nella IAM e quindi i dati vengono restituiti nella sequenza fisica in cui si trovano che,
ora, sono nella stessa sequenza dell'ordine logico
*/
SELECT * FROM dbo.TestLP
GO
Un tool molto bello per mettere il naso in modo grafico nell'architettura dello storage di SQL Server è SQL Internals Viewers:
http://www.sqlinternalsviewer.com
Questo tool utilizza delle funzioni non documentate di SQL Server che
permettono di mettere "a nudo" l'intera architettura di storage.
Volendo è possibile anche arrivare a queste informazioni a mano. Questa
query ci da le informazioni fisiche sulla locazione delle pagine ROOT,
IAM e sulla prima pagina della tabella TestLP:
SELECT
*
FROM
sys.system_internals_allocation_units au
INNER JOIN
sys.partitions p ON au.container_id = p.hobt_id
WHERE
au.[type] = 1
AND
p.[object_id] = OBJECT_ID('dbo.TestLP')
nel mio caso alla colonna "first_page" trovo il valore 0xA80600000100.
Questo valore va letto cosi: i primi due byte partendo da destra
rappresentano il numero di file di riferimento, i successivi il numero
di pagina. Anche la lettura dei valori dei byte deve essere fatta
partendo da destra:
File:
0100 -> 0001 = 1
Pagina
A8060000 -> 000006A8 = 1704
si può ora utilizzare il comando DBCC PAGE (rigorosamente non documentato nè supportato) per vedere il contenuto della pagina:
DBCC PAGE(21, 1, 1704, 2) WITH TABLERESULTS
21 = ID Del database
1 = Numero del file
1704 = Numero della pagina
2 = Modalità di funzionamento di DBCC PAGE
Il risultato è il DUMP della pagina 1:1704, che poi contiene la riga con id=1 e bigcolum='A' della nostra tabella.
E' possibile notare sia le informazioni utilizzate per costruire la
double-linked list (righe con Field = m_prevPage e m_nextPage) e, come
ultime righe, il Row-Offset.
Ovviamente il tool Sql Internals Viewer rende tutto più comodo :-)
I riferimenti sui BOL per quanto riguarda tutto questo discorso sono:
http://msdn2.microsoft.com/en-us/library/ms188270.aspx
http://msdn2.microsoft.com/en-us/library/ms189051.aspx
http://msdn2.microsoft.com/en-us/library/ms190969.aspx
http://msdn2.microsoft.com/en-us/library/ms175195.aspx