Manutenzione di SQL Server: come fare

La manutenzione periodica dei database è sicuramente tra le attività fondamentali richieste a un DataBase Administrator (DBA) che si rispetti. Innanzitutto, cerchiamo di definire il concetto di manutenzione di un database. Da un punto di vista strettamente fisico, il database è sinonimo di dati registrati su disco. È proprio questa componente meccanica (il disco appunto) che lo lega fortemente al concetto di manutenzione. Così, al pari di un automobile, di cui provvediamo a fare il tagliando ogni tot km per evitare una sosta forzata sulla corsia di emergenza in autostrada proprio il giorno di Ferragosto…, o della caldaia del gas, che sottoponiamo al controllo annuale prima che arrivi l'inverno e ci sorprenda senza riscaldamento, un database necessita di controlli e interventi periodici in grado di prevenire situazioni pericolose - oltre che imbarazzanti per l'IT Departement - che siano in grado di mantenerlo "giovane e pimpante" come il giorno della sua nascita in produzione.

Anche un database si "usura" col tempo: infatti (per citare alcuni tra i principali esempi) i suoi indici si frammentano se non ci si preoccupa di riorganizzali o ricostruirli, le sue statistiche diventano obsolete a meno che provvediamo ad aggiornarle, le sue pagine di dati possono corrompersi e, se non ne controlliamo periodicamente lo stato di consistenza, c'è il rischio di perdere definitivamente i dati in esso contenuti.

Un piano di manutenzione è fondamentale per una vita longeva di un database. Avete mai notato che il giorno in cui un database vede la luce in produzione sembra essere tutto perfetto, e man mano che il tempo passa cominciano a insorgere problemi di performance? Molte volte tali problemi sono legati a errori di progettazione del database (per esempio la scarsa normalizzazione delle tabelle), nel qual caso non c'è manutenzione che tenga, ma il più delle volte accade che il volume di dati crescente mette in crisi le attività di manutenzione periodiche esistenti (sempre che ce ne siano) e rendono necessaria una loro revisione. Da qui la necessità di manutenere il piano di manutenzione stesso, nel senso di rivederlo all'occorrenza per aggiustare il tiro durante tutto il ciclo di vita del database.

Questa mini-guida esplora l'argomento della manutenzione dei database analizzando i diversi task coinvolti in una manutenzione standard di un database MS-SQL Server 2005/2008. Gli esempi sono stati realizzati con la Enterprise Edition della versione SQL Server 2008. Ciascun task viene trattato principalmente dal punto di vista pratico, senza addentrarsi in complessi aspetti teorici per i quali si rimanderà a specifici articoli già pubblicati su HTML.it, mostrandone passo-passo l'implementazione con due metodi distinti:

• Quello standard messo a disposizione da Microsoft, ovvero il designer interno al Management Studio;
• Una soluzione free, basata sul linguaggio T-SQL, denominata SQL Server Maintenance Solution. Essa è stata realizzata nel 2008 da Ola Hallengren, un ormai noto DBA svedese che tuttora ne cura gli aggiornamenti.

Di fatto, si vedrà come Ola Hallengren sia giunto a un ottimo risultato, superando di gran lunga la soluzione standard, la quale si presta a scenari meno complessi perché, pur essendo dotata di una interfaccia grafica molto user-frendly, non ha lo stesso grado di flessibilità della soluzione T-SQL di Ola Hallengren.

Adesso una puntualizzazione necessaria: in un classico piano di manutenzione rientrano anche i backup dei database. Anche questo classico task sarà affrontato dal un punto di vista strettamente pratico, rimandando per quello teorico all'articolo Backup e restore di un database SQL Server 2005/2008.

Check integrity

Il check integrity serve a evidenziare la presenza di pagine di dati corrotte ed è , forse, il controllo più importante che il DBA è tenuto a svolgere quotidianamente.

