Per ottimizzare le performance, Sql Server memorizza il piano di esecuzione di una stored procedure alla sua prima esecuzione. Il parameter sniffing è quel fenomeno per cui lo stesso piano di esecuzione sarà utilizzato anche nei successivi run, nonostante ne esisterebbero altri più efficienti al variare dei valori dei parametri passati in input.

Esempio di parameter sniffing

Consideriamo una stored procedure con un parametro di input. All’interno della procedura è presenta una query che utilizza il parametro per eseguire un filtro su una colonna. Supponiamo inoltre che tale colonna sia indicizzata con un nonclustered index. In base alle statistiche del database, il piano di esecuzione migliore per la query potrebbe essere diverso al variare del parametro: ad esempio in alcuni casi potrebbe essere previsto l’utilizzo dell’indice non clustered (se il filtro è abbastanza selettivo), mentre in altri no (se i costi delle operazioni di key o rid lookup sono troppo alti). Questo comportamento è descritto ad esempio nel paragrafo “Analisi del piano di esecuzione di query con filtri e indici non clustered” di questo articolo https://www.yimp.it/piano-di-esecuzione-di-una-query/

Se il codice è incapsulato in una Stored Procedure, SQL Server salverà nella sua cache il piano d’esecuzione. In questo modo, nelle esecuzioni successive risparmierà il tempo necessario per la scelta del piano con conseguente miglioramento generale delle performance. Tuttavia, in un caso particolare come quello descritto in alto, il piano d’esecuzione scelto per la prima esecuzione potrebbe non essere quello più adatto per esecuzioni successive con valori differenti nel parametro. Questo succederà fin quando il piano resterà salvato nella cache, la cui durata può essere influenzata da più fattori.

Come affrontare il parameter sniffing

Esistono vari metodi per affrontare il parameter sniffing, vi riporto dei link ad articoli autorevoli sul web:

• https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
• https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
• https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/parameter-sniffing/

A mio parere il metodo che spesso raggiunge il giusto compromesso tra facilità di implementazione e risultato è aggiungere l’opzione RECOMPILE a quelle query della stored procedure che utilizzano il parametro e per cui il parameter sniffing genera problemi di performance.

Attenzione: l’Hint può essere relativo anche alla sola query che utilizza il parametro, non solo all’intera procedura. Attenzione comunque a non esagerare: in generale la possibilità di non dover ricalcolare ogni volta il piano di esecuzione è una feature importante per le performance di SQL Server, soprattutto in sistemi in cui sono eseguite contemporaneamente molte query e procedure.

Ad esempio, in casi di problemi possiamo valutare di aggiungere l’Hint come in questo caso

CREATE PROCEDURE dbo.Ricerca 
 @ParProdotto INT
AS
 SELECT * 
 FROM   TabellaTest 
 WHERE  IdProdotto = @ParProdotto 
 OPTION (RECOMPILE);

Continua a imparare

• Analizziamo i tempi d’attesa di SQL Server con dm_os_wait_stats
• Studiamo alcune tecniche per migliorare le performance delle query
• Se cerchi un corso che tratti tematiche avanzate su Sql Server puoi visitare questa pagina https://www.yimp.it/corso-t-sql-avanzato/