SQL q & R: Questioni di transazioni

Articolo
08/24/2016

I registri delle transazioni sono fondamentali per gli sforzi di backup, ma è necessario assicurarsi che stai correttamente configurati e cancellazione quando si suppone per cancellare.

Paul S. Randal

Cancellare quei tronchi

D. Ho letto che nel modello di recupero con registrazione completa, transazione registro solo compensazione avviene quando c'è un backup del log delle transazioni. Sto vedendo strano comportamento, però. A volte il backup del log non cancellare il registro. Sembra essere il backup di dati che stanno facendo. Che succede?

**R.**Tu sei corretto: nei modelli di recupero con registrazione completa e alla rinfusa, può solo essere liquidato del log delle transazioni (porzioni del log vengono contrassegnate come riutilizzabili) quando c'è un backup del log delle transazioni. Il modello di recupero, è un checkpoint che cancella log delle transazioni. Per ulteriori informazioni sulla registrazione e il ripristino in generale, vedere il mio articolo di TechNet, "registrazione di comprensione e il ripristino in SQL Server."

Tuttavia, esiste una torsione. Anche se l'operazione di compensazione di registro delle transazioni si verificherà a conclusione di un backup del log delle transazioni, non non c'è alcuna garanzia che qualsiasi porzioni del log delle transazioni in realtà verranno cancellati. Il fatto che una parte del log delle transazioni è stata eseguita il backup non è sufficiente per lasciare che venga cancellata. SQL Server deve non bisogno di quella porzione del log delle transazioni per qualsiasi altro scopo in qualsiasi altro momento.

SQL Server può ancora richiedono l'accesso a una parte del log delle transazioni perché viene eseguito un backup dei dati (come un backup completo del database). Un backup di dati deve includere la parte del log delle transazioni. Almeno avrà bisogno generati mentre sono essere copiati i dati dal database i dati del log delle transazioni. Può nemmeno più bisogno.

Ciò significa che durante l'esecuzione di un backup dei dati, Registro di compensazione non può verificarsi. Questo è vero, anche se un backup del log delle transazioni simultanee si svolge (backup di dati e di log simultanee sono stati possibili da SQL Server 2005).

In questo caso particolare, la porzione di backup di SQL Server "ricorda" c'è stato un backup del log delle transazioni e ricorda che porzioni del log delle transazioni sono stati il backup. Quando viene completato il backup dei dati simultanee, SQL Server è possibile cancellare le porzioni di registro delle transazioni che erano sostenute dal backup del log delle transazioni (a condizione che qualcos'altro non ha bisogno di quel log delle transazioni, come una transazione non vincolata, lunga).

Questo comportamento è chiamato il troncamento del log in differita. Questo è il comportamento che si sta verificando. Sembra che il backup dei dati è compensazione del log delle transazioni, ma in realtà è un artefatto del backup del log delle transazioni simultanee.

Mirror, Mirror

D. Siamo nel processo di attuazione di mirroring del database per proteggere contro la perdita di dati per uno dei nostri database. Potete dirmi che cosa dovremmo monitoriamo affinché il che nostro mirroring del database sta funzionando correttamente?

**R.**Quando si implementa il mirroring del database, è assolutamente necessario monitorare le dimensioni della coda di invio dei messaggi e coda REDO. Queste direttamente riguardano la perdita di dati e i tempi di inattività, rispettivamente.

Coda di invio tracce quanto log delle transazioni dal database principale non è ancora stato inviato al server mirror. Questa porzione di log delle transazioni vengono descritte le modifiche al database principale che sarebbe perduto se un disastro reso disponibile.

Molte persone pensano che, se si configura il mirroring del database per un'elevata sicurezza o alta disponibilità (conosciuto anche come sincrono mirroring), coda di invio sarà con sempre zero. Una transazione sul database principale non può impegnarsi fino a quando tutti i registri delle transazioni per la transazione sono stati inviati al server mirror. Tuttavia, questo non è vero. È possibile, in determinate circostanze, per i server principale e mirror per perdere il contatto con gli altri e il database principale di rimanere on-line. In questo caso, la coda di invio inizia a crescere. Questo aumenta l'esposizione di perdita di dati. Zero perdita di dati è garantita solo quando lo stato di mirroring è sincronizzato.

