Quando come e perché ricostruire e riorganizzare gli indici di SQL Server

Lo scopo di un indice di SQL Server è abbastanza simile a quello del suo lontano parente – l’indice di un libro -; ti consente di arrivare rapidamente alle informazioni ma, invece di scorrere un libro, esso indicizza un database di SQL Server.

Gli indici di SQL Server sono creati a livello di colonna sia nelle tabelle che nelle viste. L’obiettivo è fornire un accesso rapido ai dati basandosi sui valori nelle colonne indicizzate. Se un indice è creato sulla chiave primaria, ogni volta che si effettua una ricerca per una riga basata sui valori della chiave primaria, SQL Server localizza il valore cercato nell’indice e poi lo usa per restituire l’intera riga di dati. Ciò significa che SQL Server non deve eseguire una lettura completa della tabella mentre cerca per una riga particolare, che sarebbe un’attività con un impatto maggiore sulle performance in termini di tempo e di risorse utilizzate.

Indici relazionali possono essere creati anche prima che ci siano dati in una tabella specifica o anche su tabelle e viste in un altro database.

CREATE INDEX MyIndex ON MyTable (Column1);

Maggiori dettagli sull’istruzione Transact-SQL CREATE INDEX possono essere trovati su MSDN.

Dopo che gli indici sono stati creati, essi saranno soggetti a manutenzione automatica da parte del motore di database di SQL Server, ogni volta che operazioni di insert, update o delete sono eseguite sui dati sottostanti.

Anche così, queste modifiche automatiche continueranno a sparpagliare le informazioni negli indici su tutto il database, aumentando la frammentazione nel tempo. Come risultato, gli indici avranno pagine dove l’ordinamento logico (basato sul valore chiave) differisce dall’ordine fisico interno ai file di dati. Ciò significa che c’è un’alta percentuale di spazio libero nelle pagine degli indici, e che SQL Server deve leggere un maggior numero di pagine ogni volta che scansiona ciascun indice. In più, l’ordinamento delle pagine che appartengono allo stesso indice viene alterato e questo aggiunge più lavoro a SQL Server quando si legge un indice, specialmente in termini di IO.

L’impatto della frammentazione degli indici su SQL Server può variare da un decremento dell’efficienza delle query – per server con un basso impatto sulle performance -, fino al punto in cui SQL Server termina totalmente di usare gli indici e ricorre alla soluzione più estrema, una scansione completa della tabella per ciascuna query. Come detto prima, la scansione completa di una tabella impatterà drasticamente sulle performance di SQL Server e questo è l’allarme finale per rimediare alla frammentazione degli indici in SQL Server.

La soluzione alla frammentazione è di ricostruire gli indici o di riorganizzarli. Ma prima di considerare la manutenzione degli indici è importante rispondere a due domande principali:

But, before considering maintenance of indexes, it is important to answer two main questions:

1. Qual è il grado di frammentazione?

2. Qual è l’azione corretta? Riorganizzare o ricostruire?

Rilevare la frammentazione

Generalmente, per risolvere qualsiasi problema, è essenziale prima di tutto individuare ed isolare l’area interessata prima di applicare la soluzione corretta.

La frammentazione può essere facilmente individuata eseguendo la funzione di sistema sys.dm_db_index_physical_stats la quale ritorna la dimensione e le informazioni sulla frammentazione per i dati e gli indici di tabelle e viste in SQL Server. La funzione può essere eseguita su un indice specifico in una tabella o vista, tutti gli indici in una specifica tabella o vista, o tutti gli indici in tutti i database:

Il risultato restituito dopo aver lanciato la procedura include le seguenti informazioni:

• avg_fragmentation_in_percent – la percentuale media di pagine non corrette nell’indice
• fragment_count – numero di frammenti nell’indice
• avg_fragment_size_in_pages – numero medio di pagine in un frammento di un indice

Analizzare i risultati della rilevazione

