SQL Q & A: Dimensioni non trascurabili

  • Articolo

Le dimensioni, la frammentazione degli indici e la disponibilità di un database in seguito a un failover sono tra le problematiche che affliggono gli amministratori SQL evidenziate nell'articolo di questo mese.

Paul S. Randal

Paura la frammentazione

D. Ho letto alcuni post del blog che sembra implicare che non abbiamo bisogno di essere preoccupato per la frammentazione dell'indice, se i nostri database sono ospitati su archiviazione allo stato solido — la teoria che sta a stato solido (SSD) le unità sono così molto più veloce di filatura di dischi. Capisco che il degrado di prestazioni sarebbe ridotto, ma possiamo davvero solo completamente ignorare frammentazione dell'indice?

**R.**Se stai usando dischi di filatura o .ssds., è necessario prestare attenzione alla frammentazione dell'indice. Frammentazione dell'indice comprende due fenomeni — pagine di indice di out-of-order e problemi di densità di pagina. L'ex previene lettura efficiente avanti durante le scansioni di indice-gamma e quest'ultimo riduce la densità di dati.

È vero che le latenze di lettura/scrittura con SSD sono molto piccole. Di conseguenza, la necessità di eseguire le operazioni più frequenti, più piccolo read-ahead quando gamma scansione un indice frammentato non hanno lontanamente quanto più un effetto di prestazione come nella stessa situazione quando si utilizzano dischi di filatura.

Tuttavia, la riduzione di densità di dati dalla frammentazione dell'indice può essere ancora un grosso problema. La maggior parte delle frammentazione dell'indice si verifica da un'operazione chiamata una "pagina divisa." Questo accade quando lo spazio libero viene creato in una pagina in un indice, spostando la metà le righe dell'indice in una nuova pagina. Questo lascia le vecchie e nuove pagine con circa il 50 per cento di spazio vuoto. Con un indice di fortemente frammentato, non è raro vedere gli indici con una densità media di pagina del 70 per cento o meno (con spazio libero del 30 per cento).

Con questo in mente, se un gran numero di indici nelle banche dati memorizzati sul vostro SSD hanno pagina bassa densità, questo significa che tuo SSD costoso potrebbe archiviare una grande quantità di spazio vuoto. Questo non è chiaramente una situazione ottima. Inoltre, anche se i/O supplementare necessario leggere nelle pagine più bassa densità avrà basse latenze sul SSD, ti prendono più spazio nel pool di buffer di SQL Server (la cache di pagina di file di dati in memoria). Questo significa anche tua memoria preziosi server non è in uso in modo ottimale.

L'altra cosa da considerare oltre la frammentazione dell'indice stesso è la causa della frammentazione: pagina si divide. Queste sono costose operazioni che generano un sacco di record del log delle transazioni (dare un'occhiata al mio post del blog vedere quanto male può essere). Questi record di registro supplementare significa elaborazione aggiuntiva da tutto ciò che legge il log delle transazioni (come ad esempio la replica transazionale, i backup di database mirroring, log shipping). Ciò può causare degrado delle prestazioni per quegli stessi processi. Modo da non ignorare la frammentazione dell'indice solo perché stai usando gli SSD.

Non guardare allo specchio

D. Ci stanno ridisegnando la nostra strategia di disponibilità, ma avere diventare bloccati su come fare un paio di replica transazionale i database di sottoscrizione più altamente disponibili. Noi non potevamo utilizzare il mirroring del database in SQL Server 2005 perché avremmo dovuto reinizializzare la sottoscrizione dopo un failover. C'è una soluzione migliore ora che siamo su SQL Server 2008 R2?

**R.**Tu sei corretto affermare che del mirroring di un database di sottoscrizione in SQL Server 2005 fornisce solo una copia ridondante dei dati speculare già alla copia mirror del database di sottoscrizione. Non non c'è alcun modo per ricreare la sottoscrizione di replica senza una reinizializzazione completa. Questo rende ovviamente una scelta povera su SQL Server 2005 di mirroring del database.

Con SQL Server 2008, c'era un nuovo meccanismo introdotto nella replica transazionale che permette di parziale reinizializzazione di una sottoscrizione. L'opzione viene chiamato "inizializzare da lsn." Viene specificato come parametro @ sync_type quando si chiama sp_addsubscription.

La replica transazionale peer-to-peer in SQL Server 2008 è stata migliorata per consentire di aggiungere e rimuovere nodi in una topologia peer-to-peer senza dover effettuare tutte le attività nella topologia completamente quiescente prima. Questo è stato un grande impulso alla topologia peer-to-disponibilità di dati fornisce. L'opzione "initialize from lsn" è stato aggiunto come questi miglioramenti sono stati fuori posto.

Per il mirroring del database, non non c'è nessun supporto aggiuntivo per il mirroring del database di sottoscrizione (come c'è in agente di lettura Log per il mirroring del database di pubblicazione). Tuttavia, è possibile utilizzare il metodo "initialize from lsn" per fornire un veloce reinizializzazione di una sottoscrizione dopo un failover del mirroring.