La coda REDO tracce quanto log delle transazioni sul server mirror non è ancora stato rigiocato nel database mirror. Un comune malinteso è che quando il server principale invia log delle transazioni al server mirror, è anche immediatamente riprodotti nel database mirror. Questo, inoltre, non è vero. Tutto ciò che è richiesto è che il log delle transazioni è scritto in memoria permanente sul server mirror.

Questo significa che dipende l'hardware del server mirror (compresi il sottosistema I/O, qualsiasi carico di lavoro simultaneo e altri fattori che impatto sulle prestazioni), ci può essere una coda di registro delle transazioni considerevole che non è ancora stata rigiocata nel database mirror.

Quando c'è un failover del mirroring, il database mirror non verrà online fino a quando il log delle transazioni è stato rigiocato. Questo è all'incirca proporzionale alla quantità di log delle transazioni un-replayed. Rappresenta anche il tempo di inattività che dell'applicazione e i suoi utenti sperimenterà quando si verifica un failover del mirroring.

Anche prestare attenzione circa la dimensione della coda REDO se hai intenzione di utilizzare gli snapshot di database dal database mirror per scopi di segnalazioni. Quando si crea uno snapshot del database, recupero del database deve elaborare il log delle transazioni eccezionale così snapshot del database è una consistente vista del database sottostante. Più grande coda REDO, più tempo ci vorrà per creare snapshot del database. Interesserà anche le prestazioni del server mirror, il quale può fare la coda REDO crescono.

Ci sono diversi altri parametri che è necessario monitorare, ad esempio la latenza di rete tra il server principale e mirror. Questo si traduce nell'overhead per transazione dell'attuazione del mirroring sincrono. È possibile utilizzare Performance Monitor (sys.dm_os_performance_counters vista a gestione dinamica [DMV]) per tenere traccia di queste metriche. È anche possibile utilizzare lo strumento di Monitoraggio mirroring del Database incorporato nel Management Studio e descritto in sql Server Books Online. Lo strumento consente di creare facilmente avvisi basati su soglie di dimensione di coda.

Incremento delle prestazioni

D. Ho consigliato che dovrei avere più file di dati per aiutare con le prestazioni, e capire il perché. Ora mi viene detto avrei anche più file di log delle transazioni così SQL Server può fare operazioni dei / O più efficiente nel log delle transazioni. È vero?

**R.**No, questo non è corretto. Purtroppo, ho sentito di questa raccomandazione, ogni tanto.

Più file di dati possono aiutare con la contesa del sottosistema I/O. In alcuni casi (comunemente con tempdb), vi potranno aiutare contesa su strutture di allocazione del database in memoria. Ci sono tutti i tipi di linee guida su come molti file di dati per creare.

La raccomandazione di avere più file di log delle transazioni è estrapolata dalla stessa raccomandazione per i file di dati, ma non è corretto. Più file di log delle transazioni non forniscono alcun guadagno in prestazioni, disponibilità, scalabilità o qualsiasi altra metrica misurabile.

Il log delle transazioni è e deve essere sequenza in natura in modo che SQL Server non eseguire i/O parallela su log delle transazioni, se ci sono più file di log delle transazioni presenti. Il primo file verrà utilizzato nella sua interezza, poi il secondo file, poi il terzo e così via. Ciò avverrà fino a quando il log delle transazioni avvolge e ricomincia al primo file di log delle transazioni. Così quei file registro supplementare non forniscono nessun guadagno.

C'è una situazione in cui può essere necessario un secondo file di registro delle transazioni. Se il primo file di registro delle transazioni è pieno, log delle transazioni non è possibile deselezionare (vedere la risposta alla prima domanda per ulteriori spiegazioni). Il primo file di log non può crescere per ospitare più record del log delle transazioni. In tal caso, aggiungere un secondo file di registro delle transazioni temporaneamente consentirà modifiche del database continuare fino a quando non è possibile cancellare il primo file di log delle transazioni.

Ridurre i/O, aumento delle prestazioni

