Come trovare le query più lunghe con dm_exec_query_stats

elenco query più lunghe su sql server

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 cachato. 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ù lunghe

Con il codice seguente possiamo estrare le dieci query in media più lunghe 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 sqlhandlema con statistiche differenti, in quanto ognuna relativa ad 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 ad 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;

e

SELECT * FROM dbo.Fatture WHERE IdFattura = 454;

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

 

Continua a imparare

Torna su