Questa metodologia si basa sulla determinazione del numero di sequenza di Log (LSN — un numero univoco che identifica un record del log delle transazioni) delle più recenti replicato operazione applicato al database di sottoscrizione prima che si verificasse il failover del mirroring. Chiameremo questo LSN.

Alcune di queste operazioni si hanno anche stata rispecchiata alla copia del database mirror prima che si verificasse il failover. Questo potrebbe andare a LSN3, ad esempio, un po' più indietro nel tempo rispetto LSN. Ci saranno anche alcune operazioni che non sono stati applicati al database di sottoscrizione a tutti. Queste sono più recenti nel tempo rispetto a LSN o LSN3. Chiameremo quelle LSN1.

Tutte le operazioni fino a LSN furono applicate al database di sottoscrizione principale. Tutte le operazioni fino a LSN3 sono state applicate al database di sottoscrizione principale e rispecchiate nel database di sottoscrizione di specchio. Per eseguire un'inizializzazione "initialize from lsn" di una sottoscrizione di nuova dopo un failover del mirroring, la chiamata a sp_addsubscription necessario utilizzare LSN3 come punto di partenza.

Il mantenimento di distribuzione periodo di tempo deve essere impostato anche così le operazioni vengono conservate nel database di distribuzione per qualche tempo dopo la loro applicazione al database di sottoscrizione. In breve, è possibile ora utilizzare il mirroring del database per fornire maggiore disponibilità di un database di sottoscrizione con solo un minimo reinizializzazione necessaria dopo un failover del mirroring. Per una spiegazione più approfondita su questo, scaricare il white paper, "replica di SQL Server: Fornendo ad alta disponibilità utilizzando Database mirroring del. "

Too Big to Handle

D. Il nostro database principale ha raggiunto quasi 9TB. Noi stiamo trovando che noi semplicemente non hanno la capacità di eseguire operazioni di manutenzione regolare senza compromettere seriamente la nostro carichi di lavoro regolare. Siamo più preoccupati per essere in grado di eseguire il backup del database per consentire il ripristino di emergenza. Avete qualche consiglio?

**R.**Questo è un caso dove scindendo il database in pezzi più gestibile sarebbe stato benefico. È possibile farlo in vari modi, il più comune dei quali utilizzare il tabella SQL Server/indice partizionamento caratteristica (in Enterprise Edition) o manualmente dividere le cose in tabelle separate.

In entrambi i casi, il punto cruciale è quello di creare più filegroup nel database. Con il partizionamento ogni partizione degli indici/tabelle più grandi risiede in un filegroup separato. Con spacco manuale, ogni tabella di grandi dimensioni risiede in un filegroup separato (possibilmente con tutti i relativi indici pure).

Utilizzando filegroup separati, avete più granulare unità del database che è possibile eseguire il backup e ripristino. Non avrete operare sull'intero 9TB ogni volta. Se tu avessi un database sales, ad esempio, con i dati dal 2012 al 2008, è potrebbe partizionare varie tabelle da un intervallo di dati in partizioni anno civile. Ogni partizione anno sarebbe in un filegroup separato.

