La frammentazione di un indice rappresenta l'uso inefficiente delle pagine dell'indice stesso. La frammentazione avviene nel tempo, dopo la modifica dei dati. Ad esempio, quando vengono aggiunte o cancellate righe di una tabella oppure quando viene modificato un valore in un campo indicizzato, SQL Server "aggiusta" le pagine dell'indice per mantenere indicizzati i dati memorizzati. Gli aggiustamenti che avvengono nelle pagine dell'indice sono noti come page split. Il processo di divisione (splitting process) incrementa la dimensione della tabella e aumenta il tempo necessario a processare le query.
Tipi di Frammentazione
Esistono due tipi di frammentazione: la frammentazione esterna e la frammentazione interna.
|
Tipo di frammentazione |
Descrizione |
|
Interna |
L'uso inefficiente delle pagine dell'indice avviene perché la dimensione totale dei dati memorizzati all'interno di ogni pagina è minore della dimensione della pagina stessa.
La frammentazione interna oltre ad aumentare le letture logiche e fisiche di I/O, richiede più memoria per il trasferimento delle righe in cache. Queste letture extra contribuiscono al degrado delle performance nell'esecuzione delle query.
Questo tipo di frammentazione beneficia gli inserimenti intensivi di righe perché SQL Server dovrà eseguire meno cambiamenti con conseguente riduzione di page splitting. |
|
Esterna |
L'uso inefficiente delle pagine dell'indice avviene perché l'ordinamento logico delle pagine non è rispettato.
La frammentazione esterna produce un rallentamento nell'accesso ai dati del disco, le tecniche di read-head applicate non portano benefici |
Come verificare la frammentazione
Per rilevare la frammentazione degli indici si può utilizzare SQL Server Management Studio oppure la DMV sys.dm_db_index_physical_stats.
Utilizzando la dynamic management view (DMV) sys.dm_db_index_physical_stats è possibile determinare la frammentazione di un indice specifico, di tutti gli indici di una tabella o di una vista, di tutti gli indici di un database oppure di tutti gli indici di tutti i database presenti nell'istanza.
Alla funzione sys.dm_db_index_physical_stats è possibile passare i seguenti parametri:
-
ID del database
- ID della tabella
- ID dell'indice
- Numero della partizione da valutare
- Modalità di scansione utilizzata per la statistica di frammentazione
Nel result set della funzione, il campo avg_fragmentation_in_percent riveste particolare importanza, esso infatti rappresenta la quota di frammentazione dell'indice in percentuale. Più alto è questo valore, più l'indice risulterà essere frammentato.
L'esempio riportato di seguito illustra come di può determinare la quota di frammentazione in percentuale per tutti gli indici del database AdventureWorks.
SELECT
a.index_id,
b.name index_name,
avg_fragmentation_in_percent,
CASE WHEN (avg_fragmentation_in_percent <= 30)
THEN 'ALTER INDEX '+LTRIM(RTRIM(b.name))+' ON '+LTRIM(RTRIM(c.name))+' REORGANIZE'
ELSE 'ALTER INDEX '+LTRIM(RTRIM(b.name))+' ON '+LTRIM(RTRIM(c.name))+' REBUILD'
END AS action_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks'),NULL,NULL,NULL,NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
JOIN sys.objects AS c ON c.object_id = a.object_id
WHERE (b.name IS NOT NULL)
ORDER BY avg_fragmentation_in_percent DESC
.JPG)
Deframmentazione dell'indice
Ci sono due opzioni per deframmentare un indice: REORGANIZING e REBUILDING.
L'opzione REORGANIZING produce un riordinamento fisico dei livelli (leaf-level pages) che compongono la struttura ad albero relativa all'indice. Poter disporre di pagine ordinate significa poter aumentare le performance di scansione dell'indice. La riorganizzazione produce tipicamente una compattazione delle pagine dell'indice, le pagine vuote verranno rimosse per rilasciare risorse disco aumentando così lo spazio disponibile.
L'opzione REBUILDING produce la cancellazione (drop) dell'indice a la creazione di un indice nuovo. In questo modo la frammentazione viene rimossa, vengono rilasciate le risorse disco, il nuovo indice viene memorizzato in pagine contigue (allocazione di nuove pagine). Questa situazione incrementa le performance legate alla lettura su disco in quanto riduce il numero di pagine lette per ottenere i dati richiesti (read-head).
REORGANIZING o REBUILDING ?
La decisione di applicare una riorganizzazione piuttosto di una ricostruzione è funzione del livello di frammentazione dell’indice:
- Con avg_fragmentation_in_percent <= 30% si può applicare l’opzione REORGANIZE
ALTER INDEX XMLVALUE_Individual_Demographics ON Individual REORGANIZE
- Con avg_fragmentation_in_percent > 30% si può applicare l’opzione REBUILD
ALTER INDEX IX_vProductAndDescription ON vProductAndDescription REBUILD
Qualora la quota di frammentazione in percentuale sia minore del 30% e riorganizzando (REORGANIZE) l’indice non si ottenga un incremento di prestazioni, si potrà provare con la ricostruzione (REBUILD).