A SQL Server behavior where the query optimizer creates an execution plan based on the parameter values supplied during the first compilation of a stored procedure or parameterized query, which may perform poorly when different parameter values are used in later executions.
- Feature, not a bug: Parameter sniffing is designed to improve performance by creating execution plans tailored to the initial parameter values. It only becomes problematic when the cached plan is unsuitable for most subsequent executions.
- Common symptoms: A stored procedure may execute quickly for one set of parameter values but perform poorly for others, or query performance may change unpredictably after SQL Server restarts and recompiles execution plans.
- Diagnosis: DBAs identify parameter sniffing issues by comparing estimated and actual row counts in execution plans. Significant differences often indicate that the optimizer selected a plan based on non-representative parameter values.
-
Mitigation techniques: Common solutions include using
OPTION (RECOMPILE)to generate a fresh execution plan for each run, applyingOPTIMIZE FORhints, assigning parameter values to local variables to prevent sniffing, or forcing a stable execution plan through Query Store. - SQL Server 2022 improvements: Parameter Sensitive Plan Optimization, introduced as part of Intelligent Query Processing (IQP), automatically creates multiple execution plans for a single query, each optimized for different parameter value ranges, providing a more advanced solution to parameter sniffing.
- Related terms: Execution Plan, Query Store, Intelligent Query Processing, Automatic Tuning.
