SQL q &R: Colli di bottiglia e registri transazioni

  • Articolo

A volte è facile determinare la natura di un collo di bottiglia delle prestazioni — altre volte, non così tanti. Lo stesso vale per la configurazione dei registri transazioni.

Paul S. Randal

Collo di bottiglia di archiviazione

D: Sto cercando di determinare la natura del collo di bottiglia del mio sistema delle prestazioni. Sono convinto che è il livello di archiviazione come sto vedendo la coda del disco lunghezze superiori a due andare. Ho letto che questo è un buon modo per dimostrare che SQL Server è sovraccarico della memoria. Questo è vero? In caso affermativo, cosa posso fare su di esso?

R: Purtroppo, ciò che avete letto è una leggenda metropolitana. SQL Server è progettato per utilizzare I/O asincrono e felicemente spingerà la lunghezza della coda di sopra di due dischi. Ogni thread che emette un IO allora continua facendo qualcos'altro (potenzialmente) finché l'IO è completa. SQL Server tenta di aumentare la velocità effettiva dal sottosistema I/O emettendo IOs asincrone simultanee. Essa sarà inoltre eseguire operazioni come lettura in avanti durante la scansione di grandi volumi di dati.

In realtà, alcune operazioni come DBCC CHECKDB verranno saturare il sottosistema di I/O. Non è raro vedere le lunghezze di coda del disco di diverse centinaia. Potete leggere di più su questa leggenda urbana di lunghezze di coda del disco in questo post del blog di .

Allora la questione diventa, "che cosa fare Guardate per determinare se c'è un collo di bottiglia del sottosistema di I/O?" Ci sono due contatori di prestazioni in oggetto prestazioni disco fisico. Si dovrebbe prestare attenzione a questi:

  • AVG. Disk sec/Read
  • AVG. Disk sec/Write

Questi danno il tempo in millisecondi per completare un IO. Se questi numeri sono costantemente superiori (o hanno regolari i picchi più elevati) rispetto alla norma (che dovrebbe essere tra 5ms e 12 ms), poi il disco fisico è il collo di bottiglia di I/O. Naturalmente, tale disco fisico può essere un LUN SAN, ma lei non può scavare più profondo da Windows.

Se si dispone di più file log e di dati di SQL Server su quel disco fisico, potrebbe essere necessario determinare quali file stanno causando il carico di I/O. Utilizzare la gestione dinamica (DMV) di vista sys.dm_io_virtual_file_stats ed eseguire alcune semplici analisi di serie temporali sui risultati.

Se i risultati DMV non indicano un carico pesante su quel disco fisico, un amministratore storage può avere file posizionati da altre applicazioni su quella parte del sottosistema I/O. Questo carico di lavoro potrebbe essere quello che è monopolizzando la larghezza di banda di I/O. In tal caso, è necessario chiedere a quel particolare amministratore per spostare i file di SQL Server dedicato a una parte del sottosistema I/O.

Se è puramente file di SQL Server nel sottosistema ed è possibile identificare quelli che stanno causando il GI eccessivo, considerare queste strategie:

  • Guardate il carico di lavoro query di database e determinare se sta eseguendo scansioni di tabella eccessivo a causa di un'errata strategia di indicizzazione, o piani di query male causati da statistiche obsolete.
  • Spostare alcuni dei file in una parte diversa del sottosistema I/O.
  • Aggiungere più memoria al server per consentire un maggiore pool di buffer di SQL Server (in memoria cache delle pagine del file di dati) e di evitare tanto leggere IO.

Se nessuno di questi lavori che è davvero solo un caso in cui il carico di lavoro ha superato il sottosistema di I/O, spostare un sottosistema I/O più capace. Si potrebbe anche considerare la memoria flash storage di classe enterprise come fusione-io.

Size Matters

D: Sto stabilisce i requisiti di archiviazione per alcuni nuovi server e sto avendo problemi a determinare come grandi per rendere i registri delle transazioni. In passato, ho provato a in base alle dimensioni delle transazioni. Talvolta, però, sembra double. Può spiegare come posso arrivare a una stima decente?

