SQL domande e risposte: l'antico motto dei registri

Articolo
08/24/2016

I registri delle transazioni sono un componente essenziale di qualsiasi istanza di SQL Server. Una gestione corretta ed efficiente di esse è altrettanto fondamentale.

S. Paul Randal

Crescita rapida

D. Finora ho correttamente utilizzato la ricostruzione degli indici in linea poiché è stato introdotto in SQL Server 2005. Inoltre Finora ho utilizzato il modello di recupero con registrazione di massa per ridurre la quantità di crescita del log delle transazioni durante la deframmentazione degli indici. Abbiamo aggiornato SQL Server 2008 di recente e a questo punto il log delle transazioni è in crescita più che mai. Può spiegare cosa sta succedendo?

**R.**Le operazioni di ricostruzione di indici precedenti a SQL Server 2005 sempre acquisiti blocchi per tutta la durata dell'operazione di ricostruzione. Ricostruzione di un indice cluster significava un blocco di tabella esclusivo (gli utenti concorrenti né i writer). Ricostruzione degli indici non cluster significava un blocco di tabella condiviso (nessun autore concorrente per la tabella).

Con l'avvento delle operazioni sugli indici in linea di SQL Server 2005 Enterprise Edition, il blocco è stato modificato per le operazioni in linea. Fermo per blocchi era solo detenuti per brevi periodi all'inizio e alla fine dell'operazione (vedere questo post di blog per una spiegazione).

In SQL Server 2005, tutte le operazioni di ricostruzione indice utilizzano la registrazione minima. Ciò significa che sono registrate solo le allocazioni di pagina, invece di tutti gli inserimenti di riga in corrispondenza dell'indice di nuovo. Ciò riduce drasticamente la quantità di record del log delle transazioni generati dall'operazione di ricostruzione dell'indice. Ciò significa inoltre che la transazione di file di registro stesso non deve necessariamente avere le stesse dimensioni (in quanto non deve necessariamente contenere un'operazione di ricostruzione dell'indice registrazione completa). Questo comportamento si verifica solo quando si utilizzano i modelli di recupero di massa o semplice. Tutte le operazioni vengono registrate nel modello di recupero con registrazione completa, pertanto il nome.

