SQL Server: Demistificazione dei ritardi di SQL Server
È possibile utilizzare i dati di monitoraggio per i tempi di attesa delle transazioni di SQL Server per trovare risorse eccessivamente utilizzate ed eliminare i colli di bottiglia.
Tratto da "SQL Server DMV Starter Pack," pubblicato da Red Gate Books (2010).
Glenn Berry, Louis Davidson e Tim Ford
Ogni volta che una sessione di SQL Server deve aspettare per qualche motivo, prima di proseguire il lavoro richiesto, SQL Server registra l'intervallo di tempo aspettato. Registra anche la risorsa per la quale la transazione è in attesa.
La gestione dinamica di sys.dm_os_wait_stats mostra espone (DMV) queste aspettare le statistiche, aggregate in tutte le sessioni, di fornire una sintesi recensione di dove le grandi attese sono in qualsiasi istanza specifica. Questo stesso DMV espone anche i contatori delle prestazioni (PerfMon), che forniscono risorse specifico utilizzo misure (velocità di trasferimento su disco, quantità di tempo di CPU consumato e così via).
Correlando le statistiche di attesa con le misurazioni delle risorse, puoi rapidamente individuare le risorse più "impugnate" sul tuo sistema ed evidenziare potenziali colli di bottiglia.
SQL Server 2005 attese e le code
L'uso di "attese e code" come la base per una metodologia di ottimizzazione delle prestazioni è spiegata in un eccellente libro bianco da Tom Davidson. In sostanza, ogni richiesta per SQL Server provocheranno l'avvio di una serie di "lavoratore compiti."
Una pianificazione di SQL Server assegna ogni attività a un thread di lavoro. Normalmente non c'è un'utilità di pianificazione di SQL OS per ogni CPU e sola sessione all'utilità di pianificazione in esecuzione in qualsiasi momento. È il lavoro dell'utilità di pianificazione per diffondere il carico di lavoro in modo uniforme tra i thread di lavoro disponibile.
Se il thread di lavoro di una sessione viene eseguito sul processore, la sessione di stato sarà in "esecuzione," come esposto da nella colonna Stato di sys.dm_exec_requests DMV. Se un thread è pronto per andare, ma l'utilità di pianificazione a cui è assegnato attualmente ha un'altra sessione in esecuzione, allora sarà posto nella coda "Praticabili". Questo significa che è in coda per ottenere sul processore. Questa viene definita come un attesa di segnale.
Il tempo di attesa del segnale è esposto alla colonna signal_wait_time_ms ed è tempo di attesa unicamente CPU. Se una sessione è in attesa di un'altra risorsa diventare disponibili al fine di procedere, come ad esempio una pagina bloccata, o se è necessario eseguire i/O una sessione in esecuzione, esso viene spostato alla lista di attesa. Questa è una risorsa attesa e lo stato della sessione in attesa verrà registrato come "Sospesa".
La ragione per l'attesa è registrata ed esposti nella colonna wait_type della sys.dm_os_wait_stats DMV. Il tempo totale trascorso in attesa è esposto alla colonna di wait_time_ms, così si può calcolare il tempo di attesa di risorse come segue:
Risorsa attese = totale attese – attese segnale = (wait_time_ms)-(signal_wait_time_ms)
Segnale attese sono inevitabili in sistemi (OLTP), che sono costituiti da un gran numero di brevi operazioni di elaborazione delle transazioni on-line. La metrica chiave, ai potenziali pressione di CPU, è l'attesa del segnale come percentuale del totale le attese. Un segnale ad alta gradazione è un segno di una pressione eccessiva della CPU. La letteratura tende a citare "alto" come più di circa il 25 per cento, ma dipende dal vostro sistema.
Sui nostri sistemi, trattiamo i valori maggiori di 10 per cento a 15 per cento come un segno preoccupante. In generale, l'uso di statistiche di attesa rappresenta un mezzo molto efficace per diagnosticare i tempi di risposta nel vostro sistema. In termini semplici, o lavoro, o attesa. Tempo di risposta è uguale a tempo di servizio più il tempo di attesa.
Se i tempi di risposta sono lenti e non trovi alcuna significative attese, o principalmente attese di segnale, allora avete bisogno di concentrarsi sulla CPU. Se si trova il tempo di risposta è principalmente composto di tempo trascorso in attesa di altre risorse (quali la rete, i/O e così via), poi si sa esattamente dove concentrare i vostri sforzi di ottimizzazione.
Togliere le congetture
Mario Broodbakker ha scritto un eccellente introduttivo serie di articoli sull'utilizzo di attendere gli eventi per diagnosticare i problemi di prestazioni. Il nostro primo script nella categoria OS utilizza il sys.dm_os_wait_stats DMV, che è definito come: Restituisce informazioni su tutte le attese incontrate dai thread in esecuzione. È possibile utilizzare questa visualizzazione aggregata per diagnosticare i problemi di prestazioni con SQL Server e anche con batch e domande specifiche.
Questa query calcola segnale attese e risorsa attende come percentuale del tempo di attesa complessiva, al fine di diagnosticare la potenziale pressione CPU:
-- Total waits are wait_time_ms (high signal waits indicates CPU pressure) SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits] , CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]FROM sys.dm_os_wait_stats ;
Questa query è utile per contribuire a confermare la pressione della CPU. Poiché segnale attende ora aspettano una CPU a servizio di un thread, se vengono registrate le attese di segnale totale che sono grosso modo maggiore rispetto a 10 per cento a 15 per cento, allora questo è un buon piuttosto indicatore di pressione della CPU.
Queste aspettare statistiche sono cumulativi dal momento che l'ultima è stato riavviato SQL Server, quindi è necessario conoscere la vostra linea di base il valore per le attese di segnale e guardare l'andamento nel tempo. È possibile cancellare manualmente le statistiche di attesa, senza riavviare il server, mediante l'emissione di un comando DBCC SQLPERF, come segue:
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
Se l'istanza di SQL Server è in corso da un po' di tempo e si fa un cambiamento significativo come l'aggiunta di un nuovo indice, allora si dovrebbe prendere in considerazione di compensazione il vecchio attesa statistiche. In caso contrario, le vecchie statistiche attesa cumulativo si mascherano il cambio di qualsiasi impatto sui tempi di attesa.
Secondo script di esempio utilizzando il sys.dm_os_wait_stats DMV aiuterà a determinare le risorse su cui SQL Server è spendere più tempo d'attesa:
-- Isolate top waits for server instance since last restart -- or statistics clear WITH Waits AS ( SELECT wait_type , wait_time_ms / 1000. AS wait_time_s , 100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct , ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN' ) ) SELECT W1.wait_type , CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s , CAST(W1.pct AS DECIMAL(12, 2)) AS pct , CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn GROUP BY W1.rn , W1.wait_type , W1.wait_time_s , W1.pct HAVING SUM(W2.pct) - W1.pct < 95 ; -- percentage threshold
Questo script vi aiuterà a individuare il più grande collo di bottiglia a livello di istanza. Questo aiuta a focalizzare i vostri sforzi di ottimizzazione su un particolare tipo di problema. Ad esempio, se la parte superiore cumulativa aspetta tipi sono disco I/O-correlati, quindi vuoi indagare su questa questione ulteriormente utilizzando le query DMV relativi al disco e i contatori PerfMon.
Esporre i contatori delle prestazioni
DMV che espone i contatori PerfMon è sys.dm_os_performance_counters, che viene descritto come: "Restituisce una riga per ogni contatore delle prestazioni, gestito da server." Questo è un utile DMV, ma può essere frustrante da utilizzare. Dipende dal valore di cntr_type per una riga specificata, è necessario passare attraverso alcuni interessanti gyrations per ottenere informazioni significative da questa DMV. Essa è una sostituzione per il vecchio sys.sysperfinfo da SQL Server 2000.
Questo script consente di indagare sulle condizioni inusuali riempiendo di registro delle transazioni. Esso restituisce il modello di recupero, descrizione di attesa di riutilizzo del registro, dimensione del log delle transazioni, lo spazio di log utilizzati, percentuale di registro utilizzato, livello di compatibilità e pagina verificare opzione per ogni database sull'istanza corrente di SQL Server:
-- Recovery model, log reuse wait description, log file size, -- log usage size and compatibility level for all databases on instance SELECT db.[name] AS [Database Name] , db.recovery_model_desc AS [Recovery Model] , db.log_reuse_wait_desc AS [Log Reuse Wait Description] , ls.cntr_value AS [Log Size (KB)] , lu.cntr_value AS [Log Used (KB)] , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %] , db.[compatibility_level] AS [DB Compatibility Level] , db.page_verify_option_desc AS [Page Verify Option]FROM sys.databases AS db INNER JOIN sys.dm_os_performance_counters AS lu ON db. name = lu.instance_name INNER JOIN sys.dm_os_performance_counters AS ls ON db. name = ls.instance_nameWHERE lu.counter_name LIKE ‘Log File(s) Used Size (KB)%’ AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;
Questa query consente di valutare un server di database non conosce. È anche utile dal punto di vista monitoraggio. Ad esempio, se il riutilizzo di registro aspetta descrizione è qualcosa di insolito e registro delle transazioni è 85% pieno, che deve far scattare un allarme.
.png)
Glenn Berry lavora come un architetto di database alla NewsGator Technologies in Denver, Colorado Egli è un MVP per SQL Server e ha un'intera collezione di certificazioni Microsoft, tra cui MCITP, MCDBA, MCSE, MCSD, MCAD e MCTS, che dimostra che egli ama prendere le prove.
.png)
Louis Davidson è stato nel settore IT per 16 anni come uno sviluppatore di database aziendale e architetto. Egli è stato un Microsoft MVP per SQL Server per sei anni e ha scritto quattro libri sulla progettazione di database. Attualmente egli è l'architetto di dati e a volte DBA per la rete Christian Broadcasting, gli uffici di supporto in Virginia Beach, va. e Nashville, Tenn.
.png)
Timothy Ford è un MVP per SQL Server e per più di 10 anni ha lavorato con SQL Server. Egli è il primario DBA ed esperto di materia per la piattaforma di SQL Server per la salute dello spettro. È stato iscritto sulla tecnologia dal 2007 per una varietà di siti Web e mantiene il proprio blog a thesqlagentman.com, che coprono SQL come argomenti di sviluppo così come telelavoro e professionale.
Ulteriori informazioni su "SQL Server DMV Starter Pack" a red-gate.com/our-company/about/book-store.