M365Con 2027 – conferenza online gratuita · 11–22 Jan 2027 · 120 sessions m365con.net

Parameter sniffing

Concetto

Comportamento dei motori in stile SQL Server che compilano un piano per i valori dei parametri visti al momento della compilazione e riutilizzano il piano in cache, il che può dare prestazioni scarse per valori dei parametri molto diversi; detto anche sensibilità dei parametri.

✦ CONCETTOParameter sniffingM365.FM
Title graphic: Parameter sniffing (Concetto)

Descrizione

Parameter sniffing è un comportamento dei motori di database in stile SQL Server in cui un piano di query viene compilato per i valori dei parametri visti al momento della compilazione, e il piano in cache viene poi riutilizzato. È detto anche sensibilità dei parametri. Il comportamento non è di per sé un difetto: il motore ispeziona i valori correnti dei parametri durante la compilazione o la ricompilazione di una query e li passa all'ottimizzatore delle query, che può quindi produrre un piano di esecuzione più efficiente per quei valori. La difficoltà emerge quando il piano in cache viene successivamente usato per valori dei parametri che sembrano molto diversi da quelli originali, perché un piano adatto a un valore può avere prestazioni scarse per un altro.

La guida all'architettura di elaborazione delle query di SQL Server di Microsoft descrive il parameter sniffing in una sezione dedicata alla sensibilità dei parametri. Secondo tale guida, il processo si applica alle stored procedure, alle query eseguite tramite sp_executesql e alle query preparate. Interagisce direttamente con il riutilizzo dei piani, perché i piani compilati sono memorizzati nella plan cache e possono essere ripresi dalle esecuzioni successive. Il primo insieme di valori che innesca una compilazione determina quindi il piano che le chiamate successive ricevono, ed è per questo che i risultati possono variare a seconda di quale chiamata abbia compilato il piano.

La guida indica un rimedio: l'hint di query OPTION (RECOMPILE). Con questo hint, l'ottimizzatore tratta i parametri e le variabili locali come costanti in fase di compilazione, che possono essere sostituite con dei letterali. I valori usati sono quelli in vigore immediatamente prima dell'esecuzione dell'istruzione, che non sono necessariamente i valori originariamente passati. Ciò può essere utile quando i dati di input sono molto variabili o sbilanciati. La guida avverte anche che l'hint ricompila la query a ogni esecuzione, il che può essere costoso, e consiglia di misurare l'effetto sull'intero carico di lavoro prima di adottarlo. La guida rimanda inoltre a un articolo separato sull'ottimizzazione Parameter Sensitive Plan per ulteriori approfondimenti.

Un dettaglio correlato della stessa documentazione riguarda le variabili locali. I loro valori non vengono analizzati (sniffed) in fase di compilazione. Piuttosto, l'ottimizzatore effettua stime basate sulle informazioni di densità o, quando non sono disponibili statistiche, utilizza stime percentuali fisse per i confronti di uguaglianza e di intervallo. Questa distinzione è importante quando si diagnostica una query lenta, perché un parametro e una variabile locale possono portare a stime e quindi a piani diversi.

Nella pratica, il parameter sniffing si manifesta con query che girano velocemente per alcuni input e lentamente per altri, oppure con prestazioni che cambiano dopo che un piano viene rimosso dalla cache e ricompilato. L'episodio di M365.FM "Fix Bad Data Upstream with T-SQL Contracts" elenca il parameter sniffing tra i comuni errori aziendali, descrivendolo come qualcosa che può mascherarsi da casualità. L'episodio inquadra inoltre la forma dei piani come una questione di governance, sostenendo che se la forma dei piani cambia, cambiano con essa costi e affidabilità, sicché il comportamento dei piani si traduce in stabilità dei costi e rischio operativo.

L'episodio "Design Reliable and Resilient Azure Architectures" colloca il parameter sniffing in un flusso di risoluzione dei problemi per Azure SQL Database. Suggerisce di abilitare Intelligent Insights e Query Performance Insight, catturare le query a lunga esecuzione, esaminare i piani di esecuzione e identificare indici mancanti o parameter sniffing. Menziona inoltre le raccomandazioni di tuning automatico di Azure SQL e i suggerimenti sugli indici tramite Azure Advisor, insieme all'ottimizzazione delle query, a un indicizzazione appropriata e alla valutazione del ridimensionamento di DTU o vCore o del passaggio a Hyperscale.

Nel complesso, le fonti trattano il parameter sniffing come un compromesso della cache dei piani: riutilizzare un piano compilato fa risparmiare lavoro di compilazione, mentre un piano ottimizzato per un valore di parametro può non essere adatto ad altri. Le decisioni tipiche che lo riguardano comportano l'esame dei piani di esecuzione, la verifica se la distribuzione dei dati è sbilanciata e la valutazione del costo di compilazione di opzioni come OPTION (RECOMPILE) rispetto al vantaggio di piani più adatti.

Panoramica compilata con l'AI dagli episodi di M365.FM e dai dati di questa pagina. Puoi migliorarla con il pulsante di modifica qui sotto.

TipoConcetto

Domande frequenti

Le domande contrassegnate con AI sono state scritte con l'AI a partire dai fatti riportati in questa pagina.

Che cos'è il parameter sniffing? (AI)

È il comportamento dei motori in stile SQL Server che compilano un piano di query per i valori dei parametri visti al momento della compilazione e riutilizzano il piano in cache. Ciò può dare prestazioni scarse quando i valori successivi dei parametri differiscono molto. È detto anche sensibilità dei parametri.

In che modo OPTION (RECOMPILE) può influire sui problemi di parameter sniffing? (AI)

Secondo la guida al processo delle query di Microsoft, l'hint fa sì che l'ottimizzatore tratti i parametri e le variabili locali come costanti in fase di compilazione, che possono essere sostituite con dei letterali, il che può aiutare con dati molto variabili o sbilanciati. La query viene ricompilata a ogni esecuzione, il che può essere costoso, quindi è opportuno misurare prima l'effetto sull'intero carico di lavoro.

Le variabili locali vengono analizzate nello stesso modo dei parametri? (AI)

No. La documentazione afferma che i valori delle variabili locali non vengono analizzati in fase di compilazione. L'ottimizzatore effettua invece stime basate sulle informazioni di densità oppure, in assenza di statistiche, utilizza stime percentuali fisse per i confronti di uguaglianza e di intervallo.

Glossario

Community wiki

Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.

Esperti e aziende per questo argomento

Nessuno ha ancora indicato questo argomento. Aggiungilo al tuo profilo.

Mantenuto da Mirko Peters (Microsoft MVP) · Ultimo aggiornamento: