Analizziamo i tempi di attesa di SQL Server con dm_os_wait_stats

In questo articolo parliamo della vista di sistema sys.dm_os_wait_stats di SQL Server che contiene utili informazioni per l’attività di diagnostica e monitoraggio delle performance del Database. In generale quando lanciamo una query essa non verrà immediatamente processata: pensiamo ad esempio al caso frequente in cui stiamo cercando di interrogare una tabella che nel frattempo è stata modificata all’interno di una transazione ancora aperta.

Tuttavia questa non è l’unica tipologia di attesa che si può verificare: SQL Server potrebbe non avere a disposizione la RAM richiesta, potrebbe essere necessario spostare i dati dal disco al buffer cache, eccetera. Tutte queste tipologie di attese sono raggruppate all’interno della vista di sistema sys.dm_os_wait_stats contenente i dati per circa mille diverse tipologie di attese.

Come è lecito aspettarsi, la vista non contiene i dati storici a partire dall’installazione dell’istanza. Svariati eventi possono portare al reset della tabella, il più frequente è il riavvio del servizio di SQL Server. In alcuni casi può essere opportuno valutare se storicizzare i dati in una tabella fisica, aggiungendo l’indicazione di data e ora del salvataggio.

Contenuto della sys.dm_os_wait_stats

La sys.dm_os_wait_stat contiene una riga per ogni tipologia di attesa, il cui nome è riportato nella colonna wait_type. Sono presenti comunque gruppi di righe attinenti ad attese di tipologia simile: ad esempio in tutte le righe relative ai lock sulle tabelle il contenuto della colonna wait_type inizia per “LCK”. La query seguente restituisce il numero 63

SELECT COUNT(*) 
FROM   sys.dm_os_wait_stats 
WHERE  wait_type LIKE 'LCK%';

lanciata sulla mia versione di SQL Server Express 2019, Su questa versione, oltre a wait_type, le altre due colonne più significative sono:

  • wait_time_ms contenente i tempi di attesa totali dall’ultimo reset della tabella;
  • signal_wait_time_ms contenete la porzione di tempo di wait_time_ms tra il momento in cui il motivo dell’attesa era risolto (ad esempio la transazione che interessava la tabella è stata chiusa) e l’inizio effettivo della query, posticipato perché in attesa della CPU necessaria per partire.

Il suffisso ms sta a indicare che il tempo è riportato in millisecondi. Come conseguenza del contenuto delle due colonne, un primo valore che possiamo considerare di monitorare nel tempo è il rapporto tra signal_wait_time_ms e wait_time_ms: 

SELECT 
 CONVERT(DECIMAL(18,2), SUM(signal_wait_time_ms))
         /
CONVERT(DECIMAL(18,2), SUM(wait_time_ms)) AS Percentuale
FROM sys.dm_os_wait_stats;

Il crescere di questo numero può darci un indizio sul sorgere di problemi legati alla CPU.

Trovare le tipologie di attesa più ricorrenti

Un primo modo di ricavare informazioni dalla sys.dm_os_wait_stats  consiste nel calcolare per ogni wait la percentuale sul totale delle attese. Possiamo risolvere questo task facilmente usando una window function

SELECT 
 wait_type, 
 100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS Percentuale
FROM sys.dm_os_wait_stats 
ORDER BY Percentuale DESC;

Tuttavia è molto probabile che nelle prime posizioni troviamo dei “falsi positivi”, cioè delle tipologie di attesa fisiologiche e relative a task di sistemi che (quantomeno la maggior parte delle volte) non devono preoccuparci. Parlo di voci come SOS_WORK_DISPATCHER, CLR_SEMAPHORE, LAZYWRITER_SLEEP, eccetera, che possiamo filtrare dal risultato della query tramite una semplice NOT IN. Puoi trovare più informazioni sulla costruzione di query sulla sys.dm_os_wait_stats ad esempio a questo link https://www.mssqltips.com/sqlservertip/1949/sql-server-sysdmoswaitstats-dmv-queries/

Tipologie di attesa più importanti

Senza avere eccessive pretese di dettaglio e completezza, riportiamo alcune tipologie di attese spesso legate a problemi specifici.

  • CXPACKET può indicare problemi di CPU legati all’esecuzione di numerose query in parallelo;
  • OLEDB può indicare problemi relativi a linked server;
  • DISKIO_SUSPEND è legato a procedure di backup;
  • i task che iniziano con LCK indicano rallentamenti dovuti ai lock sulle tabelle generati da query di aggiornamento;
  • RESOURCE_SEMAPHORE alla RAM in quanto riporta le attese dovute al fatto che SQL Server non ha a disposizione la memoria richiesta per l’esecuzione della query;
  • in alcuni casi SOS_SCHEDULER_YIELD  può indicare problemi relativi CPU;
  • le attese che contengono IO sono solitamente legate alle operazioni I/O sul disco. In particolare PAGEIOLATCH è relativo alle attese per portare i dati dal disco alla buffer cache.
  • PREEMPTIVE a problemi esterni a SQL Server.

Vi riporto un video a riguardo della conferenza SQLBits https://sqlbits.com/Sessions/Event8/Why_are_we_Waiting.

Come uno sviluppatore T-SQL può usare la sys.dm_os_wait_stats

A mio parere i risultati ricavati dalla sys.dm_os_wait_stats possono essere utilizzati da noi sviluppatori T-SQL per indirizzare l’analisi sull’ottimizzazione delle query. Ad esempio in caso di problemi legati alla CPU potremmo partire ottimizzando le query con più alte richiesta di CPU, per la RAM da quelle con maggior richiesta di memoria, per LCK analizzando il codice nelle transazioni. Per questa tipologia di analisi possiamo utilizzare l’approccio descritto in questo articolo https://www.yimp.it/trovare-query-lunghe-dm_exec_query_stats/

Solo sistemisti e DBA molto esperti possono essere invece in grado di capire se aumentare la memoria o la CPU disponibile per SQL Server porti o meno a dei miglioramenti.

Continua a imparare

In questo articolo abbiamo visto come interrogare la vista di sistema sys.dm_os_wait_stats può essere un ottimo punto di partenza per indirizzare le nostre analisi sulle performance di SQL Server. In base alle tipologie di attesa più frequenti, potremmo infatti dare precedenza alle query che, ad esempio, consumano più CPU o creano dei Lock sul database.

Torna in alto
Torna su