Blog di Mauro Munzi

A partire da SQL Server 2005 la Plan Cache di SQL Server si rende visibile tramite la Dynamic Management View (DMV) sys.dm_exec_cached_plans, che ritorna una riga per ogni Execution Plan memorizzato e che nella sua forma più semplice (SELECT * FROM…) ci dice sostanzialmente poco, a parte permetterci di fare delle aggregazioni su alcuni campi come ad esempio size_in_bytes per avere un’idea della distribuzione degli oggetti all’interno della Plan Cache stessa

 select sum(size_in_bytes)/1024 AS 'SizeInKB', objtype
from sys.dm_exec_cached_plans group by objtype order by 1 desc

La situazione inizia a farsi interessante nel momento in cui iniziamo a fare una CROSS APPLY con sys.dm_exec_query_plan

 select cp.*, qp.query_plan
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp 

operazione che ci permette di ottenere nel risultato l’ XML dell’ Excution Plan, e con un click del mouse possiamo anche visualizzarlo nel suo formato grafico, più conosciuto.

Bene a questo punto dal momento che abbiamo a disposizione il testo che rappresenta il nostro Execution Plan (alla fine l’ XML quello è…), possiamo pensare di ricercare particolari condizioni con l’obiettivo di estrapolare dalla cache quelle query che si distinguono ad esempio per la presenza di determinati “operatori” come parallelismo, HASH, SORT, ecc. che in molti casi sono sintomo di scarse performance oppure ad esempio ricercare gli statement che gioverebbero della presenza di indici; il tutto avviene utilizzando le funzione e proprietà della sintassi XQuery per interrogare i contenuti di tipo XML

In questo primo esempio andiamo a ricercare tutte quelle query, sp, ecc. che fanno uso del parallelismo

select p.query_plan, cp.*
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
where
p.query_plan.value('declare namespace
	p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') = 1

Fondamentalmente andiamo a cercare all’interno di ogni nodo che definisce la singola operazione svolta dall’engine (RelOp) l’elemento “Parrallel” con valore uguale a 1

Lo stesso risultato lo possiamo ottenere anche ricercando l’ operatore “Parallelism”, usando quindi una stringa come criterio di ricerca invece che un numero, tramite exists, ed esplicitando il Namespace all’inizio della query

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select cp.*, qp.query_plan
from sys.dm_exec_cached_plans as cp
  cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where qp.query_plan.exist('//RelOp[
             @PhysicalOp = "Parallelism"]') = 1

Con questo approccio possiamo cercare qualsiasi altra ricorrenza di elementi all’interno della nostra plan cache, come ad esempio operazioni di HASH MATCH

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select  cp.*, qp.query_plan
from sys.dm_exec_cached_plans as cp
  cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where qp.query_plan.exist('//RelOp[
             @PhysicalOp = "Hash Match"]') = 1

SORT ed altre operazioni poco performanti; purtroppo non è possibile risalire facilmente al “peso” delle singole operazioni all’interno dell’Execution Plan, ossia la percentuale occupata da ogni singolo operatore come la vediamo nel formato grafico, in quanto il costo viene calcolato in base a particolari algoritmi interni tale per cui la semplice proporzione con il costo totale non sembra risulta attendibile, soprattutto per gli Execution Plan che sfruttano il parallelismo.

Lo stesso approccio mi è tornato utile in alcuni casi per identificare tutte quelle query che coinvolgono linked server ed in particolare quelle che si distinguono per una “Remote Scan”, altra operazione poco efficiente; in questo caso basta utilizzare @PhysicalOp = “Remote Scan” nella nostra parte di XQuery ed il gioco è fatto…
Inoltre con una piccola aggiunta possiamo risalire anche al nome del server remoto

with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select  cp.*, qp.query_plan,
qp.query_plan.value('declare default element namespace
	"http://schemas.microsoft.com/sqlserver/2004/07/showplan";
(//RelOp/RemoteQuery/@RemoteSource)[1]' , 'varchar(100)') AS [Remote Server]
from sys.dm_exec_cached_plans as cp
  cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where qp.query_plan.exist('//RelOp[
             @PhysicalOp = "Remote Query"]') = 1

Usando sempre i dati messi a disposizione dall’ Execution Plan possiamo anche estrarre l’elenco di tutti gli statement eseguiti dall’ultimo riavvio del servizio per trovare quelli che suggeriscono la creazione di un indice per incrementare le performance e allo stesso tempo filtrare ad esempio tutti quelli che hanno un impatto maggiore del 50%

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
 SELECT cp.*, qp.query_plan
 FROM sys.dm_exec_cached_plans cp
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
 WHERE qp.query_plan.exist('//MissingIndexes') = 1
 and qp.query_plan.value('
max(//MissingIndexes/MissingIndexGroup/@Impact)' , 'float') > 50

Per quest’ultima operazione è ragionevolmente più efficiente fare riferimento alle DMV che contengono i dettagli degli indici mancanti, come sys.dm_db_missing_index_details e sys.dm_db_missing_index_group_stats

Infine se a tutte le query viste aggiungiamo un’ulteriore CROSS APPLY, questa volta alla sys.dm_exec_sql_text, possiamo facilmente risalire allo statement associato all’Execution Plan; in alternativa possiamo usare sempre l’XMl dell’Execution Plan per estrarre questa informazione, aggiungnendo un campo alle varie query con questa forma

qp.query_plan.value('
(//StmtSimple/@StatementText)[1]' , 'varchar(max)') as [text]