Capita che le performance di una query decadano nel tempo a causa della mole di dati crescente, piuttosto che nuove query, magari introdotte da implementazioni software da sviluppare in tempi rapidi le quali potrebbero non essere state testate a dovere sull’ambiente di produzione.
Qualsiasi sia il motivo, spesso il punto migliore da cui partire è la valutazione del piano di esecuzione della query incriminata.
Cos’è il piano di esecuzione di una query e cosa rappresenta
Il piano di esecuzione descrive in maniera schematica come si comporta il motore di SQL Server per eseguire il nostro comando T-SQL.
SQL Server Query Optimizer è la componente dell’engine in grado di generare questa sequenza di azioni.
Come visualizzare il piano di esecuzione
Il piano di esecuzione può essere richiesto in vari modi:
- Utilizzando SQL Management Studio, da interfaccia grafica, premendo il pulsante
- Anteponendo l’istruzione:
Esiste anche la possibilità di calcolare un piano di esecuzione stimato che non esegue effettivamente il comando ma procede esclusivamente alla sua compilazione provando a dare un possibile piano risultante.
Entriamo subito nel vivo dell’argomento preparandoci a lavorare su uno dei database preconfezionati da Microsoft: AdventureWorks.
Proviamo ad eseguire questa semplice query:
select top 100
p.ProductID, p.Name, pc.Name as CategoryName, pm.Name as Model
from
SalesLT.Product p
inner join SalesLT.ProductCategory pc on pc.ProductCategoryID = p.ProductCategoryID
inner join SalesLT.ProductModel pm on pm.ProductModelID = p.ProductModelID
Una volta eseguita la query vedremo che nella parte bassa del Management Studio sarà comparsa la scheda del piano di esecuzione, come evidenziato in figura:
Come si legge un piano di esecuzione
Un piano di esecuzione in genere si legge da destra a sinistra.
Per diagrammi molto grandi dovuti a query complesse, abbiamo in fondo a destra anche un’utile pulsante per permetterci di navigare l’immagine più agilmente.
A partire da destra il diagramma ci mostra come ha deciso di intervenire sugli oggetti del database implicati nel comando, per poi via via ridurre la complessità di quanto verrà eseguito fino ad arrivare al nostro risultato (nel nostro caso la SELECT, nodo più in altro a sinistra).
Ogni oggetto rappresentato porta con sé delle proprietà in grado di farci capire quanto sia efficace l’istruzione che abbiamo scritto. Possiamo infatti fare valutazioni sul numero di records elaborati dalla query, sulla quantità di I/O richiesta, CPU, memoria, ecc…
Scorrendo su qualsiasi icona del diagramma ci viene presentato un report in finestra di dettaglio dell’operazione:
Leggere in maniera utile un piano di esecuzione non è solo una questione di conoscere lo strumento e saper identificare il significato dei vari simboli; a mio avviso è la fusione di due principali componenti: la capacità di dare un significato ai numeri riportati e la conoscenza del database su cui si sta operando.
I principali simboli rappresentati in un piano di esecuzione
| Nome | Significato | |
 | Clustered Index Scan | In questo caso viene letto l’intero clustered index (indice principale della tabella contenente tutti i campi) alla ricerca del record utile a soddisfare le condizioni proposte. Essendo un indice ordinato viene letto per l’appunto nell’ordine del campo oggetto dell’indice; viene utilizzato quando stiamo chiedendo una percentuale di dati troppo elevata da soddisfare con delle ricerche mirate oppure quando l’engine non è in grado di trovare un indice opporturno. Si applica a tabelle con un CLUSTERED INDEX. |
 | Index Scan | Come sopra, ma in questo caso non è un clustered index ma un indice a supporto della tabella. |
 | Table scan | Scansione di un’intera tabella di tipo HEAP (non contiene un indice clustered). In questo caso i dati in genere vengono letti seguendo l’ordine di inserimento e non l’ordine di uno specifico campo. Anche in questa situazione si procede con il leggere tutti i dati contenuti nella tabella fino a raggiungere quelli desiderati proprio perché non sono stati trovati delle strade più efficienti (in genere un indice adatto all’occasione). |
 | Clustered Index Seek | L’operazione di seek percorre puntualmente l’albero binario dell’indice per raggiungere in maniera rapida i dati richiesti. Quasi sempre risulta essere più efficiente di uno Scan proprio perchè non leggiamo l’interezza dell’insieme dei dati. |
 | Index Seek | Stessa operazione del clustered, ma su un indice a supporto della tabella. |
 | Key Lookup | Questa attività entra in gioco quando stiamo lavorando su un indice NON CLUSTERED della tabella e ci serve una “colonna” non inclusa nei dati dell’indice; in questo caso l’engine deve ripercorre i puntamenti dei records trovati sull’indice NON CLUSTERED così da arrivare alle pagine originali da cui recuperare i dati mancanti. L’operazione generalmente è costosa anche se spesso è impossibile creare tutti gli indici necessari al fine di soddisfare tutte le possibili selezioni effettuate su un database. |
 | RID Lookup | La RID Lookup (Row ID Lookup) equivale alla Key Lookup in una HEAP. |
 | Hash Match | Vengono utilizzati per soddisfare i criteri di join. L’engine si costruisce delle hash table in memoria (molto efficienti, vedi: Hash table) per poter trovare corrispondenza tra i records di due tabelle (quelle coinvolte nella join). |
 | Top | Soddisfa la selezione di massimo N elementi, tanti quanti specificati nella clausola TOP. |
 | Select | Generalmente è l’operatore finale del nostro piano (il più altro a sinistra), ci dice che dopo aver fatto tutto quando mostrato sulla destra del piano di esecuzione, l’engine sarà in grado di selezionare il nostro risultato. |
 | Sort | Compare quando viene specificato un’ordinamento nella query |
 | Stream Aggregate | Effettua le operazioni di raggruppamento, ho utilizzato quindi l’istruzione GROUP BY. |
 | Filter | L’istruzione HAVING applicata al GROUP BY causa l’esecuzione di una nuova operazione da parte dell’engine; questo infatti dopo aver raggruppato i dati deve per l’appunto filtrarli ulteriormente. |
