SQL Server

Suggerimenti per una manutenzione efficiente dei database

Paul S. Randal

 

Panoramica:

  • Gestione di file di registro delle transazioni e dati
  • Eliminazione della frammentazione dell'indice
  • Garanzia di statistiche accurate e aggiornate
  • Rilevamento di pagine di database danneggiate
  • Definizione di una strategia di backup efficace

Indice

Gestione di file di registro e dati
Frammentazione dell'indice
Statistiche
Rilevamento di danneggiamenti
Backup
Conclusioni

Spesso mi vengono chiesti suggerimenti su come gestire in modo efficiente un database di produzione. A volte, le domande sono poste da amministratori di database che stanno implementando nuove soluzioni e necessitano di

procedure di manutenzione dettagliate adatte per le caratteristiche dei nuovi database. Più spesso, tuttavia, le domande vengono poste da utenti che non sono amministratori di database professionisti, ai quali, per un motivo o per l'altro, è stata assegnata la proprietà e la gestione di un database. Mi piace definire questo ruolo "amministratore di database involontario". Lo scopo principale del presente articolo è fornire una panoramica sulle procedure consigliate di manutenzione dei database per tutti gli amministratori involontari.

Come per la maggior parte delle attività e delle procedure nel mondo dell'IT, anche per la manutenzione dei database non esiste un'unica soluzione valida in tutti i casi, ma esistono comunque alcune aree chiave di cui quasi sempre è necessario occuparsi. Le cinque principali aree di interesse che tratterò sono (l'ordine non corrisponde all'importanza):

  • Gestione di file di registro e dati
  • Frammentazione dell'indice
  • Statistiche
  • Rilevamento di danneggiamenti
  • Backup

La mancata manutenzione (o una manutenzione non accurata) dei database può generare problemi in una o più di queste aree, i quali, a loro volta, possono causare prestazioni inadeguate dell'applicazione o tempi di inattività e perdita di dati.

Nel presente articolo, chiarirò l'importanza di questi problemi e illustrerò semplici metodi per risolverli. Le spiegazioni saranno basate su SQL Server­® 2005, ma metterò in risalto anche le principali differenze presenti in SQL Server 2000 e nella versione imminente SQL Server 2008.

Gestione di file di registro e dati

La prima area che suggerisco sempre di verificare a chi gestisce un database comprende le impostazioni relative alla gestione di file di registro (delle transazioni) e dati. In particolare, verificare che:

  • I file di registro e di dati siano separati tra loro e isolati dagli altri file
  • L'aumento automatico sia configurato correttamente
  • L'inizializzazione file immediata sia configurata
  • La compattazione automatica non sia abilitata e la compattazione non faccia parte di alcun piano di manutenzione

Quando i file di registro e dati (che dovrebbero idealmente trovarsi su volumi separati) condividono un volume con altre applicazioni che creano o espandono file, si può verificare la frammentazione dei file. Nei file di dati una frammentazione eccessiva può contribuire a prestazioni insufficienti delle query (in particolare, quelle che eseguono la scansione di grandi quantità di dati). Nei file di registro la frammentazione può avere un impatto assai più significativo sulle prestazioni, in particolare se l'aumento automatico è impostato in modo da espandere le dimensioni di ciascun file di una piccola quantità ogni qual volta sia necessario.

