SQL Server: Approfondimenti dei problemi di prestazioni
Ci sono diverse tattiche per determinare che cosa sta causando il maggior parte dei tempi di attesa in SQL Server, così è possibile indirizzare l'ottimizzazione delle prestazioni.
Tratto daSQL Server DMV Starter Pack", pubblicato dalla documentazione di Red Gate (2010).
Glenn Berry, Louis Davidson e Tim Ford
Let's get destra giù al livello OS. Stiamo guardando i thread di lavoro che svolgono le attività richieste dalle nostre transazioni. Il Dynamic Management Objects (DMO) in questa categoria — tutti che iniziano con il nome di sys.dm_os_—provide estremamente informazioni dettagliate circa il modo SQL Server interagisce con il sistema operativo e l'hardware. È possibile utilizzare questi DMOs per ottenere risposte a tutti i tipi di domande di prestazioni:
- Il wait_stats DMO ti dice quali tipi di cose che i thread del sistema operativo del SQL Server sono in attesa.
- Performance_counters ti dice i valori del contatore delle prestazioni SQL Server e come essi sono decodificati.
- Ring_buffers, os_schedulers o wait_stats vi avviserà di una preoccupazione di utilizzo della CPU.
- Sys_info vi darà le caratteristiche della macchina su cui è in esecuzione il SQL Server .
- Sys_memory o process_memory vi dirà come la memoria intera viene utilizzata.
- Memory_cache_counters o buffer_descriptors dirvi come memoria cache viene utilizzata.
Tutte queste query funzionano con SQL Server 2005, 2008 e 2008 R2. Inoltre tutti richiedono autorizzazione View Server State. Nota: Gli oggetti, dovrebbero essere collettivamente come DMOs. Tuttavia, utilizzando DMOs tende a causare una certa confusione con l'interamente indipendenti "Distributed Management Objects," quindi è ancora abbastanza comune per gli amministratori di database per riferirsi a DMOs collettivamente come "Viste a gestione dinamica, o DMV."
SQL Server aspetta
Probabilmente la motorizzazione più significativa nella categoria sistema operativo è os_wait_stats. Ogni volta una sessione deve aspettare per qualche motivo, prima di proseguire il lavoro richiesto, SQL Server registra la lunghezza del tempo aspettato e la risorsa per cui SQL Server è in attesa. L'os_wait_stats DMV espone queste aspettare statistiche, aggregate tutti gli ID di sessione, per darvi una sommaria rassegna di dove le grandi attese sono una determinata istanza.
Questa stessa DMV espone anche i contatori delle prestazioni (PerfMon), che forniscono misure specifiche di utilizzo delle risorse (velocità di trasferimento su disco, quantità di tempo di CPU consumato e così via). Correlando le statistiche attesa con le misure delle risorse, individuare le risorse più controversa sul sistema e rapidamente evidenziare potenziali colli di bottiglia.
Attese e code
Utilizzo di attese e code è un'ottima base per l'ottimizzazione delle prestazioni, come spiegato nella libro bianco,SQL Server 2005 in attesa e le code," da Tom Davidson. Essenzialmente, ogni richiesta al SQL Server si traduce in una serie di "attività di lavoratore" essere avviati. Una pianificazione SQL Server assegna ogni attività a un thread di lavoro. Normalmente c'è uno scheduler di OS di SQL per ogni CPU, e solo una seduta per ogni utilità di pianificazione può essere eseguito in qualsiasi momento.
È compito dell'utilità di pianificazione distribuite il carico di lavoro in modo uniforme su thread di lavoro disponibili. Se il thread di lavoro di una sessione è in esecuzione il processore, lo stato della sessione sarà in esecuzione, come esposto da nella colonna stato di DM exec_requests DMV.
Se un thread è pronto ad andare, ma l'utilità di pianificazione di cui è attualmente assegnato ha un'altra sessione in esecuzione, verrà posizionato in coda "eseguibile". Questo significa semplicemente che è in coda per ottenere sul processore. Questo è chiamato un attesa di segnale.
Segnale attende
Il tempo di attesa del segnale è esposto alla colonna signal_wait_time_ms. Questo si riferisce esclusivamente al tempo di attesa della CPU. Se una sessione è in attesa di un'altra risorsa diventi disponibile, ad esempio una pagina bloccata, o se è necessario eseguire i/o una sessione in esecuzione, quindi spostarlo la lista d'attesa. Questa è una risorsa di attesa e lo stato della sessione in attesa sarà registrato come "sospeso". Il motivo per l'attesa è registrato ed esposta nella colonna wait_type della os_wait_stats DMV.
Il tempo totale trascorso in attesa è esposto alla colonna wait_time_ms, così è possibile calcolare il tempo di attesa della risorsa, come segue:
Risorse attese = totale attese – attese di segnale (o (wait_time_ms) - (signal_wait_time_ms))
Segnale attese sono inevitabili in sistemi (OLTP), di elaborazione delle transazioni online come questi sono costituiti da un gran numero di transazioni brevi. La chiave metrica, per quanto riguarda la potenziale pressione di CPU, è l'attesa del segnale in percentuale il totale attende.
Un'alta percentuale di segnale è segno di pressione di CPU. "Alta" spesso si riferisce a più di 25 per cento, ma dipende dal vostro sistema. Valori maggiori di 10 per cento al 15 per cento possono essere anche un segno preoccupante. In generale, le statistiche attesa sono un mezzo efficace per diagnosticare i tempi di risposta nel vostro sistema. In termini molto semplici, o lavoro o vi aspettate.
Se i tempi di risposta sono lenti e a trovare nessun significative attese, o principalmente attese di segnale, sapete che avete bisogno di concentrarsi sulla CPU. Se trovate il tempo di risposta è costituite principalmente da tempo speso in attesa di altre risorse (come la rete, i/o e così via), allora sapete che dovete concentrare i tuoi sforzi di ottimizzazione su tali risorse.
Profilatura delle prestazioni
Il nostro primo script nella categoria OS utilizza l'os_wait_stats DMV, che restituisce informazioni su tutte le attese incontrate dai thread eseguito. È possibile utilizzare questa visualizzazione aggregata per diagnosticare problemi di prestazioni con SQL Server globale e lotti e domande specifiche.
Questa query semplice calcola segnale attese e attese di risorse come percentuale del tempo di attesa complessiva, al fine di diagnosticare la potenziale pressione CPU:
-- Total waits are wait_time_ms (high signal waits indicate 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 ;
Questo è utile per aiutare a confermare la pressione della CPU. Perché il segnale attese sono tempo trascorso in attesa di una CPU a servizio di un thread, se si registra il segnale totale attende sopra circa 10 per cento al 15 per cento, che è un buon indicatore di pressione di CPU. Queste aspettare statistiche sono cumulativi, poiché SQL Server è stato riavviato ultima, quindi è necessario conoscere alla baseline valore per segnale aspetta e guarda l'andamento nel tempo.
È possibile cancellare manualmente le statistiche di attesa, senza riavviare il server, mediante l'emissione di una coerenza database controllo comando SQLPERF (DBCC), come segue:
-- Clear Wait Stats DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) ;
Se l'istanza del SQL Server è stato in esecuzione per un bel po' e fare un cambiamento significativo, ad esempio aggiungendo un nuovo indice importante, si dovrebbe prendere in considerazione le vecchie statistiche attesa di compensazione. In caso contrario, il vecchio statistiche attesa cumulativo si mascherano qualunque impatto che il cambiamento ha sui tempi di attesa.
Il nostro secondo script di esempio (vedere Figura 1) dimostra come usando l'os_wait_stats DMV aiuterà a determinare le risorse per cui SQL Server sta spendendo più tempo d'attesa.
Figura 1 questo script genera un rapporto sulle principali cause di attese.
-- 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
Lo script in Figura 1 ti aiuta a individuare i colli di bottiglia più grande a livello di istanza. Questo ti aiuta a focalizzare i vostri sforzi di ottimizzazione il particolare tipo di problema. Ad esempio, se aspetta la cima cumulativa sono tipi disco relative ai / O, quindi vorreste studiare questa edizione ulteriormente utilizzando query DMV relative ai dischi e contatori PerfMon.
Contatori delle prestazioni
La motorizzazione che espone i contatori PerfMon è sys.dm_os_performance_counters. Questo metodo restituisce una riga per ogni contatore delle prestazioni gestita dal server. Questo è un'utile DMV, ma può essere frustrante lavorare con.
A seconda del valore per cntr_type per una determinata riga, potrebbe essere necessario scavare in profondità per ottenere informazioni significative da questa DMV. È un rimpiazzo per il vecchio sys.sysperfinfo da SQL Server 2000.
Lo script in Figura 2 ti aiuta a indagare sulle condizioni insolite riempiendo il log delle transazioni. Restituisce il modello di recupero, registro riutilizzo attesa Descrizione, dimensioni del log delle transazioni, lo spazio utilizzato, percentuale di registro utilizzato, livello di compatibilità e pagina verificare opzione per ogni database nell'istanza del SQL Server corrente.
Figura 2 determinare ciò che si sta riempiendo il log delle transazioni utilizzando questo script.
-- 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)%' ;
La query in Figura 2 è utile per la valutazione di un server di database non conosce. È anche generalmente più utile dal punto di vista monitoraggio. Ad esempio, se aspetta il riutilizzo di registro descrizione è qualcosa di insolito come ACTIVE_TRANSACTION, il log delle transazioni è pieno l'85 per cento, quindi non ci dovrebbero essere alcuni campanelli d'allarme andando fuori.
Utilizzando questo tipo di monitoraggio è estremamente utile per identificare le fonti e le cause di attese e di altri problemi di prestazioni. Questo tipo di indagine può aiutarvi a meglio indirizzare i tuoi sforzi di ottimizzazione delle prestazioni nel posto giusto.
.png)
Glenn Berry lavora come un architetto di database presso NewsGator Technologies Inc. in Denver, Colorado Lui è un SQL Server MVP e ha un'intera collezione di certificazioni Microsoft, tra cui MCITP, MCDBA, MCSE, MCSD, MCAD e MCTS, che dimostra che gli piace prendere il test.
.png)
Louis Davidson è stato nel settore IT per 16 anni come sviluppatore di database aziendale e architetto. È stato un SQL Server Microsoft MVP per sei anni e ha scritto quattro libri sulla progettazione di database. Attualmente è l'architetto di dati e talvolta DBA per il Christian Broadcasting Network, uffici di supporto in Virginia Beach, Virginia e Nashville, Tenn.
.png)
Timothy Ford è un SQL Server MVP e ha lavorato con SQL Server per più di 10 anni. Egli è il DBA primaria ed esperto di materia per la piattaforma SQL Server per la salute di spettro. È stato scrivere di tecnologia dal 2007 per una varietà di siti Web e gestisce il proprio blog in thesqlagentman.com, che copre il SQL come argomenti di sviluppo bene come telelavoro e professionale.
Ulteriori informazioni suSQL Server DMV Starter Pack" a rosso-gate.com.