Infatti, può accadere che una o più pagine di dati un data file, si corrompano dando vita a quella che viene chiamata Torn data page, ovvero pagina di dati corrotta. La ragione di questo problema - tutt'altro che improbabile - è la seguente: mentre SQL Server lavora con blocchi di dati di 8 KB (che è la dimensione di una pagina di dati appunto), Windows scrive su disco 512 Kb alla volta, per cui quando SQL Server deve scrivere su disco una pagina di dati ne spezza i suoi 8 Kb in 16 blocchi da 512 Kb, assumendo che la scrittura sia avvenuta con successo dopo che il primo dei 16 blocchi è stato correttamente scritto su disco. Quindi, se durante la scrittura di uno dei restanti blocchi si verifica un problema di disco o di controller oppure di alimentazione, la pagina di dati registrata su disco resta orfana di dati, quindi una "Torn data page".

Una pagina di dati corrotta è di fatto non accessibile, in altre parole i dati in esso contenuti sono definitivamente persi. Unica soluzione al problema è il ripristino dei dati dal backup più recente, ed in particolare, se si dispone della Enterprise Edition della versione SQL Server 2005 (o successive), il ripristino di una singola pagina di dati corrotta. Da qui l'importanza di una identificazione tempestiva del problema.

In pratica

Il check integrity dell'intero database viene fatto mediante il comando DBCC CHECKDB, il quale svolge un check di tutte le pagine di dati di cui è composto il database e produce un output che riporta gli eventuali errori riscontrati. L'output del CHECKDB viene anche registrato nell'errorlog di SQL Server e le eventuali pagine corrotte riscontrate vengono loggate in una tabella del database di sistema MSDB denominata dbo.suspect_pages.

Inoltre, è possibile eseguire un check integrity di una singola tabella con DBCC CHECKTABLE. Questo comando si presta molto bene ai cosiddetti Very Large DataBases (VLDBs), in quanto da la possibilità di svolgere un check parziale delle sole tabelle che vogliamo analizzare, anziché quello totale del CHECKDB che durerebbe tantissimo tempo col rischio di inficiare pesantemente le prestazione del database - infatti il check integrity è un'attività che richiede un notevole uso di CPU e di Disk I/O. Sempre in presenza di VLDBs si può ricorrere all'opzione PHYSICAL_ONLY del comando CHECKDB, con la quale viene eseguito il solo check a livello fisico, con conseguente risparmio di risorse.

Vediamo, quindi, come pianificare un check integrity con il designer del SQL Server Management Studio. Dopo aver aperto un nuovo Maintenance Plan e avergli dato un nome (per esempio 1st Maintenance Plan), scegliamo il Check Database Integrity Task e lo trasciniamo sul piano di lavoro

Passiamo alla configurazione del task. Entrando nelle proprietà del task possiamo decidere soltanto quali dei database sottoporre al CHECKDB. Non possiamo, quindi, decidere di eseguire un CHECKTABLE, né di usare l'opzione PHYSICAL_ONLY.

Database Backup

A questo argomento abbiamo dedicato in passato un lungo e completo articolo intitolato Backup e restore di un database SQL Server 2005/2008 a cui rimando per gli approfondimenti teorici. Adesso, vediamo come implementare un backup in un piano di manutenzione. Apriamo il nostro 1st Maintenance Plan e trasciniamo il Back Up Database Task, quindi lo leghiamo al check integrity task mediante il Completion Control Flow (quello di colore blu) per definire un ordine di esecuzione oltre che un controllo di flusso.

Questo si traduce nel fatto che sarà eseguito per primo il Check Integrity, quindi al suo completamento, e indipendentemente dal suo esito positivo o negativo, sarà eseguito il Backup. Invece, se avessimo scelto il Success o il Failure Control Flow, il task di Backup sarebbe stato eseguito solo, rispettivamente, in caso di successo oppure in caso di errore del task di Check integrity.

