Se un tuo amico ti chiedesse una mano per un trasloco dicendoti che ha solo poche scatole, e tu scoprissi poi di dover trasportare otto elettrodomestici, due lavatrici e quattro armadi… cosa gli diresti? Beh oltre ad un’amicizia rovinata, forse la tua piccola auto non ti sarebbe più tanto d’aiuto.

SQL Server fa una cosa simile quando deve eseguire le tue query. Quando tu gli chiedi di estrarre pochi (o molti) dati, semplicemente SQL Server non ti crede (e dovevi farlo anche tu col tuo amico…). Verifica da solo la vera mole del lavoro e poi decide se aiutarti con una piccola utilitaria o con una flotta di camion.

Tramite un componente chiamato Query Optimizer, il motore genera un piano di esecuzione: in pratica identifica la sequenza di operazioni migliore (cioè quella che richiede meno tempo e risorse) per restituirti i dati richiesti in base a quella che pensa sarà la difficoltà del singolo caso.

E fare questo non è una cosa semplice! Sono coinvolti tantissimi fattori come la distribuzione dei dati, la cardinalità, gli indici, le statistiche, eccetera. Ciò nonostante, il query optimizer svolge il suo lavoro egregiamente, valutando ad ogni esecuzione il piano migliore per la query.

Ti faccio un esempio basato su un database reale: come vedi sotto ci sono due query esattamente uguali tranne che per il valore del filtro nella clausola “where”.

-- query 1
select *
from Testo t 
where t.NomeTabella = 'dbo.Articoli'
 
-- query 2
select *
from Testo t 
where 1=1 t.NomeTabella = 'dbo.Dizionario'

Ti aspetteresti il solito piano d’esecuzione? Beh, in realtà, a causa della distribuzione dei dati e dei fattori detti sopra, il Query Optimizer realizza due piani d’esecuzione differenti per la prima e per la seconda query! Eccoli qui sotto!

Come vedi i due piani d’esecuzione sono completamente diversi. Pur essendoci un indice non clustered proprio sulla colonna del filtro, dato l’alto numero di risultati attesi, la prima query opta per una scansione di tutto l’indice clustered. La seconda query, attendendo un numero molto basso rispetto al totale di righe invece, punta tutto sull’indice presente sul filtro per poi trovare l’intero record con i dati.

Il problema è quando tu pretendi di parametrizzare la query ed inserirla in una procedura! Qui son dolori! Perchè? Perchè si verifica un fenomeno che può rallentare notevolmente l’esecuzione della tua procedura!

Lo strano caso del parameter sniffing

Abbiamo detto quindi che vogliamo parametrizzare il tutto e creiamo la seguente procedura:

create procedure Sp_GetTraduzioniByNomeTabella
    @nomeTabella varchar(100)
as 
begin 
    set nocount on;
    
    select *
    from dbo.Testo t
    where t.NomeTabella = @nomeTabella -- ecco il nostro parametro!
    
end

Come vedi quindi adesso abbiamo parametrizzato la query inserendo “nomeTabella” come variabile in ingresso. Adesso ti aspetteresti che eseguendo la stored procedure in sostituzione delle due query sopra tu possa avere i soliti risultati. Giusto?

Allora eseguiamo la stored procedure come nell’ordine riportato sotto e vediamo i risultati…

-- prima chiamata
exec Sp_GetTraduzioniByNomeTabella 'dbo.Dizionario'
 
-- seconda chiamata
exec Sp_GetTraduzioniByNomeTabella 'dbo.Articoli'

Calma qualcosa non torna! Ma il filtro su ‘dbo.Articoli’ non doveva generare un piano diverso? Si amico mio, DOVEVA! Perchè quando compili una procedura e la esegui per la prima volta entri nel fantastico mondo del plan caching! In pratica SQL Server memorizza il primo piano d’esecuzione calcolato e lo riutilizza sempre, per migliorare le performance senza doverlo ricreare ogni volta!

Ed il risultato a volte è… UN PASTICCIO!

Ma allora quando il parameter sniffing diventa un problema?

Diventa un problema quando viene usato un piano d’esecuzione concepito per una situazione diversa, e di conseguenza le prestazione decadono pietosamente. Tu dirai: “ma sei sicuro”? Ok, allora facciamo una prova, ricompiliamo la procedura senza cambiare nulla e rilanciamola, questa volta passando all’inizio il valore ‘dbo.Articoli’ come parametro…

Et voilà! Non c’è trucco non c’è inganno signori!

E adesso?

Be, l’idea di eseguire la cache del piano di esecuzione di per se non è il male assoluto: è una funzionalità del motore di SQL Server ed è utile in molte situazioni. Ma in alcuni casi questo diventa un problema, e allora come puoi fare ad evitare la catastrofe e che le tue query non diano più segni di vita? Una possibilità (ma non è l’unica) è chiedere al motore di ricompilare la query ogni volta che viene invocata la procedura come indicato qui sotto tramite l’istruzione “WITH RECOMPILE”.

create or alter procedure Sp_GetTraduzioniByNomeTabella
    @nomeTabella varchar(100)
    WITH RECOMPILE -- hint 
as 
begin 
    set nocount on;
    select *
    from dbo.Testo t
    where t.NomeTabella = @nomeTabella
end

Questa è un’opzione ma devi capire che il problema non nasce da un “bug” di SQL Server. Nasce dalla disposizione dei tuoi dati.

In tutto questo però, nell’edizione 2022 di SQL Server c’è un’interessante novità!

SQL Server 2022 to the rescue!

Sarai contento di sapere che nell’edizione (non ancora rilasciata) di SQL Server 2022 ci sarà un’interessante feature: il motore eseguirà il cache di più piani d’esecuzione associandoli a diversi parametri. Così sarà possibile utilizzare il piano d’esecuzione più adatto al parametro passato!

Nel mio percorso ho raccolto molte informazioni interessanti in merito agli argomenti di cui ti ho parlato in questo articolo. Per questo ho scritto un libro intitolato “Why Your Data Matter”.

Essendo il frutto della mia passione ed esperienza diretta, ho scelto di mettere questo libro gratuitamente a disposizione di tutti gli IT Manager ed i CIO delle aziende che come te vogliono ottenere grandi risultati dalle loro scelte e dal loro lavoro (evitando di trovarsi in situazioni scomode e da risolvere con urgenza).

Ti invito a leggere le prime pagine scaricandole!
Se poi ti piacerà sarò felice di inviartene una copia GRATUITA direttamente nel tuo ufficio.  

Clicca qui per scaricare l’estratto del mio libro (se ti piacerà te lo invierò in formato cartaceo!) ==> il mio libro  

Alla prossima informazione!