D. Noi abbiamo indagato l'uso di dischi a stato solido (SSD) per cercare di ridurre alcuni dei nostri problemi di prestazioni di I/O, ma siamo confusi su quali basi di dati a mettere su di loro. Può darci alcune indicazioni sul modo migliore per utilizzare gli SSD?

**R.**Questa è una domanda interessante. Ci sono una varietà di risposte "definitive" vedrai sul forum di aiuto di Internet, come ad esempio, "sempre messo tempdb sul SSD" o "sempre mettere la transazione log sul vostro SSD." Nessuna di queste è opportuno.

Ci sono alcuni fattori da tenere a mente quando si considera come meglio utilizzare gli SSD con SQL Server:

Gli SSD sono costosi, così si vuole assicurarsi che stai ricevendo il ROI migliore da loro.
SSD forniscono la maggior parte delle prestazioni guadagno per workload casuale di I/O, carichi di lavoro I/O non sequenziale.
Per qualsiasi porzione di un sottosistema I/O Overload, un SSD fornirà una Spinta di prestazioni — indipendentemente il modello I/O — grazie alla sua natura, riducendo notevolmente la latenza di lettura e scrittura.
Direct attached SSD dovrebbero fornire una Spinta di prestazioni più profonda rispetto a quelli accessibili tramite qualsiasi tipo di tessuto di comunicazioni.
I registri delle transazioni sono scrittura sequenza, con gran parte letture sequenziali (e alcune letture casuali, se c'è un grande potenziale per le operazioni di rollback).
Tempdb può essere usato molto leggermente sul Server SQL. Anche se sono abituati moderatamente, essi non potrebbero verificarsi una quantità elevata di attività di scrittura di file di dati.

Quando si considerano questi fattori, ti rendi conto che mettere i log tempdb o transazione sul vostro SSD non può essere il modo migliore per utilizzarli. Identificare le parti del sottosistema I/O il collo di bottiglia di prestazioni più grande per il carico di lavoro. Questi possono essere anche i file di dati per un database di volatili online transaction processing (OLTP) o un log delle transazioni per un database con inserto pesante carico di lavoro — o possono anche essere tempdb. Questi sono i candidati per immissione sul vostro SSD, piuttosto che solo raccogliendo qualche porzione di stoccaggio SQL Server senza eseguire qualsiasi indagine.

Un altro pezzo di cattivi consigli per quanto riguarda gli SSD è che si può smettere di essere preoccupato per la frammentazione dell'indice quando si utilizzano gli SSD per archiviare i file di dati. Questo non è assolutamente il caso. È vero che gli effetti della lettura meno efficiente avanti dalla frammentazione si attenuerà latenza di leggere un po' da notevolmente ridotto, ma c'è ancora il costo dei / O più del necessario. È possibile ridurre che affrontando la frammentazione.

Quello che non considerano la maggior parte delle persone è che frammentazione non è solo di read-ahead. Uno degli effetti collaterali di che cosa causa la frammentazione (operazioni chiamate "pagina spaccature") è che la percentuale di spazio inutilizzato sulle pagine del file di dati può aumentare drammaticamente — dal 40% al 50% (questo è chiamato "densità di pagina").

Se una parte cospicua degli indici del database è frammentata, quindi senza manutenzione regolare indice delle pagine del file di dati stanno andando a contenere un sacco di spazio vuoto. Oltre a ridurre l'efficienza dei / O e utilizzo della memoria, significa anche che stai usando SSD costoso per memorizzare spazio vuoto. Che non è un buon ROI nel libro di nessuno.

Paul Randal

**Paul S. Randal**è l'amministratore delegato di SQLskills.com, Microsoft regional director e MVP per SQL Server. Ha lavorato del team di Microsoft SQL Server Storage Engine dal 1999 al 2007. Scrisse DBCC CHECKDB/riparazione per SQL Server 2005 ed è stato responsabile per il motore di archiviazione Core durante lo sviluppo di SQL Server 2008. Randal è un esperto di disaster recovery, alta disponibilità e manutenzione del database ed è un presentatore regolarmente a conferenze in tutto il mondo. Egli blog a SQLskills.com e si può trovarlo su Twitter a Twitter.com /PaulRandal..