SQL Q&ALog delle transazioni di grandi dimensioni, utilizzo della funzione Repair e molto altro ancora

Paul S. Randal

D Ho riscontrato un'anomalia nei backup che spero tu possa spiegarmi. Di tanto in tanto, eseguiamo il backup del nostro database di produzione da 62 GB per aggiornare i dati utilizzati dagli sviluppatori di applicazioni. Eliminiamo sempre la vecchia copia prima di ripristinare la nuova. La copia ripristinata ha le stesse dimensioni del database di produzione e i dati sono uguali, ma il processo di ripristino dura molto più a lungo rispetto a quello di backup. Quale può essere la causa? Perché è necessario più tempo per eseguire il ripristino rispetto al backup?

R In realtà non sta accadendo niente di strano: a seconda delle circostanze, si tratta in generale di un comportamento previsto. La differenza tra il tempo necessario per il backup e quello necessario per il ripristino deriva dai passaggi che ogni processo deve eseguire.

Il processo di backup di database prevede due passaggi: in sostanza, si leggono gli I/O sul database, che poi vengono scritti sulla periferica di backup.

Passaggio 1: leggere tutti i dati allocati nei file di dati e scriverli sulla periferica di backup.

Passaggio 2: leggere parte del log delle transazioni e scriverla sulle periferiche di backup.

