Indici SQL reorganize o reindex?

Sin da quando ho cominciato a ravanare in SQL le prime volte mi sono fatto un pò di domande sulla manutenzione degli indici.
Nel mio caso, forse una delle prime cose che ho imparato su MSSQL è che i dati vengono scritti fisicamente sul disco seguendo l’indice clustered. Teoricamente infatti, l’indice clustered di una tabella andrebbe creato pensando per bene a come i dati vengono scritti nel DB, certo, non è l’unica considerazione da fare, ma di sicuro è la prima domanda da porsi..
prima domanda.. che succede se ho creato l’indice clustered sulla chiave primaria e un bel giorno mi rendo conto che i dati entrano nella tabella in tutt’altro ordine? e agli altri indici che succede? questi ultimi poi, al 99% sono stati pensati per velocizzare interrogazioni delle mie amate tabelle senza tenere minimamente conto di come il dato viene scritto sul disco..

Innanzitutto vediamo come verificare quanto i miei indici sono frammentati, il technet mette a disposizione una query che funziona su varie versioni di SQL (se non sbaglio dalla 2005 in poi) che, in caso il link si rompa copio e incollo qui sotto:


SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
AND a.index_id = b.index_id;

lanciandola (se avete dei tabelloni ci vorrà un pò di tempo la prima volta) avrete un risultato abbastanza interessante.


index_id name avg_fragmentation_in_percent
----------- ------------- --------------------------
1 PK_My_Table 28,8063656865314
2 Index01_MyTable 29,7625459873218
3 Index03_MyTable 0

(3 row(s) affected)

cosa capire da quanto qui sopra?

1) chiave primaria e indice Index01 sono piuttosto frammentati, si parla di circa il 30% per tutte e due
2) Index03 è frammentato Zero

Cosa Fare?

Secondo l’articolo del Technet che ho linkato, se la frammentazione si mantiene al di sotto del 30% è ottimale effettuare una reorganize degli indici, nel caso invece siamo fuori da questo parametro è molto più conveniente effettuare una rebuild.

che differenza c’è fra le due opzioni?

la Rebuild dell’indice effettua un DROP dell’indice ed una sua rigenerazione totale, è un’operazione che mette off-line la tabella, quindi se effettuata con operazioni schedulate è necessario tenere in cosiderazione che dall’inizio delle operazioni fino al loro termine la tabella sarà offline.

la Reorganize effettua una semplice riorganizzazione della tabella, la mantiene on-line, continuando a garantire la sua operatività anche durante le operazioni di manutenzione. l’accesso sarà un pò rallentato e, avrò necessità di un pò di spazio disco in quanto, per effettuare la rebuild, l’engine dovrà appoggiare da qualche parte il pezzetto di indice che sta riorganizzando, prima di scriverlo nel suo spazio definitivo.

quindi io che sono proprio al limite del valore indicato da Microsoft che faccio?

la tentazione per molti sarebbe quella di fare una reorganize e non pensarci più… La macchina lavora da sola, per quanto tempo ci possa mettere i miei utenti saranno solo rallentati ma potranno continuare a lavorare e tanti ecc ecc

ma è tutto oro quel che luccica?

non sempre..

anche in questo caso bisogna fare un pò di considerazioni considerando 3 parametri:

1) Dimensioni della tabella
2) potenza dell’hardware su cui gira la mia istanza
3) volume degli accessi alla tabella

mi spiego meglio, i 3 parametri di cui sopra (e qualche test) saranno fondamentali per capire quale opzione scegliere, due casi:

Ho una tabella di qualche milione di righe, su un hardware un pò datato, gli accessi non sono tanti, parliamo di una frammentazione di circa 15/20%.
a guardare i parametri e seguire le indicazioni di casa Microsoft verrebbe da scegliere la reorganize. Personalmente, una Rebuild la prenderei in considerazione, approfittando del basso numero di accessi e magari con la complicità di un fine settimana.

altro caso, ho una tabella di qualche migliaio di righe, frammentazione 50/60% diverse centinaia di accessi al secondo. In questo caso l’hardware è un parametro fondamentale, se parliamo di una macchina di ultimissima generazione, magari con la ram sufficente a contenere tutti i DB Dell’istanza, forse una rebuild si può prendere in considerazione. Se al contrario invece siamo in una situazione in cui l’hardware non è potentissimo, se lo spazio disco lo consenteè forse il caso di prendere in considerazione una Reorganize, in fondo la tabella è di dimensioni limitate e l’alto numero di accessi contemporanei forse pregiudica l’operatività di un numero di utenti troppo alto per giustificarne uno stop, anche di breve durata.

come possiamo fare dei test?

semplice.. mantenendo, o creando, un ambiente di test adeguatamente misurato.
Nell’azienda dove lavoro gira un ambiente di pre-produzione, dimensionato in modo da essere circa il 30% dell’ambiente di produzione. Questo ci permette di effettuare dei test consistenti che ci diano, facendo i dovuti conti, un’idea del lavoro che dovremo affrontare quando metteremo le mani nell’ambiente di produzione.
dove questo non sia possibile si può effettuare utilizzando un backup, su una macchina “muletto”.

In definitiva

laddove non ci siano situazioni chiare, la necessità è SEMPRE fare dei test prima, sopratutto tenendo a mente una cosa.. quanto ci metto a ripristinare i backup nel caso qualcosa non vada a buon fine?…

Annunci
Questa voce è stata pubblicata in Dove Stiamo Andando?. Contrassegna il permalink.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...