Da SQL Server 2008 poi operazioni sugli indici in linea sono stati modificati per essere registrate in tutti i modelli di recupero. In questo modo l'operazione di ripristino di database che comprende un'operazione di indici in linea nel relativo file di registro delle transazioni non rilevi altri problemi. (Per informazioni più dettagliate, vedere la documentazione Microsoft l'articolo KB 2407439.)

Se limitare la crescita del log delle transazioni è più importante che consentano l'accesso alle tabelle concurrent durante la ricostruzione degli indici, Purtroppo dovrai tornare ricostruzioni indice non in linea per ottenere tale comportamento di registrazione minima. Un altro aspetto da considerare è utilizzando ALTER INDEX … REORGANIZE per rimuovere la frammentazione. Ciò funziona senza provocare il blocco e sempre possibile ridurre la quantità di log delle transazioni. (Non vi è un confronto tra la ricostruzione e riorganizzazione in una delle seguenti risposte).

Il Backup di massa

D. Ci abbiamo impiegato per progettare una strategia di backup che coincide con il nostro processo di importazione dei dati durante la notte in modo che i nostri backup completo contiene tutti i dati dal processo di importazione. Non siamo stati in grado di scoprire un modo affidabile per effettuare questa operazione, in alcuni casi i dati sono tutti i presenti e, talvolta, nessuno ne è presente. È possibile avere un aiuto?

**R.**È possibile affermare che, "in alcuni casi i dati sono tutti i presenti e talvolta che nessuno ne è presente." Ciò mi rende ritiene che si sta eseguendo il processo di importazione come un'unica transazione di grandi dimensioni.

Se si tratta di operazioni eseguite, è possibile dividerlo in transazioni più piccole. Ciò fornirà, SQL di blocco più potenziali per la cancellazione del log delle transazioni tra le transazioni di importazione e meno a lungo termine. Un processo a esecuzione prolungata, singola transazione può causare l'escalation dei blocchi per un blocco di tabella esclusivo. Potrebbe non essere in grado di modificare il processo di importazione, ma è consigliabile farlo se possibile.

Un backup completo ha due fasi: la lettura dei dati e la lettura del log delle transazioni. Quando essa è completamente la lettura dei dati, quindi lettura del log delle transazioni da che punto precedente, per quanto è necessario (vedere il mio articolo di luglio 2009, "copie di backup di comprensione SQL Server," per ulteriori dettagli).

Quando si ripristina un backup completo, il punto di ripristino del database ossia il tempo in cui è stata completata la porzione di lettura dei dati del backup. La transazione log inclusi nel backup essenzialmente viene eseguito il ripristino sul database ripristinato. In questo modo consistente mediante transazioni.

È possibile eseguire il rollback delle transazioni corso in quel momento, proprio come un normale ripristino. Se il processo di importazione è stata ancora in esecuzione la porzione di lettura dei dati completata, essa verrebbe ripristinata durante l'operazione di ripristino.

È l'unico modo per garantire una particolare transazione contenuti all'interno di un backup completo sia per serializzare le operazioni in modo che al completamento della transazione in questione prima del backup inizia. In caso contrario, non è possibile sapere se la transazione viene completata prima che venga completata la lettura dei dati.

Naturalmente vi è un'alternativa alle operazioni di serializzazione. Eseguire un backup del log delle transazioni al termine del processo di importazione. Quindi si ripristina il backup completo e il backup del log delle transazioni. Conterrà la transazione del processo di importazione.

Per ricostruire o riorganizzare

D. Sta progettando una strategia di manutenzione indice. Non è chiara per questo motivo sono disponibili due metodi per rimuovere la frammentazione degli indici. Come è possibile determinare se si desidera ricreare i nostri indici o riorganizzarli? È Impossibile trovare un elenco dei compromessi tra i due metodi.

**R.**Sono stati i due metodi per la rimozione della frammentazione, dal momento che ho scritto DBCC INDEXDEFRAG per SQL Server 2000. I due metodi sono necessari perché hanno caratteristiche molto diverse. Purtroppo, non esiste alcun white paper adeguatamente vengono descritte le differenze tra i tre metodi poi in SQL Server 2005: ALTER INDEX … RIORGANIZZA (il nuovo DBCC INDEXDEFRAG), ALTER INDEX … RICOSTRUZIONE (nuovo DBCC DBREINDEX) e la versione online di ALTER INDEX … LA RICOSTRUZIONE.

Ecco un rapido confronto delle opzioni ALTER INDEX REBUILD e RIORGANIZZA:

RICOSTRUZIONE, è necessario creare il nuovo indice prima di eliminare quella vecchia. Ciò significa che deve esistere spazio sufficiente nel database per adattarsi al nuovo indice; in caso contrario il database aumenteranno per fornire lo spazio libero richiesto. Ciò può risultare problematica per gli indici di grandi dimensioni. RIORGANIZZA richiede solo 8 KB di spazio nel database.
RICOSTRUZIONE può utilizzare più CPU in modo che l'operazione viene eseguita più rapidamente. RIORGANIZZA è sempre a thread singolo.
RICOSTRUZIONE può richiedere i blocchi a lungo termine sulla tabella che è possibile limitare le operazioni simultanee. RIORGANIZZA non tenere fermo per blocchi (è sempre un'operazione in linea).
RICOSTRUZIONE può utilizzare registrazione minima per ridurre la crescita del log delle transazioni. RIORGANIZZA viene sempre registrato completamente, ma non impedisce la cancellazione del log delle transazioni.
RICOSTRUZIONE verrà rigenerato automaticamente a tutte le statistiche di colonna di indice, che RIORGANIZZA non aggiorna le statistiche.

In questo modo, esistono diversi compromessi in termini di requisiti di spazio su disco, il blocco, registrazione e il parallelismo. La cosa più importante da considerare è algoritmica differenze tra le due operazioni.

Ricostruzione di un indice sempre causerà la ricostruzione dell'indice intero, indipendentemente dalla misura della frammentazione. Ciò significa che una ricostruzione dell'indice per un indice lightly frammentato davvero eccessivo. Riorganizza un indice solo si occuperà della frammentazione esistente. Ciò rende una scelta migliore per la rimozione della frammentazione da un indice lightly frammentato, ma una scelta sbagliata per la rimozione della frammentazione da un indice molto frammentato. In tal caso sarebbe meglio semplicemente la ricostruzione dell'indice.

Questo ci conduce le soglie diverse per la scelta tra ricostruzione e riorganizzazione. Sono disponibili le soglie di frammentazione ampiamente utilizzato in base alla colonna avg_fragmentation_in_percent nell'output di db_index_physical_stats:

0% al 10%: non eseguire alcuna operazione
10 al 30 percento: utilizzare l'istruzione ALTER INDEX … RIORGANIZZA
30 per cento e versioni successive: utilizzare l'istruzione ALTER INDEX … RICOSTRUZIONE

Si tratta di linee guida generali e che potrebbero risultare diversi valori funzionano meglio nel proprio ambiente. È inoltre necessario considerare se si sta utilizzando il mirroring del database, che richiede il modello di recupero con registrazione completa. Ciò significa che verranno registrate completamente le operazioni di ricostruzione di indici. Molte persone si trova che questo produce una quantità eccessiva del log delle transazioni da inviare in modo efficiente tra mirroring principale e mirror. In questi casi, può essere preferibile riorganizzare gli indici per ridurre al minimo il log delle transazioni.

È anche possibile considerare un altro utente script manutenzione indice per risparmiare tempo. OLA Hallengren dispone di alcuni script completo e diffuso.

Aumentando il gruppo di continuità e diminuendo dimensione del Registro

D. Ho appena diventare amministratore di database. Si è verificati dei problemi impostando alcuni nuovi database per i nostri sviluppatori. Il problema che è necessario capire di dimensioni del log delle transazioni dovrà essere. Indipendentemente dalla dimensione scelgo, esso aumenta e rimane in questo modo. Se riducendolo, si ancora estenda alle stesse dimensioni nuovamente. Esiste un modo per impostare correttamente le dimensioni durante la creazione del database?

**R.**Prima di tutto, non regolarmente compattare il log delle transazioni. Quando il log delle transazioni è necessario aumentare le dimensioni, il nuovo spazio allocato per il log delle transazioni deve essere inizializzata a zero. Ciò significa che le transazioni in attesa per generare i record del log potrebbero essere necessario attendere che l'inizializzazione viene eseguita.

Se si utilizza un shrink-grow, compattazione-aumento ciclo con il log delle transazioni, si sono causando sulle SQL Server prestazioni non necessarie. Si consiglia di lasciare il log delle transazioni con le dimensioni necessarie. Riduzione dovrebbe essere un'operazione di rara i registri dei dati o la transazione.

È possibile stimare la dimensione del log delle transazioni in base alle operazioni di database. Ulteriori informazioni che nel mio articolo di gennaio 2011. Se il log delle transazioni aumenta a una dimensione costante dopo ogni compattazione, che è probabile che la dimensione che è sempre consigliabile. Mantenete l'impostazione di questa dimensione, a meno che non è veramente troppo grande. Se è troppo grande, determinare qual è la causa della crescita e indica se è possibile dividere l'operazione in più parti in modo che il log delle transazioni ha la possibilità di cancellare.

Paul S. Randal

S. Paul Randalè managing director di SQLskills.com, un Microsoft regional director e MVP per SQL Server. Ha lavorato nel team SQL Server Storage Engine in Microsoft dal 1999 al 2007. Egli ha scritto l'istruzione DBCC CHECKDB/repair per SQL Server 2005 e si era responsabile di Core Storage Engine durante lo sviluppo del SQL Server 2008. Randal è un esperto di ripristino di emergenza, un'elevata disponibilità e la manutenzione di database ed è un normale relatore a conferenze in tutto il mondo. Un blog he al /blogs/paul, e che è possibile trovarlo nei movimenti in Twitter.com/PaulRandal.