La quantità esatta di log delle transazioni necessaria può variare notevolmente, ma è tale quantità che permette il ripristino del database in un momento preciso (una spiegazione più approfondita è disponibile nel post del blog disponibile all'indirizzo sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx).

Il ripristino del database, invece, può comportare fino a quattro passaggi e le attività necessarie sono più complesse rispetto al semplice processo di lettura e scrittura di I/O.

Passaggio 1: se i file del database non esistono, crearli.

Passaggio 2: leggere tutti i dati e il log delle transazioni dal backup e scriverli nei relativi file del database.

Passaggio 3: eseguire la fase di rollforward del ripristino sul log delle transazioni.

Passaggio 4: eseguire la fase di rollback del ripristino sul log delle transazioni.

Il tempo totale necessario a eseguire entrambi i passaggi del processo di backup è approssimativamente lo stesso tempo necessario per il passaggio 2 del processo di ripristino (presupponendo che l'hardware sia simile e il server non stia eseguendo alcuna attività degli utenti). Il passaggio 1 del processo di ripristino può richiedere molto tempo se i file di dati sono di grandi dimensioni e devono essere inizializzati a zero (comportamento tipico in SQL Server 2000 e predefinito in SQL Server 2005).

Per evitare che questo accada, non eliminare i file esistenti prima di avviare il ripristino oppure, in alternativa, attivare l'inizializzazione immediata, in modo che i file vengano creati rapidamente (ulteriori informazioni sono disponibili all'indirizzo msdn.microsoft.com/­library/ms175935.aspx).

Nei passaggi fasi 3 e 4 viene eseguito il ripristino sul database già ripristinato per renderlo coerente a livello di transazione: si tratta dello stesso processo a cui viene sottoposto il database durante il ripristino in caso di arresto anomalo. La durata del ripristino dipende dalla quantità di log delle transazioni che deve essere elaborata. Se al momento del backup, ad esempio, è attiva una transazione a esecuzione prolungata, tutto il log della transazione in questione sarà inserito nel backup e dovrà essere sottoposto a rollback.

D Sono indeciso tra la distribuzione dei log e il mirroring del database per ottenere una copia ridondante del nostro database di produzione. Mi preoccupa la quantità di log delle transazioni che dovranno essere scambiati tra i server, soprattutto per le operazioni di ricostruzione degli indici che vengono effettuate ogni notte. Ho sentito che il mirroring invia i comandi correnti di ricostruzione anziché il log delle transazioni e le ricostruzioni vengono effettuate sul mirror. È vero? Questo renderebbe il mirroring una soluzione più conveniente anche rispetto alla distribuzione dei log con il modello di recupero con registrazione minima delle transazioni di massa (BULK_LOGGED), giusto?

R Ciò che hai sentito non è vero. Il mirroring del database funziona inviando i record correnti del log delle transazioni dal database principale al server mirror, dove vengono "riprodotti" nel database mirror. Non viene effettuata alcuna conversione o filtraggio, né alcun tipo di intercettazione di comandi T-SQL per il database sottoposto a mirroring.

Il mirroring del database supporta solo il modello di recupero con registrazione completa (FULL), quindi l'operazione di ricostruzione di indici verrà sempre registrata completamente. In base alla dimensione degli indici in questione, può essere generata una quantità significativa di log delle transazioni e, di conseguenza, un file di registro di grandi dimensioni sul database principale, con la conseguente occupazione di una notevole larghezza di banda per l'invio dei record del log al mirror.

Il mirroring del database può essere considerato come la distribuzione dei log in tempo reale (questo era, infatti, il nome utilizzato per la funzionalità durante le prime fasi di sviluppo di SQL Server 2005). Nella distribuzione dei log, i backup del log delle transazioni del database primario vengono regolarmente distribuiti al server secondario e ripristinati sul database secondario.

La distribuzione dei log supporta il modello di recupero con registrazione completa (FULL) e con registrazione minima delle transazioni di massa (BULK_LOGGED). Per un'operazione di ricostruzione di indici nel database sottoposto a distribuzione del log attraverso il modello di recupero con registrazione completa (FULL), verrà generata la stessa quantità di log delle transazioni di un database sottoposto a mirroring. Tuttavia, nel caso di un database sottoposto a distribuzione del log, i dati vengono inviati al database ridondante in un backup del registro (o una serie di backup del registro) e non sotto forma di flusso continuo.

Se il modello di recupero con registrazione minima delle transazioni di massa (BULK_LOGGED) viene utilizzato nel database sottoposto a distribuzione del log durante la ricostruzione degli indici, sarà generata solo una quantità minima di log delle transazioni. Tuttavia, il successivo backup del log delle transazioni conterrà anche tutti gli extent dei file di dati modificati dall'operazione, sottoposta a registrazione minima, di ricostruzione degli indici. Quindi, i backup di log che comprendono la ricostruzione degli indici nel modello di recupero con registrazione minima delle transazioni di massa (BULK_LOGGED) avranno quasi le stesse dimensioni di quelli che prevedono la ricostruzione nel modello di recupero con registrazione completa (FULL).

Dunque la quantità di informazioni che deve essere inviata al database ridondante per la ricostruzione di un indice è quasi la stessa, sia nel caso di un database sottoposto a mirroring che di uno sottoposto a distribuzione dei log. Le due soluzioni differiscono per le modalità di invio delle informazioni: in modo continuativo o in batch.

Nella scelta tra questi due approcci, si devono considerare molti altri fattori (e sono così tanti da non poter essere discussi in un unico numero di SQL Q&A). Prima di prendere una decisione, si devono valutare tali fattori in base alle proprie esigenze (come il limite accettabile di perdita di dati e i tempi di inattività ammissibili).

D Utilizzo SQL Server 2005 e il log delle transazioni di uno dei database continua ad aumentare di dimensioni. Il database è impostato sulla modalità di ripristino completa ed eseguo i backup del log delle transazioni. Credevo che questa operazione avrebbe evitato l'aumento delle dimensioni del log. Cos'è che non funziona in questo processo?

R Hai ragione a considerare fondamentale l'esecuzione di backup del log delle transazioni in modalità di ripristino completa. Tuttavia, esistono altri fattori che possono contribuire all'aumento di dimensioni del log delle transazioni. Tutto dipende da ciò che il log delle transazioni richiede come necessario (o attivo). Altre probabili cause del problema, oltre alla mancanza di backup del log delle transazioni, includono replica, mirroring del database e la presenza di una transazione attiva.

La replica opera in modo asincrono, leggendo i record del log delle transazioni e caricando quindi le transazioni da replicare in un database di distribuzione separato. I record del log delle transazioni non ancora letti dal programma di lettura del log di replica non possono essere liberati. Se il carico di lavoro genera molti record del log delle transazioni ed è stato impostato un ampio intervallo di lettura del log di replica, i record si accumulano e causano l'aumento di dimensioni del log delle transazioni.

Se si esegue il mirroring asincrono del database, può verificarsi un backlog dei record del log delle transazioni non inviati dal server principale al server mirror (la cosiddetta coda di invio del mirroring del database). I record del log delle transazioni non possono essere liberati finché non vengono inviati correttamente. Con un elevato tasso di generazione di record di log delle transazioni e una larghezza di banda limitata (o altri problemi di hardware), il backlog si ingrandisce e causa l'aumento di dimensioni del log delle transazioni.

Infine, se l'utente avvia una transazione esplicita (ad esempio, utilizzando l'istruzione BEGIN TRAN) e, in seguito, esegue modifiche di qualsiasi tipo (ad esempio, l'istruzione DDL o un'operazione di inserimento/aggiornamento/eliminazione), i record del log delle transazioni generati devono essere conservati finché l'utente non esegue il commit o il rollback della transazione. Neppure i successivi record del log delle transazioni generati da altre transazioni possono essere liberati, poiché il log non può essere liberato selettivamente. Se un utente, ad esempio, non completa la transazione, il log delle transazioni continuerà a ingrandirsi man mano che nuovi record vengono generati senza poter essere liberati.

