Domande e risposte su SQL: Ottimizzazione e prestazioni ottimali
Indici duplicati, operazioni di rollback annullate e picchi di I/O possono provocare problemi di prestazioni. Tuttavia, è possibile trovare una soluzione a tutto ciò.
Paul S. Randal
Duplicare gli indici
D. Sembra che mi permetta di creare indici che sono esattamente gli stessi nella stessa tabella SQL Server. Come questo può contribuire mia prestazione del carico di lavoro? Query diverse utilizzerà diverse copie dello stesso indice?
**R.**È un peccato che SQL Server consente duplicati indici, poichè non forniscono alcun beneficio di alcun tipo. In realtà, gli indici duplicati possono essere dannosi in molti modi.
Un indice duplicato si verifica quando le chiavi di indice sono esattamente gli stessi come un altro indice, specificato nello stesso ordine e con le stesse specifiche ASC o DESC. Le colonne incluse (se presente) sono anche lo stesso (anche se le colonne incluse possono essere specificate in qualsiasi ordine).
SQL Server solo utilizzare uno degli indici duplicati per aiutare con le query, ma devono mantenere tutti gli indici in una tabella durante inserire, aggiornare ed eliminare le operazioni. Questo significa che ogni volta che c'è un'istruzione insert o delete sul tavolo, deve riflettersi in tutti gli indici. Lo stesso è vero per gli aggiornamenti, se le colonne aggiornate fanno parte dell'indice.
Questa manutenzione indice utilizza risorse extra e genera i record del log delle transazioni supplementare — tutti per gli indici che sono essenzialmente inutili. Tali indici duplicati prendono ulteriore spazio su disco e spazio extra nei backup — e le pagine necessarie per la manutenzione di indice prendono spazio extra nella memoria troppo.
Indici duplicati rischiano di diventare frammentata. Inoltre essi richiedono risorse extra durante rimuovere la frammentazione degli indici regolari. Il log delle transazioni supplementare registra dalla manutenzione indice e rimuovere la frammentazione può anche portare ad per abbassare le prestazioni delle funzioni (HA) ad alta disponibilità come il mirroring del database e la replica transazionale.
SQL Server ti non dà alcun avviso che hai appena creato un indice di duplicati, quindi spetta a voi per evitare di farlo. Controllare se si dispone già di indici duplicati non è importa piccola. Esso coinvolge tutte le definizioni di indice e manualmente comparare loro, o estesa a livello di codice analisi dei cataloghi di sistema di scripting. L'anno scorso, Kimberly Tripp pubblicato un completa soluzione a questo problema.
Guardatevi il Rollback
D. Recentemente ho dovuto annullare un aggiornamento di lunga durata. Dopo l'operazione di rollback, il backup del log delle transazioni ogni giorno successivo è stato enorme. Mi aspettavo che fosse molto piccolo, come nulla era cambiato nel database. Può spiegare questa anomalia?
**R.**Questo è un equivoco piuttosto comune. Se rotolate indietro una vasta operazione, backup differenziale successivo deve essere piccolo, giusto? Sbagliato.
Qualsiasi momento che SQL Server apporta una modifica al database, due cose accadono. In primo luogo, esso genera record del log delle transazioni che descrivono il cambiamento. In secondo luogo, per le pagine di file dati modificate dal cambiamento, il bit corrispondente viene impostato in una bitmap differenziale. Ciò significa che quelle pagine dovrebbero essere sostenute da un backup differenziale successivo.
Quando si rollback di un'operazione, SQL Server deve annullare le modifiche dell'operazione fatta. Ciò significa che esso esamina tutti i record del log delle transazioni generato dalla parte anteriore dell'operazione. Essa ha annullare le modifiche apportate in ordine inverso. Ogni record del log delle transazioni descrive una singola modifica al database come parte dell'operazione. Rollback di tale cambiamento, devi fare un altro cambiamento al database che nega l'effetto della modifica originale. Per esempio, è sarebbe ripristinare un inserimento record da eliminare il record. L'effetto netto è che non esiste il record.
Qui è la parte più confusionaria: ogni modifica effettuata durante il ripristino è davvero solo un'altra modifica del database (anche se uno speciale). Per ogni modifica al database, ci deve essere un record del log delle transazioni. Così anche le modifiche apportate durante un rollback devono essere registrate correttamente. Questo significa che un rollback vasta operazione genererà non solo transazione record del log per la parte anteriore dell'operazione, ma anche per il rollback. I backup del log delle transazioni indietro tutti questi record del log delle transazioni, contabilità per il backup del log delle transazioni di grandi dimensioni.
Quando la parte anteriore dell'operazione provoca la bitmap differenziale di avere bit impostati perché porzioni del database sono cambiati, è non può cancellare i bit della bitmap differenziale ancora una volta perché il database è cambiata. Non importa se il cambiamento è stato alla fine rollback. Le pagine del file di dati sono ancora state cambiate (due volte, in realtà) e quindi devono essere sostenute da un backup differenziale.
Il nocciolo della questione è che, anche quando un'operazione di rollback, il database è ancora cambiato. È necessario che tutti i backup riflettano le modifiche apportate.
In cerca di picchi
D. Io sto risolvendo un dove vediamo periodiche picchi I/O da uno dei nostri server SQL. Io ho ristretto verso il basso per i checkpoint utilizzando PerfMon, ma non posso dire quale database è il principale colpevole. Come posso io trapano in ulteriormente?
**R.**I checkpoint esistono per due motivi. In primo luogo, essi aggiornare le pagine del file di dati con quanto è stato scritto nel log delle transazioni. SQL Server utilizza un meccanismo denominato registrazione write-ahead, dove modifiche al database sono descritti nel log delle transazioni prima di essere riflessa nei file di dati. Questo garantisce la durata dei cambiamenti in caso di un incidente. In secondo luogo, essi ridurre la quantità di carico costante dei / O scrivendo solo i dati modificati pagine a file periodicamente, piuttosto che dopo ogni cambiamento di ciascuna pagina del file di dati.
I punti di controllo si verificano separatamente per ogni database. Essi stanno innescati dipende da diversi fattori, tra cui l'intervallo di recupero — questo è il Server SQL stimare che abbastanza log delle transazioni è stato generato dal punto di controllo dell'ultimo modo che cade recupero avrà circa un minuto (di default).
Questo numero corrisponde a una generazione di molte decine di migliaia di record del log delle transazioni individuali. Le pagine di file dati più cambiata da questi record del log delle transazioni, la più grande la quantità dei / O che devono essere eseguite da checkpoint del database.
È possibile tenere traccia di checkpoint utilizzando il contatore "Checkpoint pagine/sec" in SQL Server: Gestione buffer. Che dà solo un conteggio di aggregato tra tutti i database nell'istanza di SQL Server. Per determinare quale database è in "checkpoint" in qualsiasi momento, è necessario utilizzare il flag di traccia.
Se si attiva il flag di traccia 3502 (traccia stampa quando si verifica un checkpoint), 3504 (dettagli di stampa traccia circa il checkpoint) e 3605 (consentire traccia stampe di andare nel log di errore), sarete in grado di determinare quale database è contabilità per le punte di I/O a causa di posti di blocco.
È possibile attivare questi flag di traccia utilizzando il comando:
DBCC TRACEON (3502, 3504, 3605, -1)
Disabilitarle nuovamente utilizzando il comando:
DBCC TRACEOFF (3502, 3504, 3605, -1)
I checkpoint successivi produrrà output simile al seguente nel log degli errori:
2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 started (8) 2011-12-30 05:07:14.390 spid17s About to log Checkpoint begin. 2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 phase 1 ended (8) 2011-12-30 05:07:14.830 spid17s FlushCache: cleaned up 4307 bufs with 201 writes in 441 ms (avoided 23 new dirty bufs) 2011-12-30 05:07:14.830 spid17s average throughput: 76.30 MB/sec, I/O saturation: 198, context switches 392 2011-12-30 05:07:14.830 spid17s last target outstanding: 15, avgWriteLatency 2 2011-12-30 05:07:14.830 spid17s About to log Checkpoint end. 2011-12-30 05:07:14.830 spid17s Ckpt dbid 21 complete
Ciò consente di vedere quale database è in fase checkpoint e che corrispondono alle informazioni da PerfMon. Poi può indagare perché c'è così tanti dei dati viene modificati tra posti di blocco, eseguire più frequenti posti di blocco per ridurre il picco di I/O o aumentare la capacità del sottosistema I/O.
Preoccupazioni di consolidamento
D. La mia azienda ha istituito una nuova politica che richiedono che noi consolidare quanto più possibile per ridurre i costi dell'hardware. Sto anche essere spinti a ridurre il numero di istanze di SQL Server per risparmiare i costi di licenza. Ci sono eventuali linee guida per quanti database per ogni istanza di SQL Server hanno senso?
**R.**La risposta a questa domanda è un grande "dipende". L'elenco dei fattori comprende la dimensione del database, i tipi di carichi di lavoro che si sta eseguendo, la volatilità dei dati, il tipo di richiesta manutenzione regolare e il ripristino di emergenza e HA requisiti.
Ogni istanza di SQL Server include una quantità finita di spazio in memoria per memorizzare le pagine del file di dati in corso di elaborazione in qualsiasi momento (questo è noto come il pool di buffer). I database più avete su un'istanza con carichi di lavoro più disparate tutti che richiedono elaborazione, la maggiore concorrenza ci sarà tra i carichi di lavoro per spazio pool di buffer.
Questo può portare a thrashing la memoria del pool di buffer. Ci sarà costante zangola per fare spazio per nuove pagine del file di dati letti dal disco. Ci saranno anche grandi quantità di lettura I/O con latenze di lettura più che accettabile. Tutti questi fattori si degradano le prestazioni del carico di lavoro.
Se i vari carichi di lavoro comportano modifiche al database, ci saranno anche i situata nel/di scrittura o di punti di controllo periodici. Con numerose banche dati consolidati in una singola istanza, potrebbe essere più posti di blocco che si verificano simultaneamente. Ciò potrebbe causare latenze di scrittura I/O — rallentando le operazioni di checkpoint e ulteriormente che contribuiscono al degrado delle prestazioni del carico di lavoro.
Manutenzione del database regolare diventa anche un problema con un gran numero di basi di dati. Se ogni database richiede indice e manutenzione di statistiche, la verifica della coerenza e backup, può essere una sfida pianificare tutte queste operazioni per tutti i database in modo tale che non sia in conflitto con l'altro e di mettere ancora più carico dei / O sul server.
I database più ci si trovano in un'istanza, più difficile diventa di utilizzare le tecnologie HA native di SQL Server per proteggere tutti loro. È più probabile che avrete bisogno di qualche tipo di tecnologia di replica a livello del sottosistema dei / O — anche solo dalla prospettiva della facilità di gestione. Questo significa ulteriore esborso di capitale che potrebbe compensare il risparmio sui costi di consolidamento dei server.
Consolidamento è un argomento enorme. Farlo pienamente giustizia è oltre la portata di una singola colonna. Questo è abbastanza cibo per la mente di farvi cauto di over-consolidating. Da altro lato, si potrebbero avere molti piccoli database con carichi di lavoro minimi che potesse ospitare in una singola istanza senza problemi. Come ho detto prima, dipende.
.jpg)
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. Ha blog all'indirizzo SQLskills.com /Blogs/Paul e si può trovarlo su Twitter a Twitter.com /PaulRandal..