Dopo che è stata rilevata una frammentazione, il prossimo passo è determinare il suo impatto su SQL Server e se o quale azione intraprendere.

Non ci sono informazioni esatte sul valore minimo della frammentazione che può influenzare SQL Server in maniera specifica da avere effetto sulle performance specialmente poiché gli ambienti SQL Server variano notevolmente da un sistema all’altro.

Tuttavia, c’è una regola generalmente condivisa basata sulla percentuale di frammentazione (avg_fragmentation_in_percent column from the previously described sys.dm_db_index_physical_stats function)

• La frammentazione è meno del 10% – nessuna deframmentazione è richiesta. È generalmente accettato che nella maggioranza degli ambienti una frammentazione sotto al 10% è trascurabile ed il suo impatto sulle prestazioni di SQL Server è minimale.
• La frammentazione è tra il 10 ed il 30% – è consigliato eseguire una riorganizzazione degli indici
• La frammentazione è più alta del 30% – è consigliato eseguire una ricostruzione degli indici

Queste sono le motivazioni delle soglie indicate sopra, che ti aiuteranno a determinare se eseguire una ricostruzione o una riorganizzazione degli indici.

La riorganizzazione è un processo dove SQL Server scorre attraverso un indice esistente e lo pulisce. La ricostruzione è un processo più pesante dove l’indice è cancellato e poi ricreato da interamente da zero con una nuova struttura, libera da tutti i frammenti in pila e da pagine con spazi vuoti.

Mentre la riorganizzazione degli indici è un’operazione di pulizia che lascia lo stato del sistema com’è senza bloccare le tabelle e viste interessate, il processo di ricostruzione blocca le tabelle interessate per l’intera durata della ricostruzione, che potrebbe portare a lunghi tempi di attesa non accettabili in alcuni ambienti.

Tenendo a mente questo, è chiaro che la ricostruzione di un indice è un processo con una soluzione ‘più forte’, ma che richiede un prezzo da pagare – possibili lunghi blocchi sugli indici delle tabelle interessate.

Dall’altro lato, la riorganizzazione degli indici è un processo più leggero che risolve la frammentazione in un modo meno efficace – poiché un indice pulito sarà sempre secondo ad uno nuovo costruito da zero. Ma riorganizzare gli indici è molto meglio dal punto di vista dell’efficienza, perché le tabelle interessate dal processo non vengono bloccate durante l’operazione.

I server con un regolare piano di manutenzione (cioè manutenzione regolare durante i week end) dovrebbero sempre optare per la ricostruzione degli indici, a prescindere dalla percentuale di frammentazione, poiché questi ambienti risentirebbero pesantemente dei blocchi delle tabelle imposti dalla ricostruzione degli indici dovute a intervalli di manutenzione lunghi e regolari.

Come riorganizzare e ricostruire un indice:

Usando SQL Server Management Studio:

1. Nel pannello Object Explorer espandere il nodo Database
2. Espandere lo specifico database con l’indice frammentato
3. Espandere il nodo delle tabella e la tabella con l’indice frammentato
4. Espandere la specifica tabella
5. Espandere il nodo degli indici
6. Click con il destro sull’indice frammentato e selezionare l’opzione Rebuild or Reorganize nel menu contestuale ( a seconda dell’azione desiderata):

   

7. Cliccare il bottone OK ed aspettare che il processo sia completo

Riorganizzare gli indici in una tabella usando Transact-SQL

Fornire gli appropriati dettagli del database e della tabella ed eseguire il seguente codice in SQL Server Management Studio per riorganizzare tutti gli indici su una tabella specifica:

USE MyDatabase;
GO

ALTER INDEX ALL ON MyTable REORGANIZE;
GO

Ricostruire gli indici in una tabella usando Transact-SQL

Fornire gli appropriati dettagli del database e della tabella ed eseguire il seguente codice in SQL Server Management Studio per ricostruire tutti gli indici su una tabella specifica:

USE MyDatabase;
GO

ALTER INDEX ALL ON MyTable REBUILD;
GO