Un articolo sull’uso delle funzionalità di SQL Server per il controllo dell’efficienza degli indici dei database. Il comando DBCC ShowContig e il suo successore, sys.dm_db_index_physical_stats,
Nota: (2013) l’articolo è stato scritto per SQL Server 2000 e 2005 ma nonostante sia stato dichiarato obsoleto DBCC esiste ancora e funziona perfettamente anche in SQL Server 2012
Introduzione
Antefatto
Qualche settimana fa, mi sono trovata a dover intervenire presso un cliente per capire i motivi per cui il gestionale sembrava dare errori casuali all’interno di procedure consolidate da tempo.
Per farlo, d’accordo con il cliente ed il fornitore del gestionale, abbiamo deciso di agganciare il Profiler di SQL Server e verificare cosa accadeva a livello di server dati quando si verificavano gli errori. Oltre agli errori, era stata riportata una notevole lentezza delle risposte sui dati maggiormente usati, ovvero le tabelle più grandi.
Cosa abbiamo fatto
Dopo aver avviato il profiler, ho deciso di fare qualche analisi sui dati, per cercare di capire se i problemi potevano essere dovuti anche a come il database era stato utilizzato.
Per fare questo, ho utilizzato per prima cosa il DBCC, comando standard T-SQL per il controllo della consistenza del database, evolutosi negli anni acquisendo una ricca serie di funzionalità adatte alla ricerca dei problemi di base relativi alle performance.
La funzionalità che serviva in questo caso è DBCC SHOWCONTIG, questa funzione permette di rilevare per ogni tabella presente nel database, informazioni statistiche sull’allocazione dei dati delle tabelle all’interno del database e sulla forma ed allocazione degli indici impostati su ogni tabella.
Dato che non si tratta di funzioni che si utilizzano quotidianamente, ho aperto i Books Online di SQL Server, per rilevare la sintassi con cui chiamare il comando ed ho trovato la segnalazione del fatto che questo comando è obsoleto e potrebbe essere eliminato nelle future versioni di SQL Server, di utilizzare invece sys.dm_db_index_physical_stats. Trovandomi a lavorare su SQL 2000 non ho preso in considerazione la cosa, riservandomi di provare quanto prima questo nuovo oggetto.
Ho invece cercato sul web per verificare quali sono i parametri da osservare per capire se il database è lento ed ho trovato il necessario in un articolo, che ho poi utilizzato assieme a quanto ricavato dai Books online, come spunto per quanto vi racconterò nel resto dell’articolo.
Struttura dei dati di SQL Server
Il metodo in cui i dati sono memorizzati in SQL Server, è rimasto praticamente immutato dalla sua nascita, posso presumere che questa scelta sia dovuta al fatto che è ancora il metodo più efficiente.
Vi illustro brevemente qual’è questo metodo, consigliandovi di consultare i Books Online di SQL Server se volete approfondire il soggetto.
All’interno di un database SQL Server, i contenuti sono memorizzati utilizzando la seguente struttura:
Il database è composto da Extents, ognuno dei quali è grande 64KB, ogni Extent, contiene 8 pagine dati di 8KB ciascuna, ogni pagina contiene uno o più record appartenenti ad una delle tabelle del database.
In SQL Server 2000 questo limitava la massima dimensione di un record a 8KB, e per i dati di tipo Lob (Large object quali: immagini, testi molto grandi, dati binari di altro genere) c’erano degli appositi spazi che permettevano di inserire oggetti con dimensioni che arrivavano a 2GB, e sulla tabella che li ospitava c’era esclusivamente una cella che ne conteneva l’ “indirizzo”, ovvero i dati necessari ad andarlo a recuperare all’interno dell’area Lob.
In SQL Server 2005, questo limite non esiste più, anche se comunque la struttura base è rimasta la stessa, i dati di dimensioni superiori a 8KB, sono automaticamente gestiti dai meccanismi interni di SQL Server probabilmente, presumo, in modo simile a quello dei Lob ma del tutto trasparente a noi utenti.
Per inciso, chiunque lavori con i dati, penso possa concordare con me che sono molto pochi e ben precisi i casi in cui una tabella necessita di spazio superiore agli 8KB per un singolo record.
Perché un database diviene lento
Saputo come SQL Server memorizza i dati, vediamo perché un database a lungo andare diviene lento; e lo vediamo osservando come i dati vengono memorizzati.
Essenzialmente, i dati vengono memorizzati sequenzialmente, il motore database genera le tabelle secondo l’indice Clustered imposto a ciascuna di esse (Assumo sappiate cos’è un indice clustered). Se una tabella non possiede un indice Clustered, viene memorizzata in sequenza di inserimento in quello che viene definito uno Heap.
In una stessa pagina, come nella figura qui sopra, possono prendere posto più tabelle, in base allo spazio disponibile. La Database Engine, quando io decidessi di inserire un nuovo record nella tabella 2 la cui posizione secondo l’indice clustered stesse fra il record 2 ed il record 3 della tabella stessa, sposterebbe i record successivi facendo spazio. Se la pagina non avesse spazio sufficiente ad accogliere il nuovo record, la Database Engine spezzerebbe in 2 il contenuto della pagina, spostandone la metà su una diversa pagina o addirittura su un diverso Extent. Allocando spazio per il nuovo record da inserire.
Questo significa, che a lungo andare, le tabelle saranno spezzettate su più pagine, e le pagine sparse su più di un extent, pertanto, per accedere ai dati quando viene effettuata una query, la Database Engine deve leggere più extent diversi, estrarre le pagine interessate e da queste comporre i dati.
DBCC SHOWCONTIG, (contig sta per contiguity=contiguità dei dati), serve a mostrarci per una o più tabelle di un database, qual’è lo stato delle stesse all’interno del supporto fisico, quindi il file su disco ove è memorizzato il database.
Dean Thompson, nell’articolo da cui sono partita per i miei test, utilizza una query per recuperare le 10 tabelle con il maggior numero di record dal database, e visto che in SQL si può anche costruire stringhe, compone con questa query la query che chiamerà per verificare lo stato delle tabelle. Vediamo come:
SELECT TOP 10
'DBCC SHOWCONTIG(' + CAST(id AS NVARCHAR(20)) + ')'
+ CHAR(10) +
'PRINT '' ''' + CHAR(10)
FROM
sysindexes
WHERE
indid = 1 or
indid = 0
ORDER BY rows DESC
Il risultato di questa query, lanciato su un mio database è il seguente:
DBCC SHOWCONTIG(1798349521) PRINT ' ' DBCC SHOWCONTIG(1029682816) PRINT ' ' DBCC SHOWCONTIG(628249343) PRINT ' ' DBCC SHOWCONTIG(2082158513) PRINT ' ' DBCC SHOWCONTIG(596249229) PRINT ' ' DBCC SHOWCONTIG(1845685723) PRINT ' ' DBCC SHOWCONTIG(2005686293) PRINT ' ' DBCC SHOWCONTIG(786205951) PRINT ' ' DBCC SHOWCONTIG(1576392685) PRINT ' ' DBCC SHOWCONTIG(2108586600) PRINT ' '
Ovvero una serie di query che compongono la sintassi di DBCC SHOWCONTIG recuperando dalla tabella di sistema sysindexes, l’ID delle 10 tabelle con il numero di rows più alto.
modificando il codice della riga seguente:
'PRINT '' ''' + CHAR(10) + '-- N.righe: ' + convert(varchar(10), rows)
Otteniamo:
DBCC SHOWCONTIG(1798349521) PRINT ' ' -- N.righe: 259691 DBCC SHOWCONTIG(1029682816) PRINT ' ' -- N.righe: 46423 DBCC SHOWCONTIG(628249343) PRINT ' ' -- N.righe: 45280
La sintassi da noi utilizzata per DBCC SHOWCONTIG, è quindi la seguente
DBCC SHOWCONTIG (object_id) PRINT ' '
Ove object_id è l’ID univoco della tabella nel database e PRINT indica che vogliamo che il risultato della query sia stampato come testo sulla finestra Messages del query analyzer o del SQL Management Studio.
Cosa restituisce DBCC SHOWCONTIG
DBCC SHOWCONTIG scanning 'CRONUS Italia S_p_A_$99000757$0' table... Table: 'CRONUS Italia S_p_A_$99000757$0' (1209771367); index ID: 1, database ID: 24 TABLE level scan performed. - Pages Scanned................................: 314 - Extents Scanned..............................: 42 - Extent Switches..............................: 130 - Avg. Pages per Extent........................: 7.5 - Scan Density [Best Count:Actual Count].......: 30.53% [40:131] - Logical Scan Fragmentation ..................: 99.68% - Extent Scan Fragmentation ...................: 78.57% - Avg. Bytes Free per Page.....................: 206.8 - Avg. Page Density (full).....................: 97.45% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
L’output all’esecuzione della query per una delle tabelle è quello qui sopra riportato, e i valori che ci fornisce sono i seguenti:
- Pages Scanned: quante pagine ha letto.
- Extents Scanned: quanti extents ha letto.
- Extent Switches: Quante volte ha dovuto cambiare extent per leggere i record nell’ordine in cui devono essere scorsi, come vediamo è molto più alto del numero di extent che ha letto.
- Avg. Pages per extent: Quante pagine sono contenute in ogni extent, sono 7,5 e non 8 perché come abbiamo visto è possibile occupare una pagina con più di una tabella.
- Scan Density: Densità di scansione [Best Count: Conteggio migliore, Actual Count: Conteggio reale] ci dice quante scansioni avrebbe dovuto fare per leggere l’indice e quante ne ha dovute fare nella realtà. In questo caso, ci sono 42 extent occupati, quindi l’ideale sarebbe stato che facesse 42 letture, invece ne ha fatte 130 quindi è dovuto uscire e rientrare più volte dagli extent.
- Logical Scan Fragmentation: Percentuale di frammentazione logica, per gli indici clustered l’ideale sarebbe 0% per gli altri indici non dovrebbe essere presa in esame.
- Extent Scan Fragmentation: Frammentazione di scansione degli extent, come la precedente ha senso per gli indici clustered e dovrebbe tendere allo 0%.
- Avg. Bytes Free per Page: Numero medio di byte liberi per pagina, un valore basso ci indica che le pagine sono tutte piene, quindi un nuovo inserimento frammenterà ulteriormente le pagine.
- Avg. Page density (full): Densità media di occupazione dello spazio delle pagine.
Il parametro principale da considerare nel valutare la frammentazione è quindi la Scan Density, che più si avvicina a 100% meglio è, utilizzando il suo valore come determinante, potremmo decidere quali sono le tabelle che hanno bisogno di una riorganizzazione degli indici all’interno di un database.
Come sys.dm_db_index_phisical_stats sostituisce DBCC SHOWCONTIG
Innanzitutto, dopo aver aperto i books online sulla pagina relativa a questo nuovo strumento, sono andata a cercare di cosa si tratta, osservando all’interno degli oggetti del database l’ho trovata:
Come possiamo vedere osservando l’immagine, si tratta di una Table-Valued Function situata sul Database Master del server. Vediamo di capire come utilizzarla e cosa otterremo dopo averla chiamata:
sys.dm_db_index_physical_stats (
{ database_id | NULL }
, { object_id | NULL }
, { index_id | NULL | 0 }
, { partition_number | NULL }
, { mode | NULL | DEFAULT }
)
La sintassi di base del comando è quella sopra scritto, pertanto la nostra funzione, richiede di indicare l’ID di un database o NULL, l’ID di un oggetto o NULL, lID di un indice o NULL, il numero di una partizione o NULL e una modalità di esecuzione oppure NULL o DEFAULT. Il primo test effettuato è stato il seguente:
select * from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL)
Il risultato è stato una lista contenente i dati relativi agli indici di tutte le tabelle di tutti i database del mio computer, interessante ma dispersivo.
Il secondo test, lo facciamo assegnando un ID Database al primo parametro, l’ID di AdventureWorks, il DB di Test di SQL Server 2005, otteniamo un numero di righe limitato, agli indici delle tabelle del database.
select * from sys.dm_db_index_physical_stats(6,NULL,NULL,NULL,NULL)
Essendo una table valued Function, il risultato della sua esecuzione è una tabella, al suo interno una serie di campi, li descrivaiamo per stabilire in che modo essi sostituiscono il risultato dello SHOWCONTIG:
| Nome | Tipo | Descrizione |
|---|---|---|
|
database_id |
smallint |
ID Univoco del database che contiene l’oggetto esaminato |
|
object_id |
int |
ID Dell’oggetto esaminato |
|
index_id |
int |
ID dell’ indice esaminato 0 = Heap (Tabella priva di indice clustered) |
|
partition_number |
int |
Numero di partizione, parte da 1 e sale, se un indice non è partizionato oppure è l’indice fisico di una tabella senza indice clustered (Heap), il suo valore è 1. |
|
index_type_desc |
nvarchar(60) |
Descrizione del tipo di indice:
|
|
alloc_unit_type_desc |
nvarchar(60) |
Descrizione del tipo di unità di allocazione:
L’unità di allocazione LOB_DATA, contiene i dati che sono memorizzati in campi di tipo text, ntext, image, varchar(max), nvarchar(max), varbinary(max) ed xml. L’unità di allocazione ROW_OVERFLOW_DATA contiene i dati che sono memorizzati in colonne di tipo varchar(n), nvarchar(n), varbinary(n) e sql_variant che sono state spostate fuori da una riga (i dati eccedenti gli 8KB). |
|
index_depth |
tinyint |
Numero di livelli di indicizzazione. Vale 1 per le unità di allocazione di tipo: Heap, LOB_DATA, ROW_OVERFLOW_DATA. |
|
index_level |
tinyint |
Livello corrente dell’indice: Vale 0 per gli indici a livello di foglia, e le unità di allocazione di ti po Heap, LOB_DATA, ROW_OVERFLOW_DATA. E’ maggiore di zero per i livelli di indici diversi da quello di foglia. index_level conterrà il valore più alto dell’indice tra root e foglia. I livelli di indice superiori a quelli del livello foglia, sono processati solo quando la funzione è chiamata con la modalità DETAILED. |
|
avg_fragmentation_in_percent |
float |
Frammentazione logica degli indici, o frammentazione degli extent per gli Heaps nelle unità di allocazione di tipo IN_ROW_DATA . Il valore è una percentuale e prende in considerazione file multipli. |
|
fragment_count |
bigint |
Numero di frammenti a livello di foglia di una unità di allocazione di tipo IN_ROW_DATA. |
|
avg_fragment_size_in_pages |
float |
Numero medio di pagine per frammento a livello di foglia di una unità di allocazione di tipo IN_ROW_DATA. Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit. LOB_DATA e ROW_OVERFLOW_DATA. NULL per le unità Heap se la modalità è SAMPLED |
|
page_count |
bigint |
Per un indice, numero totale di pagine di indice o dati per un indice nel livello corrente del b-tree relativo all’unità di allocazione di tipo IN_ROW_DATA. |
|
avg_page_space_used_in_percent |
float |
Percentuale media di spazio allocato dai dati in tutte le pagine. Vale NULL se la modalità è LIMITED. |
|
record_count |
bigint |
Numero totale di records. Per un indice è riferito al livello corrente del b-tree per le unità di allocazione di tipo IN_ROW_DATA. Per lo Heap è il numero di record delle unità di allocazione di tipo IN_ROW_DATA. Per LOB_DATA e ROW_OVERFLOW_DATA il numero totale di record nell’unità di allocazione. Vale NULL se la modalità è LIMITED. |
|
ghost_record_count |
bigint |
Numero di record fantasma pronti ad essere rimossi dal task “Ghost cleanup”, nell’unità di allocazione. 0 Per i livelli non foglie di un indice nelle unità di allocazione di tipo IN_ROW_DATA. Vale NULL se la modalità è LIMITED. |
|
version_ghost_record_count |
bigint |
Numero di record fantasma ritenuti da una transazione con livello di isolamento snpshot non risolta all’interno di una unità di allocazione. 0 per i livelli non foglia di un indice nelle unità di allocazione IN_ROW_DATA. Vale NULL se la modalità è LIMITED. |
|
min_record_size_in_bytes |
int |
Dimensione minima del record in bytes. Per un indice, la dimensione minima del record si applica al livello corrente del b-tree nelle unità di allocazione di tipo IN_ROW_DATA. Per uno Heap è la dimensione minima del record nelle unità di allocazione di tipo IN_ROW_DATA. Per le unità di allocazione LOB_DATA e ROW_OVERFLOW_DATA, la dimensione minima di un record in tutta l’unità di allocazione. Vale NULL se la modalità è LIMITED. |
|
max_record_size_in_bytes |
int |
Dimensione massima del record in bytes. Segue i valori della precedente al contrario. |
|
avg_record_size_in_bytes |
float |
Dimensione media del record in bytes. Segue i valori della minima e della massima. |
|
forwarded_record_count |
bigint |
Numero di record inoltrati in uno Heap. NULL per tutti i tipi di unità di allocazione salvo gli IN_ROW_DATA, NULL per tutte le unità di allocazione se la modalità è LIMITED. [Forwarded Records] Questi record “Inoltrati” sono quelli che vengono spostati in altra pagina od extent perché non c’è posto per allocarli nella pagina corrente, ci da un valore che ha una correlazione con gli ExtentSwitches del DBCC SHOWCONTIG |
Osservazioni
Leggendo le descrizioni relative ai campi della tabella fornita dalla funzione, possiamo osservare che, mentre i primi 4 parametri della funzione sono semplicemente dei filtri, l’ultimo (MODE) ha effetto sul tipo di risultati. I valori che può assumere sono i seguenti:
- DEFAULT, NULL, LIMITED (equivalenti)
- SAMPLED
- DETAILED
Ovviamente l’uso di uno o dell’altro dipende da quanti dati dobbiamo esaminare, Default, può andare bene per un controllo di base, SAMPLED, che esclude alcuni dati va bene per stabilire delle priorità di controllo. DETAILED quando si esamina a fondo una tabella.
Verificando sui books online che cosa è opportuno annotare, abbiamo i seguenti indicatori per scoprire quanto un indice sia frammentato:
- avg_fragmentation_in_percent
- forwarded_record_count
La percentuale media di frammentazione dovrebbe tendere a zero perché un indice sia efficiente. Il numero di record “Inoltrati” allo stesso modo dovrebbe essere basso o tendente a zero, per evitare che sia necessario scandire più pagine su più extent per ottenere i dati ricercati. Per fare questo, il metodo più semplice è far divenire clustered l’indice più usato, in questo modo, l’ordinamento fisico di una tabella è imposto da questo indice e su questo indice non ci saranno mai scansioni multiple.
Qual’è il vantaggio di questa nuova funzione rispetto al vecchio DBCC SHOWCONTIG, sicuramente il fatto di non aver bisogno di fare una query per richiedere i dati relativi alle tabelle con il maggior numero di record, la possibilità di fare dei filtri, la possibilità di leggere solo i valori che ci servono, la possibilità di ottenere i dati su una tabella che possiamo utilizzare da una applicazione esterna, fatta in qualsiasi tipo di linguaggio, possiamo anche utilizzarla come sorgente dati per un report su Reporting Services. Mi sembrano tutti vantaggi che giocano a favore di questa nuova function al posto dello SHOWCONTIG.
Conclusioni
I due strumenti di cui abbiamo scoperto le funzionalità, ci possono fornire il necessario per creare dei piani di manutenzione che tengano in efficienza gli indici dei nostri database.
Per riorganizzare gli indici di un Database Frammentato, ciò che si utilizzava su SQL Server 2000, e si può ancora utilizzare, è DBCC DBREINDEX oppure DBCC INDEXDEFRAG. Ma anche queste due istruzioni sono definite obsolete per SQL Server 2005 e devono essere sostituite con ALTER INDEX REBUILD e ALTER INDEX REORGANIZE, andremo a curiosare questi comandi specifici in un prossimo articolo.
Nota estemporanea:
Il problema presso il mio cliente era in minima parte dovuto alla frammentazione degli indici, il problema più grave era invece dovuto al fatto che un consulente informatico esterno, autorizzato dal titolare, aveva eliminato delle relazioni sul database per poter cancellare dei dati altrimenti non cancellabili. In questo modo, il database era rimasto inconsistente e il gestionale andava in tilt in modalità casuale perché non trovava le relazioni oppure si trovava dei dati sbagliati nelle query. Ergo, quando esaminate un problema su un database, non date nulla per scontato, soprattutto la possibilità di manomissioni, solitamente fatte senza pensare alle conseguenze, più che per volontà di provocare danni.
Traduzioni materiale bibliografico di base a cura di Patrizia Cosolo.
Bibliografia: Comprendere l’uso di DBCC SHOWCONTIG, di Dean Thompson; MSDN Books Online sys.dm_db_index_physical_stats; Microsoft SQL ISV Program; MSDN Books Online DBCC SHOWCONTIG;