M365Con 2027 – free online conference · 11–22 Jan 2027 · 120 sessions m365con.net

Parameter sniffing

Concept

Behavior of SQL Server-style engines that compile a plan for the parameter values seen at compile time and reuse the cached plan, which can perform poorly for very different parameter values; also called parameter sensitivity.

✦ CONCEPTParameter sniffingM365.FM
Title graphic: Parameter sniffing (Concept)

Description

Parameter sniffing is a behavior of SQL Server-style database engines in which a query plan is compiled for the parameter values seen at compile time and the cached plan is then reused. It is also called parameter sensitivity. The behavior is not a defect in itself: the engine inspects the current parameter values while compiling or recompiling a query and passes them to the query optimizer, which can then produce a more efficient execution plan for those values. The difficulty appears when the cached plan is later used for parameter values that look very different from the original ones, because a plan that suits one value can perform poorly for another.

Microsoft's query processing architecture guide for SQL Server describes parameter sniffing in a section on parameter sensitivity. According to that guide, the process applies to stored procedures, queries run through sp_executesql, and prepared queries. It interacts directly with plan reuse, because compiled plans are stored in the plan cache and can be picked up again by later executions. The first set of values that triggers a compilation therefore shapes the plan that subsequent calls receive, which is why results can vary depending on which call happened to compile the plan.

The guide names one remedy: the OPTION (RECOMPILE) query hint. With this hint, the optimizer treats parameters and local variables as compile-time constants, which can be folded into literals. The values used are those in effect right before the statement runs, which are not necessarily the values originally passed in. This can help when input data is highly variable or skewed. The guide also warns that the hint recompiles the query on every execution, which can be expensive, and advises measuring the effect on the whole workload before adopting it. The guide also points to a separate article on the Parameter Sensitive Plan optimization for further reading.

A related detail from the same documentation concerns local variables. Their values are not sniffed at compile time. Instead, the optimizer estimates from density information, or, when no statistics are available, uses fixed percentage guesses for equality and range comparisons. This distinction matters when diagnosing a slow query, since a parameter and a local variable can lead to different estimates and therefore different plans.

In practice, parameter sniffing shows up as queries that run fast for some inputs and slowly for others, or as performance that changes after a plan is evicted and recompiled. The M365.FM episode "Fix Bad Data Upstream with T-SQL Contracts" lists parameter sniffing among common enterprise failures, describing it as something that can masquerade as randomness. The episode also frames plan shapes as a governance matter, saying that if plan shapes change, spend and reliability change with them, so that plan behavior translates into cost stability and operational risk.

The episode "Design Reliable and Resilient Azure Architectures" places parameter sniffing in a troubleshooting workflow for Azure SQL Database. It suggests enabling Intelligent Insights and Query Performance Insight, capturing long-running queries, reviewing execution plans, and identifying missing indexes or parameter sniffing. It further mentions Azure SQL's automatic tuning recommendations and index suggestions via Azure Advisor, along with query optimization, appropriate indexing, and considering scaling DTUs or vCores or moving to Hyperscale.

Taken together, the sources treat parameter sniffing as a plan-caching trade-off: reusing a compiled plan saves compilation work, while a plan tuned to one parameter value may not suit others. Typical decisions around it involve examining execution plans, checking whether data distribution is skewed, and weighing the compile cost of options such as OPTION (RECOMPILE) against the benefit of more suitable plans.

Overview compiled with AI from M365.FM episodes and the facts on this page. You can improve it with the edit button below.

TypeConcept

Frequently asked questions

Questions marked with AI were written with AI from the facts on this page.

What is parameter sniffing? (AI)

It is the behavior of SQL Server-style engines that compile a query plan for the parameter values seen at compile time and reuse the cached plan. This can perform poorly when later parameter values differ greatly. It is also called parameter sensitivity.

How can OPTION (RECOMPILE) affect parameter sniffing problems? (AI)

According to Microsoft's query processing guide, the hint makes the optimizer treat parameters and local variables as compile-time constants that can be folded into literals, which can help with highly variable or skewed data. The query is recompiled on every execution, which can be expensive, so the effect on the whole workload should be measured first.

Are local variables sniffed in the same way as parameters? (AI)

No. The documentation states that values of local variables are not sniffed at compile time. The optimizer instead estimates from density information or, without statistics, uses fixed percentage guesses for equality and range comparisons.

Glossary

Community wiki

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

Experts and companies for this topic

Nobody has listed this topic yet. Add it to your profile.

Maintained by Mirko Peters (Microsoft MVP) · Last updated: