SQL Q & A: Gestione di log e indici

Articolo
08/24/2016

La conservazione dei backup di log e la gestione corretta degli indici sono essenziali per assicurare l'efficienza di SQL Server.

Paul s. Randal

Non interrompere la catena

D: Ho stato definendo una strategia di backup per i database. Il piano prevede i backup del log delle transazioni è possibile eseguire il ripristino di emergenza con perdita di dati minima. Ho stato esaminando alcuni dei problemi che potrebbe incontrare e che ho letto più volte è necessario prestare attenzione a non interrompere la sequenza di backup del log. È possibile spiegare questo è e come potrebbe essere interrotto?

R: È un'ottima domanda ed è qualcosa che molte persone trascurare. La catena di backup del log, denominata talvolta semplicemente la catena di log, si riferisce a una serie ininterrotta di backup del log delle transazioni che riguardano l'ora del backup dei dati più recenti (completo o differenziale) al punto in cui si desidera ripristinare. Una sequenza di ripristino esempio sarà il seguente:

Il backup completo del database più recente
Quindi il backup differenziale del database più recente
Quindi tutti i backup log delle transazioni eseguite dopo che

La maggior parte delle persone mantenere transazione più backup log intorno nel caso in cui uno dei backup danneggiato ed è necessario ripristinare un backup di dati meno recenti. È possibile ottenere ulteriori informazioni su backup e ripristino in due articoli di TechNet Magazine scritto l'anno scorso, “ di informazioni su SQL Server backup” e “ recupero da guasti Using Backups . ”

Se uno qualsiasi dei backup del log necessari sono danneggiato o non disponibile per la sequenza di ripristino scelto, la sequenza di backup del log verrà interrotto e sarà possibile ripristinare passato scegliere. Se solo uno dei backup del log è danneggiato, è possibile imporre il ripristino utilizzando l'opzione WITH CONTINUE_AFTER_ERROR. Che imporrebbe un ripristino del record di log delle transazioni danneggiato produrrebbe danneggiamento del database. Sarebbe molto esitare su imporre questo tipo di ripristino.

Un'operazione che potrebbe comportare un backup del log necessari non disponibile è un “ di banda ” backup del log, operazione non garantisce che un backup del log viene mantenuto. È possibile utilizzare questa opzione per fornire una copia di uno sviluppatore, ad esempio. Backup del log fa parte della catena di backup del log, perché è l'unico che conterrà i record di registro generati dopo l'ultimo backup del log.

Se non si utilizza l'opzione WITH COPY_ONLY, che esegue il backup del log, ma consente anche il backup del log successivo efficacemente backup lo stesso set di record di log. Vedere il blog post, “ BACKUP WITH COPY_ONLY, ” per visualizzare ulteriori dettagli su come evitare di interrompere la catena di backup .

Un esempio comune di più di un'operazione di interruzione della sequenza di backup del log è quello che impedisce l'esecuzione di un backup del log delle transazioni durante le normali operazioni. Questi tipi di operazioni:

Cambio di recupero con registrazione minima del modello e quindi nuovamente completa o BULK_LOGGED
Scaricamento log in SQL Server 2005 e versioni precedenti utilizzando il BACKUP LOG … WITH NO_LOG o TRUNCATE_ONLY opzioni
Ripristino di un database da uno snapshot di database

È necessario eseguire il backup dei dati (completo o differenziale) dopo queste operazioni per consentire il backup del log continuare. Si tratta di riavviare la sequenza di backup del log.

Un'ultima cosa: Contrariamente ai comuni myth eseguendo un backup completo o differenziale è ** interrompere la sequenza di backup del registro e, infatti, non ha alcun effetto su qualsiasi tipo di backup log.

Cluster degli indici

D: Molte delle tabelle nel database SQL Server 2008 Don ’t include un indice cluster. Ho sentito che potesse ho problemi di prestazioni di record inoltrati causando I/o aggiuntivo. È possibile stabilire automaticamente come è possibile verificare questo e cosa è possibile fare?

R: Un heap è una tabella che non dispone di un indice cluster. È intrinsecamente non ordinato. Per coloro che Don ’t conoscere record inoltrati nell'heap e come sono utilizzati, vedere il post del blog, “ inoltro e record inoltrati e la dimensione del puntatore back , ” per ulteriori dettagli. Record inoltrati in heap può portare a operazioni di I/O casuali supplementari durante l'elaborazione di query, che a sua volta comporta una riduzione delle prestazioni.