Per capire il motivo per cui il log delle transazioni non può essere liberato, è sufficiente eseguire una query nella vista del catalogo di sistema sys.database ed esaminare la colonna log_reuse_wait_desc, come nell'esempio seguente:

SELECT name AS [Database], 
  log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;

Se la causa è una transazione attiva, si può utilizzare l'istruzione DBCC OPENTRAN per ottenere ulteriori informazioni sulla transazione:

DBCC OPENTRAN ('dbname')

D Ho sentito dire che REPAIR_ALLOW_DATA_LOSS deve essere utilizzato solo come ultima risorsa per il ripristino in caso di danneggiamento, poiché è preferibile l'utilizzo di un backup. Per quale motivo il comando "repair per SQL Server 2005" non deve essere utilizzato e perché viene inserito nel prodotto nonostante sia così "pericoloso"?

R Innanzitutto, sono stato io a scrivere il comando repair per SQL Server 2005. Il problema di REPAIR_ALLOW_DATA_LOSS (che d'ora in poi chiamerò semplicemente Repair) è che il suo funzionamento non è chiaro. Il nome dell'opzione è stato scelto per sottolineare che l'esecuzione può causare la perdita di dati del database. La funzionalità di solito ripristina una struttura danneggiata del database eliminandola e, in seguito, correggendo tutto ciò che nel database si riferiva ad essa. Repair deve essere considerato l'ultima risorsa per rendere il database coerente dal punto di vista strutturale e non deve essere inteso come un mezzo per il salvataggio dei dati degli utenti. Il comando non eccede nell'eliminazione di dati, ma neppure nel salvataggio.

Può sembrare un modo irresponsabile di risolvere i problemi, ma quando è necessario eseguire un ripristino, si rivela il metodo più veloce e affidabile per riparare i danni. La rapidità è di fondamentale importanza in caso di ripristino di emergenza e la correttezza è assolutamente necessaria. È quasi impossibile progettare algoritmi di ripristino più complessi che funzionino rapidamente e correttamente in tutti i casi. Nel codice di ripristino esistono alcuni algoritmi complessi per la soluzione del caso in cui due indici abbiano la stessa pagina o lo stesso extent allocati, ma principalmente l'algoritmo è di ripristino e correzione.

Tuttavia, è opportuno tenere presenti alcuni aspetti del comando Repair.

• Quando elimina le strutture danneggiate, Repair non considera i vincoli delle chiavi esterne, dunque potrebbe eliminare record da una tabella con una relazione di chiave esterna con un'altra tabella. È possibile determinare se si è verificato questo caso solo eseguendo il comando DBCC CHECK­CONSTRAINTS dopo il ripristino.
• Repair non considera (e non può considerare) alcuna logica aziendale intrinseca, né relazioni tra dati definite a livello di applicazione che possono venir meno in seguito all'eliminazione di dati. Anche in questa eventualità, determinare quanto accaduto è possibile solo eseguendo una qualsiasi verifica della coerenza personalizzata incorporata nell'applicazione.
• Alcune operazioni di ripristino non possono essere replicate. Quando si esegue il comando Repair su un server di pubblicazione o su un nodo di una topologia peer-to-peer, possono verificarsi incongruenze all'interno della topologia, che dovranno essere corrette manualmente.

Per queste ragioni, in caso di danneggiamento è sempre consigliabile utilizzare un backup piuttosto che eseguire il comando Repair. Tuttavia, il comando è disponibile nel prodotto per l'eventualità in cui il database sia danneggiato, non esista un backup e sia necessario rimettere in linea il database rapidamente.

D Ho iniziato da poco a lavorare in una nuova azienda come amministratore di database e mi è stata assegnata la responsabilità di alcune applicazioni e dei relativi database di back-end. Le prestazioni di una delle applicazioni sono scadenti durante gli aggiornamenti. Approfondendo la questione, ho scoperto che ognuna delle tabelle utilizzate dall'applicazione ha un numero enorme di indici. Dopo aver chiesto informazioni, ho appreso che il precedente amministratore di database aveva l'abitudine di aggiungere un indice a ogni colonna della tabella, più alcune combinazioni. Non credo che siano necessari tutti quegli indici: come posso sapere quali eliminare senza correre rischi? In azienda si utilizza SQL Server 2005.

R Effettivamente, l'elevato numero di indici influisce in modo significativo sul calo delle prestazioni. A ogni inserimento, aggiornamento o eliminazione di una riga nella tabella, le operazioni corrispondenti devono essere effettuate in ogni indice non raggruppato. In questo modo si aggiunge un overhead in termini di I/O, utilizzo della CPU e generazione di log delle transazioni.

In SQL Server 2000 si poteva determinare quali indici fossero in uso solo mediante il profiling e l'esame dei piani di query. In SQL Server 2005 è disponibile una nuova vista a gestione dinamica (DMV) che registra l'utilizzo degli indici: sys.dm_db_index_usage_stats.

Questa DMV registra ogni utilizzo di ciascun indice (e la modalità con cui viene utilizzato) da quando il database di cui fa parte è stato creato. Le statistiche per tutti i database vanno perse quando si chiude SQL Server, mentre quelle relative a un singolo database sono eliminate quando quest'ultimo viene chiuso o scollegato. L'idea di base è che, se non viene visualizzato nell'output, un indice non è stato utilizzato da quando il database è stato avviato.

Un semplice approccio alla registrazione dell'utilizzo degli indici nel tempo consiste nell'eseguire periodicamente snapshot dell'output della DMV per poi confrontarli. Un elemento di cui molti non tengono conto è la necessità di tenere traccia dell'utilizzo degli indici sull'intero ciclo aziendale. Se si eseguisse uno snapshot per un unico giorno, ad esempio, si potrebbe notare che molti indici non vengono utilizzati. Ma se vengono utilizzati, ad esempio per velocizzare l'esecuzione dei report di fine mese, questi indici probabilmente non devono essere rimossi. Se un indice non è stato utilizzato per un intero ciclo aziendale, invece, probabilmente è possibile eliminarlo, recuperando spazio e ottimizzando le prestazioni.

Per ottenere del semplice codice da utilizzare per eseguire snapshot periodici della DMV, consulta il post sul mio blog all'indirizzo sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx.

Paul S. Randal è Managing Director di SQLskills.com e MVP per SQL Server. Paul ha scritto il comando DBCC CHECKDB/repair per SQL Server 2005 ed è stato responsabile di Core Storage Engine durante lo sviluppo di SQL Server 2008. Paul, che vanta una notevole esperienza nell'ambito del ripristino di emergenza, della disponibilità elevata e della gestione dei database, partecipa regolarmente a conferenze in qualità di relatore. Il suo blog è disponibile in SQLskills.com/blogs/paul.