Vediamo adesso come possiamo configurare questo task. Possiamo scegliere:

• il Backup Type (Full, Differential, Transaction log)
• i Database coinvolti in questo backup che si sta configurando
• la destinazione del file di backup (su Disk o Tape)
• l'estensione (solitamente BAK per i backup di tipo full, DIFF per i differential, TRN per i transaction log)
• se verificare l'integrità del file di backup generato
• se generare un file di backup compresso (valido solo per la Enterprise Edition e la Developer)

Per quanto concerne il nome, possiamo stabilirne uno a nostra discrezione scegliendo l'opzione Back Up Databases accross one or more files, quindi scegliere di accodare ogni backup all'interno del file (opzione Append) o sovrascriverlo ogni volta (opzione Overwrite). In alternativa, possiamo lasciar fare a questo task scegliendo l'opzione Create a backup file for every database, che ad ogni esecuzione del piano genererà un nuovo file con un nome differente basato su questa sintassi: <database_name>_<year>_<month>_<day>_<hour><minutes><seconds>.

Quest'ultima opzione ci impone di prendere in considerazione l'eliminazione dei file di backup più vecchi, per evitare di riempire lo spazio a disposizione dedicato appunto ai backup. Per fare ciò utilizziamo il task Maintenance Cleanup Task, lo trasciniamo e lo leghiamo al task di Backup con un Success Control Flow in modo da eseguire l'eliminazione dei file più vecchi soltanto se il backup si completa correttamente, altrimenti rischiamo di perdere tutti i backup precedenti se non ci accorgiamo in tempo che il Backup task non si sta completando correttamente.

Anche per questo task, la configurazione è molto intuitiva: andiamo a definire il folder in cui andare a cancellare i backup considerati vecchi, la loro estensione e infine la retention che possiamo esprimere in ore, giorni, settimane e mesi.

Re-organize e rebuild degli indici

Anche a questo argomento abbiamo dedicato in passato un lungo e completo articolo intitolato Indici B+Tree di SQL Server 2005, a cui rimando per gli approfondimenti teorici. Ricordo soltanto che la Reorganize è un'operazione on-line, mentre la Rebuild lo è solo nella Enterprise Edition e nella Developer , sempre che venga scelta l'opzione ONLINE = ON. Per operazione on-line si intende un'attività che non necessità di un accesso esclusivo all'indice, che quindi può continuare ad essere acceduto durante tutta l'attività di manutenzione. Di fatto, è bene ricostruire un indice esclusivamente durante i momenti di minor carico di lavoro, e comunque e sempre in modalità ONLINE = ON, soprattutto se lo facciamo con un piano di manutenzione come quello che stiamo qui realizzando, il quale, come vedremo tra poco, lancia la ricostruzione degli indici di un database senza valutarne prima lo stato di frammentazione.

Adesso vediamo come implementare una riorganizzazione, e poi come implementare una rebuild degli indici in un piano di manutenzione. Apriamo lo stesso nostro 1st Maintenance Plan e trasciniamo il Reorganize Index Task legandolo al task di Backup con un Completion Data Flow:

Quindi entriamo nella finestra delle proprietà del task per configurarne:

• I database da coinvolgere nell'attività (Database(s))
• La tipologia di oggetti da riorganizzare (Object: Tables, Views, Tables and Views)
• Le tabelle e/o le viste specifiche (Selection: Specific objects)

Per la rebuild degli indici, trasciniamo il task Rebuild Index Task legandolo sempre al task di Backup con un Completion Data Flow.

Prima di passare a configurarlo, però, è doverosa una puntualizzazione: avere in uno stesso piano di manutenzione sia un task di Reorganize che uno di Rebuild ha senso solo se ciascuno è configurato in modo da operare su indici distinti, altrimenti sprecheremmo risorse inutilmente e perderemmo sicuramente tempo. Questo perché entrambi operano sugli indici di un database indistintamente, senza cioè verificare che il livello di frammentazione sia basso a tal punto da giustificarne una riorganizzazione piuttosto che una ricostruzione. Quindi, si rischia per esempio di eseguire una Rebuild di indici già perfettamente ottimizzati da una precedente operazione di Reorganize o viceversa.