I file di registro sono divisi internamente in sezioni denominate VLF (Virtual Log Files): maggiore è la frammentazione nel file di registro (uso il singolare perché avere più file di registro non comporta alcun vantaggio: deve esisterne solo uno per database), maggiore sarà il numero di VLF. Quando un file di registro, ad esempio, contiene più di 200 VLF, questo può influire negativamente sulle prestazioni di operazioni relative al registro, quali letture (ad esempio, per replica/ripristino di transazioni), backup e, in SQL Server 2000, anche trigger (in SQL Server 2005, per l'implementazione dei trigger il registro delle transazioni è stato sostituito da un framework di controllo delle versioni a livello di riga).

La procedura consigliata per il dimensionamento di file di registro e dati consiste nel crearli con una dimensione iniziale adeguata. Per i file di dati, la dimensione iniziale deve tenere in considerazione la possibilità di aggiungere dati ulteriori al database nel breve periodo. Ad esempio, se la dimensione iniziale dei dati è pari a 50 GB, ma si prevede che nei sei mesi successivi questa aumenterà di altri 50 GB, è opportuno creare sin dall'inizio un file di dati pari a 100 GB anziché doverlo espandere più volte fino a raggiungere tale dimensione.

Per i file di registro, purtroppo, l'operazione è un po' più complessa e occorre tenere in considerazione fattori quali la dimensione delle transazioni (le transazioni a esecuzione prolungata non possono essere eliminate dal registro fino al completamento) e la frequenza di backup del registro (poiché è da questa che dipende la rimozione della parte inattiva del registro). Per ulteriori informazioni, consultare "8 Steps to Better Transaction Log Throughput", un post del noto blog all'indirizzo SQLskills.com, scritto da mia moglie, Kimberly Tripp.

Una volta impostate le dimensioni dei file, è necessario monitorarle in diversi orari e aumentarle manualmente in modo proattivo a una determinata ora del giorno. Inoltre, è consigliabile mantenere l'aumento automatico attivo come ulteriore protezione, in modo che le dimensioni dei file possano comunque aumentare nel caso si verifichino anomalie. Non è consigliabile affidare completamente la gestione dei file all'aumento automatico, poiché tale operazione, se eseguita per piccole quantità, può portare alla frammentazione dei file e richiedere tempi lunghi, rischiando di bloccare il carico di lavoro dell'applicazione a orari imprevedibili.

Al fine di limitare il tempo e lo spazio necessari per eseguire l'aumento automatico, la dimensione di quest'ultimo deve essere impostata su un valore specifico anziché su un valore percentuale. Ad esempio, è possibile impostare un file di dati di 100 GB in modo che aumenti automaticamente di una dimensione fissa pari a 5 GB, anziché del 10%. Questo significa che il file aumenterà sempre di 5 GB, indipendentemente dalla dimensione finale, anziché di una quantità sempre maggiore (10 GB, 11 GB, 12 GB e così via) ogni volta che le dimensioni crescono.

Quando viene aumentato (sia manualmente che automaticamente), un registro delle transazioni è sempre inizializzato a zero. In SQL Server 2000 i file di dati hanno lo stesso comportamento predefinito, ma a partire da SQL Server 2005, è possibile attivare l'inizializzazione file immediata, che salta l'inizializzazione a zero rendendo quindi aumento e aumento automatico dei file quasi istantanei. Contrariamente a quanto si pensa, questa funzionalità è disponibile in tutte le edizioni di SQL Server. Per ulteriori informazioni, inserire "inizializzazione file immediata" nell'indice della documentazione in linea di SQL Server 2005 o SQL Server 2008.

Infine, accertarsi che la compattazione non sia attivata. La compattazione può essere utilizzata per ridurre la dimensione di un file di registro o dati, ma è un processo particolarmente intrusivo, che comporta l'utilizzo di grandi quantità di risorse, causando un'elevata frammentazione di scansione logica nei file di dati (per informazioni dettagliate, vedere di seguito) e un calo delle prestazioni. Ho modificato la voce relativa alla compattazione nella documentazione in linea di SQL Server 2005, aggiungendo un'avvertenza relativa a questo effetto. Tuttavia, in circostanze particolari, è possibile eseguire la compattazione manuale di singoli file di registro e dati.

La compattazione automatica è il problema principale, poiché si avvia in background ogni 30 minuti e tenta di compattare i database in cui l'opzione corrispondente è impostata su True. Si tratta di un processo imprevedibile, in quanto vengono compattati solo i database con più del 25% di spazio libero. La compattazione automatica utilizza una quantità notevole di risorse, causando una frammentazione che provoca un calo delle prestazioni, pertanto non è un processo consigliabile in alcuna circostanza. Disattivare la compattazione automatica con il seguente comando:

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

Un piano di manutenzione regolare che includa una compattazione manuale del database è altrettanto sconsigliabile. Se il database aumenta costantemente una volta eseguita la compattazione nel piano di manutenzione, significa che è necessario ulteriore spazio.

La soluzione ottimale consiste nel consentire al database di aumentare fino a una dimensione stabile ed evitare del tutto di eseguire la compattazione. Ulteriori informazioni sugli svantaggi dell'utilizzo della compattazione e alcuni commenti sui nuovi algoritmi in SQL Server 2005 sono disponibili sul mio precedente blog MSDN® all'indirizzo blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx.

Frammentazione dell'indice

Oltre che a livello di file system e all'interno del file di registro, è possibile che si verifichi una frammentazione anche all'interno dei file di dati, nelle strutture in cui sono memorizzati i dati di tabelle e indice. All'interno di un file di dati, possono verificarsi due tipi fondamentali di frammentazione:

  • Frammentazione all'interno di singole pagine di dati e indice (a volte denominata frammentazione interna)
  • Frammentazione all'interno di strutture di indice o tabelle costituite da pagine (denominate frammentazione di scansione logica e frammentazione di scansione extent)

La frammentazione interna si verifica quando è presente una grande quantità di spazio vuoto all'interno di una pagina. Come indicato in Figura 1, ogni pagina all'interno di un database ha una dimensione pari a 8 KB e un'intestazione di 96 byte. Di conseguenza, in una pagina possono essere memorizzati circa 8096 byte di dati di tabelle o indice (informazioni specifiche relative a tabelle e indici per strutture di dati e righe sono disponibili sul mio blog all'indirizzo sqlskills.com/blogs/paul, nella categoria Inside The Storage Engine). Gli spazi vuoti sono presenti quando la dimensione di ciascun record di indice o tabella supera la metà di quella della pagina: in tal caso, infatti, è possibile memorizzare un unico record per pagina. Questa situazione può risultare difficile o addirittura impossibile da correggere, poiché richiederebbe una modifica dello schema di tabelle o indice, ad esempio tramite la variazione di una chiave dell'indice al fine di evitare punti di inserimento casuali, come nel caso di un GUID.