R: Non esiste una formula semplice per calcolare la dimensione del registro delle transazioni ottimale. Purtroppo, è anche difficile evitare transazioni crescita del log a meno che non hai disattivato aumento automatico per il file di log. Tuttavia, lo spegnimento automatico-crescere non è mai consigliabile.

Il primo di questi è il livello RAID sottostante. I vari livelli RAID hanno diversi compromessi per quanto riguarda le prestazioni e la ridondanza. Ad esempio, la configurazione RAID più conveniente che offre ancora qualche ridondanza è RAID-5, ma questa configurazione può solo far fronte a un singolo guasto (a meno che l'utilizzo di RAID-6, o configurato unità hot-spare) e a volte può compromettere le prestazioni per carichi di lavoro di scrittura pesante, a seconda di quante unità presenti nella matrice.

Il log delle transazioni dovrebbe sempre essere in grado di aumento automatico. Questo è particolarmente vero per le emergenze, quando il file registro di dimensione monitoraggio ha esito negativo. Ad esempio, si dispone di un avviso di SQL Server Agent dal contatore delle prestazioni per cento registro utilizzato sarà superiore al 90%, ma il contatto di emergenza notificato tramite posta elettronica/cercapersone è malato. Se il registro non può crescere, tutti attualmente in esecuzione operazioni di modifica del database interrompe e rollback. Questo si traduce in tempi di inattività per il carico di lavoro.

Tuttavia, dicendo: non vi è alcuna formula facile non è un po' fuorviante. Esistono numerose operazioni che occupano lo spazio del log delle transazioni. È possibile utilizzare le dimensioni di queste operazioni per stimare i requisiti di registro delle transazioni. Questi possono essere operazioni che si verificano come parte del carico di lavoro quotidiano, o azioni più frequenti come la manutenzione del database. È necessario considerare tutti loro. Elenco operazione include:

  • La più grande singola transazione insert/update/delete il carico di lavoro esegue (se una singola istruzione transazione implicita che affligge milioni di righe di una tabella o una transazione esplicita che esegue molte operazioni).
  • La più grande massa-operazione esegue il carico di lavoro, come un BULK INSERT. Se si utilizza la modalità di recupero con registrazione completa, si può essere in grado di ridurre la quantità di log delle transazioni generate utilizzando il modello di recupero BULK_LOGGED. Se si utilizza il modello di recupero BULK_LOGGED-registrazione minima per alcune operazioni, può influire la capacità di recuperare dopo una catastrofe. Vedere il mio blog post, " un SQL Server DBA mito al giorno: BULK_LOGGED recupero modello . "
  • Ricostruzione di un indice dell'indice cluster più grande. Si può essere in grado di utilizzare BULK_LOGGED qui pure.

Con tutte queste operazioni, non è solo la quantità di log delle transazioni, che è necessario considerare, è inoltre necessario prendere in considerazione lo spazio del sistema di gestione del registro delle transazioni sarà "di riserva" per consentire rollback della transazione corretto. Se una transazione genera 100 MB di record del log delle transazioni, il sistema si riserva di circa 100 MB di spazio vuoto nel log delle transazioni per garantire può interrompere la transazione e correttamente rollback. Si tratta di un meccanismo di sicurezza per prevenire un database diventando incoerente. Questo è il motivo per cui si può avere visto il log delle transazioni crescere, anche se si pensa che hai dato lo spazio sufficiente per la più grande operazione.

Un'altra cosa da considerare è se vi siano motivi perché i record del log delle transazioni deve restare nel registro. Questo potrebbe portare ad un crescita del log delle transazioni dover crescere ancora di più. Alcuni dei motivi possibili includono:

  • Il database è utilizzando i modelli di recupero con registrazione completa o BULK_LOGGED e non esegue il backup del log delle transazioni (o esecuzione li raramente). Record del log deve eseguire un backup prima scartando li.
  • C'è una transazione insolitamente lunga. Ciò impedirà di scartare tutti i record del log delle transazioni generati dall'avvio della transazione a esecuzione prolungata.
  • Il mirroring del database è in uso e alcuni record del log delle transazioni non sono stati inviati dal server principale al server mirror. È impossibile ignorare questi finché non sono stati inviati.
  • La replica transazionale (o peer-to-peer) è in uso e ci sono alcuni record del log delle transazioni che non ha elaborato il lavoro di agente di lettura Log.

Se stai vedendo la coltivazione di registro della transazione e non siete sicuri perché sta accadendo, chiedere di SQL Server. Eseguire la query:

SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO

Il risultato sarà il motivo non è possibile eliminare alcuni record del log e riutilizzare lo spazio del log (chiamato "cancellazione" o "troncamento" del log).

Come potete vedere, ci sono abbastanza poche cose che possono influire sulle dimensioni del log delle transazioni, ancor più se si sta valutando come pure nel database tempdb. Potete leggere un po' di più su questo argomento nel mio post di blog, " di importanza della gestione di dimensione corretta transazione registro," e nel technet Magazine articolo " registrazione del Server SQL di comprensione e di recupero ."

Registrazione obbligatoria

D: Può spiegare perché non posso fare operazioni SQL Server non registrata? Ho letto che troncamento della tabella è non registrate — perché non ci può essere una cornice per rendere tutte le operazioni non registrate in modo che SQL Server viene eseguito più velocemente? Che cosa succede se non mi preoccupo in grado di recuperare dopo un disastro? Soprattutto, vorrei essere in grado di ignorare il log delle transazioni di tempdb.

R: Non è vero quello che avete letto su un'operazione TRUNCATE TABLE. Tutte le operazioni su tutti i database vengono registrate in una certa misura. Alcuni sono "registrazione minima," come ad esempio il troncamento della tabella. In poche parole, un'operazione con registrazione minima è uno dove viene registrato solo l'allocazione e deallocazione delle pagine del file di dati. Eventuali operazioni di Records/indice della tabella delle pagine non sono registrate. Questo velocizza le operazioni e significa meno del log delle transazioni viene generato — ma c'è ancora qualche registrazione.

Un troncamento della tabella viene sempre minimamente registrato in tutti i modelli di recupero. Altre operazioni con registrazione minima (ad esempio indice carichi di costruire/ricostruire e massa) vengono registrati solo minimamente quando si utilizzano i modelli di recupero SIMPLE o BULK_LOGGED.

Le operazioni solo veramente non registrata in SQL Server sono quelli che riguardano l'archivio delle versioni in tempdb, che supporta funzioni come l'isolamento dello snapshot e le operazioni sugli indici in linea. Questi possono essere non registrati perché non c'è mai la necessità di ripristinare una versione archivio operazione o esecuzione crash-recupero sul database tempdb.

Questo si ottiene al nocciolo della tua domanda. Perché le operazioni in SQL Server non possono essere non registrata? SQL Server deve sempre essere in grado di eseguire il rollback di un'operazione se qualcosa va storto. Se vi è stata che alcuna descrizione (come i record del log delle transazioni) dell'operazione che non aveva fatto, come SQL Server sapere cosa fare durante il rollback? È solo possibile raggiungere questo obiettivo attraverso la registrazione.

Anche se non si cura recupero di arresto, SQL Server deve ancora essere in grado di ripristinare le operazioni se il database esaurisce lo spazio o incontra un settore danneggiato, o se la query esaurisce la memoria. Se SQL Server non è possibile ripristinare un'operazione, il database diventa inutilizzabile e si interrompe il carico di lavoro.

Questo vale anche per il database tempdb. Anche se la registrazione è semplificata e ridotto in tempdb, è mai possibile rimuoverlo completamente, per le stesse ragioni. Inoltre, SQL Server deve essere in grado di eseguire il ripristino di arresto anomalo del sistema dopo ogni crash affinché ogni database è coerente. In caso contrario, il database è inutilizzabile. Linea di fondo: Non non c'è alcun modo per rendere le operazioni non registrate in SQL Server e io non aspettarsi che le cose cambino.

Paul Randal

Paul S. Randal è il direttore generale di SQLskills.com, un Microsoft regional director e MVP per SQL Server. Ha lavorato nel team di SQL Server Storage Engine in Microsoft 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 ripristino di emergenza, ad alta disponibilità e manutenzione dei database e un normale relatore a conferenze in tutto il mondo. I blog SQLskills.com/blogs/paul e si può trovare lui su Twitter a Twitter.com/PaulRandal.

Contenuto correlato