Il modo più semplice per controllare se vi sono query elaborano inoltrata record consiste nell'esaminare il contatore delle prestazioni di record inoltrati/sec nell'oggetto prestazione dei metodi di accesso. Utilizzare la funzione di gestione dinamica sys.dm_db_index_physical_stats con la modalità dettagliata su alcune delle tabelle del database e verrà restituito il numero di record inoltrati per ogni tabella nella colonna forwarded_record_count dell'output. Vedere questo argomento nella documentazione in linea per ulteriori dettagli.

Peggiori consente di rimuovere record inoltrati consiste nel creare un indice cluster e quindi rilasciarlo nuovamente. In questo modo tutti gli indici non cluster della tabella per essere rigenerato automaticamente due volte, ovvero un enorme spreco di risorse. Vedere il mio blog post per ulteriori dettagli: “Cosa succede agli indici non cluster quando la struttura della tabella viene modificata?”

Il modo più semplice per rimuovere in modo permanente e impedire record inoltrati in heap consiste nel creare gli indici cluster. Evitare di inserire “ vs indice cluster. heap ” dibattito qui informazioni perché si dovrebbero avere cluster indici nella maggior parte dei casi invece di heap. Vedere “ di chiave di clustering ” blog del mia moglie Kimberly Tripp post serie su questo per ulteriori dettagli. Consiglia di valutare l'utilizzo di indici cluster.

Quando i record della tabella aumentino di dimensioni, è possibile che record inoltrati quando è disponibile spazio sufficiente. Un altro modo per impedire inoltrati i record, è pertanto per impedire la modifica delle dimensioni del record. Ciò potrebbe significare, ad esempio, utilizzando i valori predefiniti per le colonne di lunghezza variabile.

In SQL Server 2008 è una nuova istruzione ALTER TABLE … REBUILD che consente di ricostruire gli heap. Questa procedura funziona allo stesso modo che l'istruzione ALTER INDEX … REBUILD consente di ricostruire gli indici. Microsoft ha aggiunto questa istruzione per supportare le funzionalità di compressione dei dati, ma funziona per i nostri scopi. Vedere questo argomento nella documentazione in linea per ulteriori dettagli.

Manutenzione di indici

D: Ho cambiato la routine di manutenzione indice utilizzare di rigenerazione di indici in linea, ma vengono ancora visualizzati i problemi di blocco a volte quando esegue le routine di manutenzione. Come mai? Pensato di operazioni sugli indici in linea Don ’t utilizzano i blocchi, pertanto shouldn't vedo alcun blocco. È il comportamento previsto o mi esegue qualcosa di sbagliato?

R: Si visualizzazione del comportamento previsto. È presente un blocco di tabella condiviso richiesto all'avvio dell'operazione, l'operazione di inizializzazione (un processo molto veloce). Verrà immediatamente interrotta. Questo blocco deve essere accodato come qualsiasi altro blocco e impedirà qualsiasi nuova query di apportare modifiche alla tabella fino a quando non è possibile concedere e rilasciare nuovamente il blocco.

È Impossibile acquisire il blocco solo dopo aver completato tutte le query di modifica attualmente in esecuzione. L'operazione potrebbe richiedere molto tempo, a seconda del carico di lavoro. Ciò significa che il blocco può verificarsi all'avvio di un'operazione di indice in linea.

Al termine dell'operazione, è necessario eseguire un blocco di modifica dello schema, si tratta di questo come un blocco esclusivo, per consentire il completamento. Questo anche accade molto rapidamente. Quindi si rilasciarlo immediatamente. Questo blocco impedirà qualsiasi tipo di nuove query sulla tabella (lettura o scrittura) fino a concedere e rilasciare il blocco.

Ancora una volta, non è possibile acquisire il blocco fino al completamento del tutto in esecuzione lettura SQL o scrivere query. Nuovo significa che vi è la possibilità di blocco.

Per riepilogare, anche se il nome della funzione è operazioni sugli indici in linea, richiede ancora due blocchi a breve termine che possono causare problemi di blocco. Il guadagno tramite operazioni di indice in linea tradizionale è che per la maggior parte dell'operazione di indice, non esistono blocchi e così complesso viene aumentata la concorrenza. White paper “ di operazioni di indicizzazione in linea di SQL Server 2005 ” dispone di molte più informazioni dettagliate sul funzionano di tali operazioni.