fig01.gif

Figura 1 Struttura di una pagina di database (fare clic sull'immagine per ingrandirla)

Più spesso, la frammentazione interna è dovuta a modifiche di dati, quali inserimenti, aggiornamenti ed eliminazioni, che possono lasciare spazio vuoto in una pagina. Inoltre, anche una gestione inadeguata del fattore di riempimento può contribuire alla frammentazione. Per ulteriori informazioni, consultare la documentazione in linea. In base allo schema di tabelle/indice e alle caratteristiche dell'applicazione, una volta creatosi, lo spazio vuoto potrebbe non essere riutilizzato, causando un continuo aumento di spazio inutilizzabile nel database.

Si consideri, ad esempio, una tabella di 100 milioni di righe con record di dimensione media di 400 byte. Nel tempo, il modello di modifica dei dati dell'applicazione lascia di media 2800 byte di spazio vuoto in ogni pagina. Lo spazio totale necessario per la tabella è di circa 59 GB, risultato ottenuto con il seguente calcolo: 8096-2800 / 400 = 13 record per pagina da 8 KB, dividendo poi 100 milioni per 13 al fine di ottenere il numero di pagine. Se non si sprecasse spazio, ciascuna pagina potrebbe contenere 20 record, riducendo lo spazio totale necessario a 38 GB. Il risparmio è notevole.

Lo spazio sprecato nelle pagine di dati/indici, pertanto, può causare la necessità di un numero superiore di pagine per contenere la stessa quantità di dati. Questo non solo occupa più spazio su disco, ma significa anche che una query deve utilizzare più I/O per leggere la stessa quantità di dati. Inoltre, tutte le pagine aggiuntive occupano più spazio nella cache di dati, utilizzando quindi più memoria del server.

La frammentazione di scansione logica è causata da un'operazione denominata divisione di pagina, che si verifica quando un record deve essere inserito in una pagina di indice specifica (in base alla definizione della chiave dell'indice), ma nella pagina non è disponibile spazio sufficiente per l'inserimento dei dati. La pagina è divisa a metà e circa il 50% dei record viene trasferito su una nuova pagina riallocata. Tale pagina, in genere, non è fisicamente contigua alla precedente, pertanto è detta frammentata. Concettualmente, la frammentazione di scansione extent è simile. La frammentazione all'interno delle strutture di tabelle/indice influisce sulla capacità di SQL Server di eseguire scansioni efficienti, sia relative a una tabella/indice interi, sia limitata a una query con clausola WHERE (ad esempio, SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000).