Proseguiamo nell’analisi
Da questo momento in poi, una volta visualizzato il piano, non esiste una vera e propria ricetta da seguire per identificare il problema di performance della nostra query. Come scritto prima spesso serve esperienza per identificare punti critici che possiamo ottimizzare; anche la conoscenza del dominio applicativo in genere rende più rapide questi tipi di analisi.
Le prime cose che cerco di identificare sono:
- Ci sono delle “scan”? Se si, sono giustificate? Quanti dati deve leggere l’engine prima di restituire le informazioni richieste?
- Questa ricade nel primo punto, ossia, le clausole di filtro stanno adeguatamente sfruttando gli indici che ho creato?
- Le frecce sono molto importanti, spesso ci mostrano a colpo d’occhio una disparità tra i dati in ingresso e quelli in uscita (ad esempio una freccia molto grossa proveniente da destra, sfocia in un una piccolissima a sinistra dopo magari l’esecuzione di un ulteriore filtro.
- Quante risorse richiede l’istruzione rappresentata da uno dei simboli; magari sto chiedendo troppa memoria ed il server non è in grado di offrirla, oppure sto impegnando così tanto la CPU da non permettere quasi l’esecuzione di altre istruzioni.
- Ci sono delle lookup? Posso rimuoverle togliendo elementi dalla mia selezione?
Questi solo alcuni dei punti che guardo in prima battuta. Dopodiché si entra in analisi ben più complesse che riguardano: parallelismo, parameter sniffing, indici, statistiche, ecc…
Voi cosa verificate inizialmente in un piano di esecuzione? Sono curioso di conoscere il vostro approccio nei commenti.