Riduzione del tempo di manutenzione indice

D: Ho ereditato alcuni sistemi in cui i processi di manutenzione indice richiedono tempo per eseguire e generare grandi quantità di I/O, ma è possibile non eseguire qualsiasi Ricrea indice perché non ottenere frammentazione degli indici. Vorrei semplificare il lavoro svolto, come non ricevo alcun miglioramento delle prestazioni. È possibile consigliare una strategia per?

R: Si tratta di un problema abbastanza comune. Esso deriva dal modo in cui i processi di manutenzione indice determinano quali indici per ricostruire o riorganizzare.

Molte persone, eseguire la funzione di gestione dinamica sys.dm_db_index_physical_stats (descritto in precedenza) tutti gli indici del database, quindi scegliere se ricostruire, riorganizzare o non eseguire alcuna operazione. Questa decisione base di avg_fragmentation_in_percent, il page_count e i valori avg_page_space_used_in_percent utilizzando una clausola WHERE sull'output.

Il problema è che la frammentazione dell'indice non viene memorizzata nella memoria come altre statistiche. Questa funzione deve leggere ed elaborare ogni indice per determinare l'estensione della relativa frammentazione. Se la maggior parte degli indici nel database sono statica o modificare molto lentamente (in termini di frammentazione), quindi sono non essere ricostruiti o riorganizzati. Controllare la frammentazione ogni volta che si esegue un processo di manutenzione indice è essenzialmente una perdita di tempo.

Viste a gestione dinamiche più supportano “ predicato push giù, ” dove solo i dati elaborati sono che corrisponde al predicato nella clausola WHERE. Tuttavia sys.dm_db_index_physical_stats è una funzione, non di una visualizzazione, non può farlo. In tal caso che è necessario filtrare manualmente e richiede solo la funzione per l'elaborazione di tali indici che potrebbero essere frammentati e potrebbe essere necessario ricostruire o riorganizzare.

Si consiglia di monitorare la frammentazione nel corso di poche settimane. In questo modo è possibile ottenere un'idea dei quali indici sono opportuno controllare la frammentazione, anziché tutto il controllo. Dopo aver ottenuto l'elenco degli indici, creare una tabella con il nome della tabella, indice nome e frammentazione soglia per l'operazione. È probabile che alcuni indici possono avere più frammentazione prima che incidono sulle prestazioni rispetto ad altri. Si tratta di “ driver tabella ” quindi utilizzare per guidare il processo di manutenzione indice. Dovrebbe eseguire un ciclo attraverso tutti gli indici descritti nella tabella ed eseguire solo la funzione sys.dm_db_index_physical_stats su di essi.

Ho implementato per diversi client. In alcuni casi, ha ridotto il runtime del processo di manutenzione indice dalle ore a 15 minuti o meno. Che è puramente non esegue questa funzione degli indici statici. Inoltre, è possibile passare un passaggio ulteriore e tenere traccia della frequenza di ricostruzione di un indice e modificare potenzialmente il FILLFACTOR dell'indice impostazione automatica, si spera causando una riduzione ulteriormente il lavoro eseguito dal processo di manutenzione indice.

Per ulteriori informazioni sui vari metodi di manutenzione degli indici, vedere il post del blog, “ importanza della manutenzione indice ” e per una spiegazione dettagliata di ciò che va in dietro le quinte della funzione, anche leggere il blog, “ Inside sys.dm_db_index_physical_stats . ”

Grazie a Kimberly l. Tripp di SQLskills.com per propria revisione tecnica di mese questo.

Paul Randal

Paul s. Randal è direttore gestione di SQLskills.com, un direttore regionale Microsoft e MVP per SQL Server. Ha lavorato nel team SQL Server Storage Engine in Microsoft dal 1999 al 2007. Ha scritto il comando DBCC CHECKDB/repair per SQL Server 2005 ed era responsabile di Core Storage Engine durante lo sviluppo di SQL Server 2008. Randal è un esperto di ripristino di emergenza, disponibilità elevata e la manutenzione del database ed è un normale relatore a conferenze in tutto il mondo. Il suo blog all'indirizzo SQLskills.com/blogs/paul ed è possibile trovare lui Twitter in Twitter.com/PaulRandal.