Come trovare le query più lente su SQL Server con dm_exec_query_stats

Analizziamo uno dei possibili metodi per estrarre su SQL Server l’elenco delle query più problematiche basandoci sulla vista di sistema dm_exec_query_stats. Ma cosa intendiamo con “più problematiche”? Dipende dal contesto, potremmo essere interessati alle query che durano più a lungo, a quelle che consumano più CPU, a quelle che leggono più pagine sul disco o sulla ram, o che richiedono più memoria. Tuttavia vedremo come sarà molto semplice passare da un tipo di analisi all’altra per eseguire un task fondamentale nel processo di monitoraggio delle performance di SQL Server.

Attenzione: non fare copia e incolla del codice senza i dovuti test.

Caratteristiche della sys.dm_exec_query_stats

La sys.dm_exec_query_stats è una vista di sistema che contiene dati statistici relativi alle query completate il cui piano d’esecuzione è stato salvato nella cache. Infatti quando lanciamo una query SQL Server tendenzialmente salverà il relativo piano di esecuzione, in modo da non doverlo ogni volta ricalcolare quando lanciamo la stessa query o una simile.

I piani di esecuzione non restano nella cache all’infinito. In generale non abbiamo pieno controllo di quando vengono cancellati, sicuramente dopo un riavvio del server la cache sarà praticamente vuota, ma anche altre cause più o meno prevedibili possono provocare la cancellazione di una riga.

Tuttavia in molti casi il numero di righe contenute è sufficiente per estrarre utili informazioni dall’istanza di SQL Server, come l’elenco delle dieci query più lunghe o quelle che consumano più CPU.

Estrazione delle dieci query più lente

Con il codice seguente possiamo estrarre le dieci query in media più lente tra quelle il cui piano d’esecuzione è salvato nella cache. Parlo di lunghezza media perché se la stessa query viene eseguita più volte, all’interno della sys.dm_exec_query_stats sarà presente comunque una sola riga con l’indicazione del numero di esecuzioni e della durata totale (a questa regola ci sono alcune eccezioni).

SELECT TOP 10 
  SUBSTRING(
      b.text,
      (a.statement_start_offset/2) + 1, 
      (CASE WHEN a.statement_end_offset = -1
	    THEN datalength(b.text)
	    ELSE a.statement_end_offset
       END - a.statement_start_offset)/2 + 1) AS query_text,
   a.total_elapsed_time/a.execution_count AS avg_elapsed
FROM SYS.dm_exec_query_stats AS a
CROSS APPLY SYS.dm_exec_sql_text(a.sql_handle) AS b
ORDER BY avg_elapsed DESC;

Osserviamo nella from una Cross Apply della SYS.dm_exec_query_stats con la SYS.dm_exec_sql_text tramite il campo sql_handle. Questo ci permette di aggiungere al nostro output il testo della query. Tuttavia esso non è contenuto direttamente all’interno della colonna text, in quanto nel caso lanciassimo un unico batch contenente più query, text risulta valorizzata sempre con l’intero batch contenente tutte le query. Ad esempio, se lanciamo in un’unica esecuzione questo batch composto da tre query;

SELECT * 
FROM   Clienti 
WHERE  IdCliente = 1;
SELECT * 
FROM   Fatture 
WHERE  IdFattura = 1;
SELECT * 
FROM   Fornitori 
WHERE  IdFornitore = 1;

allora nella SYS.dm_exec_query_stats avrò tre righe, tutte con lo stesso valore di sqlhandle, ma con statistiche differenti, in quanto ognuna relativa a una delle singole query presenti nel batch. Di conseguenza, dopo la cross apply avrò all’interno del campo text tre valori identici contenenti il codice dell’intero batch. Tuttavia, combinando tramite una opportuna SUBSTRING il contenuto di text con le colonne statement_start_offset e statement_end_offset della SYS.dm_exec_query_stats, riuscirò a estrapolare la singola query a cui fa riferimento ogni riga.

Colonne della sys.dm_exec_query_stats

Come anticipato prima se eseguiamo la stessa query più volte, tendenzialmente non andrò a creare nuove righe ma a incrementare il valore in execution_count. Ogni statistica sarà riportata dunque su quattro livelli di aggregazione: somma totale, minimo, massimo, e ultima esecuzione. Ad esempio, relativamente alla durata della query avrò quattro colonne:

  • last_elapsed_time;
  • total_elapsed_time;
  • min_elapsed_time;
  • max_elapsed_time.

La durata non è però l’unica statistica disponibile, ad esempio ho anche colonne relative all’utilizzo della cpu, al numero di letture logiche eseguite, alla memoria richiesta e utilizzata della query. Se ad esempio volessi estrarre le dieci query con il consumo maggiore di CPU, potrei semplicemente modificare la clausola ORDER BY nella query del paragrafo precedente e scrivere

ORDER BY a.total_worker_time/a.execution_count;

Righe della sys.dm_exec_query_stats

Come vi preannunciavo in alcuni casi la stessa query potrebbe apparire comunque in più righe distinte. Viceversa query diverse potrebbero essere accorpate in un’unica riga. Ad esempio, anche eseguendo singolarmente queste due query:

SELECT * 
FROM   dbo.Fatture 
WHERE  IdFattura = 454;
SELECT * 
FROM  dbo.Fatture
WHERE IdFattura = 455;

mi aspetto comunque che all’interno della sys.dm_exec_query_stats ci sia una sola riga il cui testo sarà

SELECT * 
FROM  [dbo].[Fatture] 
WHERE [IdFattura]=@1;

In altri casi apparentemente identici a questo, query simili potrebbero essere riportare in righe diverse. In questo caso le righe avranno comunque lo stesso valore all’interno della colonna query_hash, che può essere dunque utilizzato per raggruppare i dati.

Aggiunta del piano d’esecuzione

Possiamo aggiungere il piano d’esecuzione stimato (non l’effettivo) con un’ulteriore Cross Apply, questa volta con la vista di sistema sys.dm_exec_query_plan. Aggiungeremo nella SELECT la colonna c.query_plan, mentre nella FROM l’espressione

CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS c

Alcune note importanti sull’utilizzo di sys.dm_exec_query_stats

Riepilogo alcune osservazioni importanti:

  1. quando usiamo la parola query per far riferimento al contenuto della sys.dm_exec_query_stats, lo facciamo in senso lato: non ci sono solo le interrogazioni, ma anche aggiornamenti, inserimenti, create…Quindi occhio a non copiare ed eseguire una delle query restituite senza assicurarsi che si tratti di una semplice query di selezione;
  2. ricordiamoci di monitorare sempre l’esecuzione delle query: se ci sono troppo righe e la query sta impiegando troppo, meglio rieseguirla aggiungendo una where su last_execution_time;
  3. essendo informazioni aggregate, non conosco metodi per ricavare da queste tabelle la sessione di origine delle query;
  4. le query provengono da tutti i database dell’istanza. Sono comprese anche “query di sistema” non lanciate dagli utenti;
  5. i dati sono spesso in microsecondi.

Continua a imparare

La sys.dm_exec_query_stats può essere un ottimo strumento per un’analisi proattiva delle performance di SQL Server. Essa infatti può fornirci numerose informazioni su quali siano le query più problematiche relativamente a vari parametri come l’utilizzo di CPU, la durata e il numero di pagine lette.

Torna in alto
Torna su