Con solo il 2012 filegroup in fase modifiche, si potrebbe appoggiarlo su frequentemente. Potrebbe eseguire il backup altri filegroup immutabile molto meno frequentemente. Questo consente di risparmiare spazio di archiviazione di backup e la quantità di tempo che i/O sovraccarico supplementare di eseguire il backup è sostenute sul sistema produttivo.

Con tale disposizione, disaster recovery diventa anche più veloce (con Enterprise Edition). È solo necessario ripristinare rapidamente il filegroup necessaria per portare la parte Online Transaction Processing (OLTP) del carico di lavoro on-line. È possibile fare questo con un ripristino parziale, quindi utilizzare disponibilità parziale database per portare il database on-line. È possibile ripristinare il filegroup contenente i dati più vecchi successivamente utilizzando ripristino online, mentre l'attività OLTP è che si verificano nel filegroup già online.

Potete leggere di più su questo approccio in questi white paper:

Sotto pressione

D. Una cosa che confonde il nostro team DBA è come dire se il pool di buffer è pressioni. C'è un sacco di informazioni contrastanti su cui i contatori PerfMon e ciò che le soglie da utilizzare. La maggior parte di quello che ho letto dice di usare la pagina l'aspettativa di vita (PLE) e 300 come soglia. Si può far luce su questo?

**R.**Non siete soli nella vostra confusione. Il numero 300 in primo luogo è stato citato in un white paper Microsoft pubblicato cinque anni fa. Che è ora gravemente obsoleti.

PLE è il contatore di destro da utilizzare, ma dovete capire che cosa significa e quando essere preoccupato. Quel numero fornisce una misura immediata di aggressivamente come il pool di buffer è fare spazio per le pagine del file di dati richiesti essere letti dal disco in memoria. Non è una media. È il tempo previsto in secondi che una pagina letta dal disco sarà restano in memoria prima che dovete stanare così un'altra pagina può prendere il suo posto.

Come tale, guardando un singolo valore PLE non dirvi molto. Hai bisogno di guardare alle tendenze di valore. È del tutto possibile per operazioni di SQL Server valide causare PLE eliminare drasticamente. Recupererà spesso poi al suo valore ex. Se PLE gocce e resta basso, che è un motivo di preoccupazione.

La soglia per quando interessati non è un valore fisso, come molte persone descrivono. I mezzi di 300 valore il pool di buffer intero viene sostituito ogni 300 secondi. Se si dispone di un pool di buffer di 100 GB, ad esempio, questo significa che 100 GB di nuovi dati viene letto in memoria ogni cinque minuti. Questo è chiaramente un problema di prestazioni. Tuttavia, diventa un modo di emissione massiccia prestazioni prima PLE hits 300. È possibile calcolare un valore più ragionevole tramite (memoria del pool di buffer in GB / 4) * 300, come spiegato nel questo post del blog.

È inoltre necessario essere a conoscenza della configurazione non-uniform memory access (NUMA) del server. Il contatore PLE in oggetto prestazioni Buffer Manager è in realtà la media di PLE per ogni nodo NUMA, se hai configurato di NUMA. Questo significa che il Buffer gestire PLE di monitoraggio non è un vero indicatore di pressione della memoria sul server. In tal caso, si dovrebbe misurare il contatore PLE in ciascuno degli oggetti prestazioni Buffer partizione. È possibile leggere di più su PLE e NUMA in questo post del blog.

PLE è il contatore di destro per monitorare, ma si dovrebbe solo essere interessato se il valore scende significativamente inferiore alla norma e vi rimane per molto tempo. Che è un orientamento generale, ma purtroppo non ci sono non specifiche che si applicano a tutte le situazioni.

Paul S. 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. Egli scrisse DBCC CHECKDB/riparazione per SQL Server 2005 ed era 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 a sqlskills.com /Blogs/Paul, e lo potete trovare su Twitter a Twitter.com /PaulRandal..

Contenuti correlati