Quello che il task di Rebuild ci consente di configurare sono:

• I database da coinvolgere nell'attività (Database(s))
• La tipologia di oggetti da riorganizzare (Object: Tables, Views, Tables and Views)
• Le tabelle e/o le viste specifiche (Selection: Specific objects)
• La percentuale di spazio libero da mantenere in ogni pagina di dato (Free space option)
• L'opzione di ordinamento nel database Tempdb (Sort results in tempdb)
• L'opzione di rebuild online (Keep index online while reindexing)

Circa la percentuale di spazio libero in ogni pagina di dato, trattasi dell'opzione fill factor, ma in senso opposto. In altre parole, mentre il comando T-SQL che realizza la ricostruzione consente di specificare la "percentuale di spazio occupato" mediante l'opzione FILLFACTOR (es. ALTER INDEX IX_My_Index ON My_Table REBUILD WITH (FILLFACTOR=90, ONLINE = ON) ), il task del maintenance plan ragiona al contrario, quindi in termini di percentuale di spazio libero.

Update delle statistiche

Per ogni indice di database SQL Server genera una statistica di distribuzione dei dati in esso contenuti. Questa statistica viene presa in considerazione dal Query Optimizer di SQL Server ogniqualvolta esso si trova a decidere se sfruttare o meno quel particolare indice a supporto di una query comandata al Database Engine. L'aggiornamento delle statistiche è un'operazione molto spesso trascurata dai DBA, ma di fatto importantissima in quanto avere sempre statistiche aggiornate significa garantire alle query le informazioni sufficienti perché SQL Server possa sfruttare al meglio gli indici presenti sul database, quindi assicurarsi un uso efficiente degli indici di database.

Si tenga presente, inoltre, che oltre alle statistiche sugli indici, SQL Server provvede a crearne anche sulle colonne non indicizzate che vengono usate nel predicato WHERE di una query. Di una statistica possiamo sapere la data del suo ultimo aggiornamento interrogando la funzione di sistema STATS_DATE( object_id, stats_id ) come nell'esempio seguente

SELECT name AS index_name,STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('NomeTabella')

Per l'aggiornamento delle statistiche scegliamo l'Update Statistics Task del Maintenance Plan.

Di questo task è possibile configurare:

• I database da coinvolgere nell'attività (Database(s))
• La tipologia di oggetti da aggiornare (Object: Tables, Views, Tables and Views)
• Le tabelle e/o le viste specifiche (Selection: Specific objects)
• Se aggiornare le statistiche degli indici oppure delle colonne o di entrambe
•  Se procedere a una scansione completa o mediante semplificazione (Scan Type)

L'aggiornamento delle statistiche è un'operazione di tipo online, ma come le operazioni di manutenzione degli indici è raccomandato eseguirla durante i momenti di minor carico di lavoro, soprattutto in presenza di VLDBs.

Come nel caso della Reorganize e della Rebuild, anche questo task opera sugli oggetti da noi scelti in fase di configurazione senza entrare nel merito della necessità effettiva di tale attività. Quindi, può verificarsi che questo task aggiorni le statistiche di un indice di una tabella non più utilizzata - quindi con dati immutati da tempo, di conseguenza statistiche invariate che non avrebbero affatto bisogno di essere aggiornate.

Una cosa importante da tenere a mente è che l'attività di Rebuild forza di default l'aggionamento delle statistiche dell'indice ricostruito. Quindi dopo aver ricostruito un indice, non è necessario aggiornarne le statistiche, mentre dopo averlo riorganizzato potrebbe essere utile farlo se questa risulta non aggiornata da molto tempo.