Nella Figura 2 sono illustrate pagine di indice appena create con fattore di riempimento del 100% e prive di frammentazione (le pagine sono complete e il loro ordine fisico corrisponde a quello logico). Nella Figura 3 è illustrata la frammentazione che può verificarsi dopo inserimenti/aggiornamenti/eliminazioni casuali.

fig02.gif

Figura 2 Pagine di indice appena create prive di frammentazione: pagine complete al 100% (fare clic sull'immagine per ingrandirla)

fig03.gif

Figura 3 Pagine di indice con frammentazione interna e di scansione logica in seguito a inserimenti, aggiornamenti ed eliminazioni casuali (fare clic sull'immagine per ingrandirla)

A volte, è possibile evitare che la frammentazione modifichi lo schema di tabelle/indice ma, come affermato in precedenza, ciò potrebbe risultare complesso o addirittura impossibile. Quando non è possibile evitarla, esistono procedure per eliminare la frammentazione, in particolare mediante la ricostruzione e riorganizzazione degli indici.

Per ricostruire un indice, è necessario crearne una nuova copia, compattata in modo efficiente e il più contigua possibile ed eliminare la precedente versione frammentata. Poiché SQL Server crea una nuova copia dell'indice prima di eliminare la precedente, è necessario uno spazio libero nei file di dati che sia all'incirca equivalente alla dimensione dell'indice. In SQL Server 2000 la ricostruzione dell'indice era un'operazione non in linea. In SQL Server 2005 Enterprise Edition, tuttavia, è possibile eseguirla in linea, con alcune eccezioni. La riorganizzazione, invece, utilizza un algoritmo sul posto per compattare e deframmentare l'indice, richiede solo 8 KB di spazio aggiuntivo e viene sempre eseguita in linea. In effetti, in SQL Server 2000, ho scritto il codice per la riorganizzazione dell'indice affinché fosse una valida alternativa in linea alla ricostruzione e richiedesse poco spazio.

In SQL Server 2005 i comandi da utilizzare sono ALTER INDEX … REBUILD per la ricostruzione e ALTER INDEX … REORGANIZE per la riorganizzazione degli indici. La nuova sintassi sostituisce rispettivamente i comandi DBCC DBREINDEX e DBCC INDEXDEFRAG di SQL Server 2000.

Questi metodi comportano diversi compromessi, quali la quantità di registri di transazione generati, la quantità di spazio libero necessaria nel database e la possibilità di interrompere il processo senza perdere il lavoro. All'indirizzo microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx è disponibile un white paper in cui sono illustrati tali compromessi. Il documento è basato su SQL Server 2000, ma i concetti sono applicabili anche alle versioni successive.

Alcuni utenti si limitano a ricostruire o riorganizzare tutti gli indici ogni sera o ogni settimana (ad esempio, utilizzando l'opzione del piano di manutenzione) anziché determinare quali indici siano frammentati e se la rimozione della frammentazione comporterà effettivi vantaggi. Si tratta di una buona soluzione per gli amministratori di database involontari che desiderano semplicemente sistemare le cose con il minimo sforzo, ma può rivelarsi una scelta poco efficiente per database o sistemi di dimensioni maggiori in cui le risorse scarseggiano.

Un approccio più sofisticato comporta l'utilizzo della DMV sys.dm_db_index_physical_stats (o DBCC SHOWCONTIG in SQL Server 2000) per determinare periodicamente quali indici siano frammentati, scegliendo in seguito se e come operare su tali elementi. Nel white paper è inoltre descritto l'utilizzo di soluzioni mirate. Infine, nell'esempio D della voce nella documentazioni in linea relativa alla DMV sys.dm_db_index_physical_stats in SQL Server 2005 (msdn.microsoft.com/­library/ms188917) o nell'esempio E della voce relativa a DBCC SHOWCONTIG in SQL Server 2000 e versioni successive (msdn.microsoft.com/library/aa258803), sono disponibili alcuni codici esemplificativi per l'applicazioni dei filtri.

A prescindere dal metodo utilizzato, è consigliabile esaminare e risolvere la frammentazione regolarmente.

Il processore di query è la parte di SQL Server che decide la modalità di esecuzione di una query, in particolare le tabelle e gli indici da utilizzare, nonché le operazioni da eseguire per ottenere i risultati. Tale procedura è denominata piano di query. Alcuni degli input più importanti in questo processo decisionale sono statistiche che descrivono la distribuzione di valori dei dati per le colonne all'interno di una tabella o indice. Ovviamente, al fine di risultare utili per il processore di query, le statistiche devono essere accurate e aggiornate. In caso contrario, potrebbero causare prestazioni insufficienti dei piani di query.

Le statistiche sono generate mediante la lettura dei dati di tabelle/indice e la successiva determinazione della distribuzione dei dati per le colonne rilevanti. È possibile creare statistiche eseguendo la scansione di tutti i valori di dati per una determinata colonna (scansione completa), ma è anche possibile basarle su una percentuale di dati specificata dall'utente (scansione parziale). Se la distribuzione di valori in una colonna è uniforme, è sufficiente una scansione parziale, che rende la creazione e l'aggiornamento delle statistiche più rapidi rispetto alla scansione completa.

È possibile creare e gestire le statistiche in modo automatico attivando le opzioni database AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS, come indicato nella Figura 4. Per impostazione predefinita, tali opzioni sono attive, ma nel caso in cui si utilizzi un database per la prima volta, è consigliabile controllarle. Quando le statistiche diventano obsolete, è possibile aggiornarle manualmente utilizzando l'operazione UPDATE STATISTICS per specifici set di statistiche. In alternativa, è possibile utilizzare la stored procedure sp_updatestats, che consente di aggiornare le statistiche obsolete (in SQL Server 2000, sp_updatestats aggiorna tutte le statistiche, a prescindere dalla data).

fig04.gif

Figura 4 Modifica di impostazioni del database mediante SQL Server Management Studio (fare clic sull'immagine per ingrandirla)

Per inserire l'aggiornamento di statistiche nel piano di manutenzione regolare, è opportuno conoscere le seguenti informazioni. Sia UPDATE STATISTICS che sp_updatestats per impostazione predefinita utilizzano il livello di campionamento specificato in precedenza (se presente, potrebbe trattarsi di una quantità inferiore alla scansione completa). La ricostruzione dell'indice aggiorna automaticamente le statistiche con una scansione completa. Se le si aggiornano manualmente dopo la ricostruzione di un indice, è possibile che le statistiche risultino meno precise. Questo può verificarsi se una scansione parziale dell'aggiornamento manuale sovrascrive la scansione completa generata dalla ricostruzione dell'indice. D'altro canto, la riorganizzazione di un indice non aggiorna le statistiche.

Anche in questo caso, molti utenti dispongono di un piano di manutenzione che aggiorna tutte le statistiche in un determinato momento, prima o dopo la ricostruzione di tutti gli indici. Quindi, inconsapevolmente, ottengono statistiche meno precise. Se si opta semplicemente per la ricostruzione a intervalli regolari di tutti gli indici, tale operazione garantirà anche statistiche precise. Se si sceglie la procedura più complessa con la rimozione della frammentazione, sarà necessario seguire questa strada anche per la manutenzione delle statistiche. Ecco alcuni suggerimenti:

  • Analizzare gli indici e determinare su quali di essi operare e come procedere alla rimozione della frammentazione.
  • Per tutti gli indici che non vengono ricostruiti, aggiornare le statistiche.
  • Aggiornare le statistiche per tutte le colonne non indicizzate.

Per ulteriori informazioni sulle statistiche, consultare il white paper "Statistiche utilizzate da Query Optimizer® in SQL Server 2005" (microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx).

Rilevamento di danneggiamenti

Ho trattato la manutenzione in relazione alle prestazioni. Ora intendo illustrare il rilevamento e l'attenuazione di danneggiamenti.

È del tutto improbabile che un database contenga informazioni inutili che non interessano a nessuno. Quindi, com'è possibile accertarsi che i dati non vengano danneggiati e siano recuperabili in caso di emergenza? I dettagli relativi alla pianificazione di una strategia di disponibilità elevata e un ripristino di emergenza completo esulano dagli obiettivi dell'articolo. Tuttavia, per iniziare, è consigliabile conoscere alcune semplici informazioni.

La maggior parte dei danneggiamenti sono causati dall'"hardware". Perché l'ho scritto tra virgolette? Perché hardware, in questo caso, è un termine breve per indicare "qualcosa nel sottosistema di I/O su cui si basa SQL Server". Il sottosistema di I/O è costituito da elementi quali sistema operativo, driver del file system, driver di dispositivi, controller RAID, cavi, reti e le stesse unità disco. I punti in cui possono verificarsi (e di fatto si verificano) problemi sono molti.

Uno dei problemi più comuni è il verificarsi di interruzioni di alimentazione mentre un'unità disco sta scrivendo una pagina di database. Se l'unità non riesce a completare la scrittura prima dell'interruzione dell'alimentazione (oppure se le operazioni di scrittura sono memorizzate nella cache e la batteria di backup non è sufficiente per cancellare la cache dell'unità), questo potrebbe causare un'immagine di pagina incompleta sul disco. Il problema può verificarsi perché una pagina di database da 8 KB è in realtà costituita da 16 settori del disco contigui da 512 byte. Un'operazione di scrittura incompleta potrebbe aver causato la scrittura di alcuni settori della nuova pagina, tralasciando tuttavia alcuni settori della pagina precedente. Una situazione denominata pagina incompleta. Com'è possibile rilevare questo fenomeno?

SQL Server dispone di un meccanismo apposito, che consiste nella memorizzazione di una coppia di bit di ciascun settore della pagina e nella sostituzione di tali bit con un modello specifico (prima che la pagina venga scritta sul disco). Se quando la pagina viene riletta, il modello non è lo stesso, SQL Server rileva che la pagina è "incompleta" e segnala un errore.

In SQL Server 2005 e versioni successive è disponibile un meccanismo più completo, denominato checksum di pagina, che può rilevare i danneggiamenti presenti su una pagina. Questa procedura prevede un checksum della pagina intera prima che sia scritta, quindi una verifica quando viene riletta, come per il rilevamento delle pagine incomplete. Una volta abilitati i checksum di pagina, prima che venga protetta da tali meccanismi, la pagina dev'essere letta nel pool di buffer, modificata e riscritta sul disco.

Pertanto, è consigliabile attivare i checksum di pagina per SQL Server 2005 e versioni successive e abilitare il rilevamento di pagine incomplete per SQL Server 2000. Per attivare i checksum di pagina, utilizzare:

ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;

Per attivare il rilevamento di pagine incomplete per SQL Server 2000, utilizzare:

ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;

Questi meccanismi consentono di rilevare se una pagina è danneggiata, ma solo nel momento in cui questa viene letta. Com'è possibile fare in modo che tutte le pagine allocate vengano lette? Il metodo migliore per eseguire questa operazione (e identificare qualsiasi altro tipo di danneggiamento) è utilizzare il comando DBCC CHECKDB. A prescindere dalle opzioni specificate, il comando leggerà comunque tutte le pagine nel database, causando la verifica di checksum di pagina o rilevamenti di pagine incomplete. Inoltre, è consigliabile impostare degli avvisi, in modo tale da sapere quando gli utenti incontrano problemi di danneggiamento durante l'esecuzione di query. È possibile ricevere una notifica relativa a tutti i problemi descritti in precedenza utilizzando un avviso per gli errori con gravità 24 (Figura 5).

fig05.gif

Figura 5 Impostazione di un avviso per tutti gli errori con gravità 24 (fare clic sull'immagine per ingrandirla)

Quindi, un'altra procedura consigliata è eseguire regolarmente il comando DBCC CHECKDB sui database per verificarne l'integrità. Esistono molte varianti di questo comando, nonché domande sulla frequenza con cui eseguirlo. Purtroppo, non sono disponibili white paper relativi a questo argomento. Tuttavia, poiché DBCC CHECKDB era la parte di codice principale che ho scritto per SQL Server 2005, nel blog ne ho discusso ampiamente. Per articoli dettagliati sulle verifiche della coerenza, procedure consigliate e suggerimenti sulle procedure, consultare la categoria "CHECKDB From Every Angle" del mio blog (sqlskills.com/blogs/paul). Per gli amministratori di database involontari, la regola generale è eseguire il comando DBCC CHECKDB con la stessa frequenza con cui si effettuano backup del database completi (ulteriori informazioni al riguardo di seguito). Suggerisco di eseguire il seguente comando:

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, 
  ALL_ERRORMSGS;

Se in seguito all'esecuzione del comando vengono visualizzati output, DBCC ha identificato danneggiamenti nel database. In questo caso la domanda da porsi è: cosa fare quando DBCC CHECKDB identifica danneggiamenti? È qui che entrano in gioco i backup.

Quando si verificano danneggiamenti o altri malfunzionamenti, il metodo più efficace per risolvere la situazione è ripristinare il database dai backup. I presupposti sono, innanzitutto, disporre di backup e che tali backup non siano danneggiati. Molto spesso, gli utenti chiedono come sia possibile ripristinare un database danneggiato in assenza di backup. La risposta è semplice: non è possibile, non senza evitare perdite di dati che potrebbero creare problemi all'integrità di dati relazionali e logica aziendale.

Quindi, esistono ragioni più che sufficienti per eseguire backup regolari. Le complessità legate all'utilizzo di backup e ripristino esulano dagli obiettivi dell'articolo. Tuttavia, illustrerò brevemente i principi fondamentali per definire una strategia di backup.

Primo: eseguire regolarmente backup completi del database. Questo consentirà di disporre di una copia temporizzata con cui eseguire il ripristino. È possibile eseguire un backup completo del database utilizzando il comando BACKUP DATABASE. Per eventuali esempi, consultare la documentazione in linea. Per garantire ulteriore protezione, è possibile utilizzare l'opzione WITH CHECKSUM, che verifica i checksum (se presenti) delle pagine lette e calcola un checksum sull'intero backup. Selezionare una frequenza che corrisponde alla quantità di dati o lavoro che la propria azienda può permettersi di perdere. Ad esempio, se si esegue un backup completo del database una volta al giorno, in caso di emergenza, si perderà un volume di dati equivalente a un massimo di un giorno. Se si utilizzano solo backup completi del database, è consigliabile sfruttare il modello di recupero con REGISTRAZIONE MINIMA (comunemente denominato modalità di ripristino), al fine di evitare le complicazioni legate alla gestione dell'aumento del registro delle transazioni.

Secondo: conservare i backup per alcuni giorni, nel caso in cui uno venga danneggiato (disporre di un backup di qualche giorno prima è comunque meglio di non averne affatto). Inoltre, verificare l'integrità dei backup utilizzando il comando RESTORE WITH VERIFYONLY (consultare la documentazione in linea). Se durante la creazione del backup si è utilizzata l'opzione WITH CHECKSUM, eseguendo il comando di verifica sarà possibile controllare che il checksum del backup sia ancora valido, nonché ricontrollare tutti i checksum delle pagine all'interno del backup.

Terzo: se l'esecuzione di un backup completo al giorno non consente di rispettare la perdita massima di dati/lavoro che l'azienda può subire, è possibile considerare i backup differenziali. Un backup di database differenziale si basa su un backup completo e contiene un record di tutte le modifiche apportate dall'ultimo backup completo (in genere, si ritiene erroneamente che i backup differenziali siano incrementali: non lo sono). Ad esempio, è possibile utilizzare una strategia che preveda un backup di database completo al giorno e un backup differenziale ogni quattro ore. Il backup differenziale consente di disporre di un ripristino temporizzato supplementare. Se si utilizzano solo backup di database completi e differenziali, è comunque consigliabile sfruttare il modello di recupero con REGISTRAZIONE MINIMA.

Infine, l'ultima risorsa per il ripristino è rappresentata dall'uso di backup di registro. Questi sono disponibili solo nei modelli di recupero con REGISTRAZIONE COMPLETA (o REGISTRAZIONE MINIMA DELLE TRANSAZIONI DI MASSA) e consentono di eseguire il backup di tutti i record di registro generati dal backup di registro precedente. L'esecuzione di una serie di backup di registro con backup di database completi (e differenziali) periodici consente di avere un numero illimitato di backup temporizzati e aggiornati per il ripristino. Il compromesso necessario è che il registro delle transazioni continuerà ad aumentare, a meno che non sia "liberato" mediante un apposito backup. In questo caso, è possibile utilizzare una strategia che preveda un backup di database completo al giorno, un backup di database differenziale ogni quattro ore e un backup di registro ogni mezz'ora.

Decidere quale strategia di backup applicare e configurarla può risultare complesso. Come minimo, è consigliabile eseguire regolarmente backup di database completi al fine di avere almeno una copia temporizzata per il ripristino.

Come si può notare, per garantire che il database si mantenga in buone condizioni e sia sempre disponibile, è sufficiente eseguire alcune operazioni "obbligatorie". Di seguito è riportato un elenco di controllo finale per gli amministratori involontari che assumono il controllo di un database:

  • Eliminare l'eccessiva frammentazione dei file del registro delle transazioni.
  • Impostare correttamente l'aumento automatico.
  • Disattivare qualsiasi operazione di compattazione pianificata.
  • Attivare l'inizializzazione dei file immediata.
  • Definire una procedura regolare per rilevare e rimuovere la frammentazione dell'indice.
  • Attivare AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS definendo una procedura regolare per l'aggiornamento delle statistiche.
  • Attivare i checksum di pagina (o almeno il rilevamento delle pagine incomplete in SQL Server 2000).
  • Definire una procedura regolare per l'esecuzione di DBCC CHECKDB.
  • Definire una procedura regolare per l'esecuzione di backup di database completi e differenziali, nonché backup di registro per il ripristino temporizzato.

All'interno dell'articolo ho citato comandi T-SQL, ma anche Management Studio consente di svolgere diverse operazioni. Spero di aver fornito alcuni suggerimenti utili per una manutenzione efficiente dei database. Per commenti, suggerimenti o domande, scrivere all'indirizzo: paul@sqlskills.com.

Paul S. Randal è Managing Director di SQLskills.com e MVP per SQL Server. Ha lavorato nel team di SQL Server Storage Engine in Microsoft dal 1999 al 2007 ed è esperto in ripristino di emergenza, disponibilità elevata e manutenzione di database. Il suo blog è disponibile all'indirizzo SQLskills.com/blogs/paul.

© 2008 Microsoft Corporation e CMP Media, LLC. Tutti i diritti riservati. È vietata la riproduzione completa o parziale senza autorizzazione.