Parameter sniffing is when SQL Server's query optimizer builds an execution plan based on the parameter values supplied on a query's first execution, caches that plan, and reuses it for later executions — even when different parameter values would perform better.
It is a performance feature by design. It becomes a problem only when the first-seen values produce a plan that is inefficient for other values used later, which shows up as a procedure that is fast sometimes and slow other times.
Parameter sniffing is one of the most common — and most misunderstood — causes of inconsistent stored procedure performance. This guide explains why it happens, how to confirm it is your issue, and the trade-offs of every standard fix.
Why parameter sniffing happens
When you execute a parameterized query or stored procedure for the first time, SQL Server compiles an execution plan optimized for the specific values you passed, then caches that plan and reuses it for subsequent calls. Per Redgate's Simple Talk, this reuse is the point: recompiling a plan on every execution would be wasteful, so "sniffing" the parameters once and caching the result is normally a win.
The trouble starts when data is skewed. Suppose a procedure filters orders by customer. For a customer with 5 orders, a plan that seeks an index and does key lookups is ideal. For a customer with 5 million orders, that same plan is catastrophic — a scan would have been far cheaper. Whichever customer is passed first dictates the cached plan for everyone, and as Brent Ozar describes it, that is the elephant getting the mouse's plan, or the mouse getting the elephant's.
Parameter sniffing is not a bug. It is the default behavior of plan caching. The goal is not to eliminate it everywhere — it is to stop a single cached plan from hurting the values it does not fit.
How to confirm it is your problem
The signature symptom of a sniffing problem is a procedure whose speed depends on which parameters it was first compiled for. Watch for these tells:
- The procedure is fast for some inputs and slow for others, with no obvious difference in the work involved.
- Performance flips after a plan eviction, a statistics update, an index rebuild, or a server restart — anything that forces a recompile on different first values.
- The same query runs fast when you copy it out and run it ad hoc, but slow inside the procedure.
To confirm it directly, compare the cached plan's estimated row counts against the actual rows for a slow parameter set: a large divergence means the plan was built for a different cardinality. A quick practical test is to add OPTION (RECOMPILE) to the slow call — if it suddenly runs fast, the cached plan was the culprit.
Quick diagnostic: does a fresh plan fix the slow call?EXEC dbo.GetOrdersByCustomer @CustomerId = 42
WITH RECOMPILE; -- forces a one-off fresh plan for these values
-- If this is dramatically faster than the cached call,
-- parameter sniffing is the likely cause.
The standard fixes and their trade-offs
There is no universal best fix — the right choice depends on how often the procedure runs and how widely the optimal plan varies across parameter values. The main options:
| Fix | What it does | Best when | Cost |
|---|---|---|---|
OPTION (RECOMPILE) |
Builds a fresh, value-specific plan on every execution. | Executions are infrequent and the optimal plan varies widely. | Compilation overhead on every call. |
OPTIMIZE FOR (@p = value) |
Pins the plan to a representative value you choose. | One common value represents most calls. | Atypical values may still get a poor plan. |
OPTIMIZE FOR UNKNOWN |
Uses average column statistics instead of the actual value. | General-purpose procedures with no dominant value. | A compromise plan that is rarely the best for anyone. |
| Local variables | Assigning parameters to local variables hides the values from the optimizer (behaves like UNKNOWN). | A pragmatic, version-portable workaround. | Same compromise-plan downside; can be surprising to maintainers. |
| Specialized procedures | Split into separate procedures so each value range gets its own plan. | A few discrete ranges need fundamentally different plans. | More code to maintain. |
Microsoft and community practitioners agree there is no one-size-fits-all answer: the fix has to match the workload. Plan-stability features such as Query Store plan forcing (and automatic plan correction on supported versions) can also pin a known-good plan once you have identified one.
Resist the reflex to slap WITH RECOMPILE on the whole procedure. On a frequently called procedure, recompiling every execution can cost more than the sniffing it cures. Match the fix to the call pattern.
How automated optimization handles sniffing
Detecting parameter sniffing is exactly the kind of pattern an AI analysis step does well: given the execution plans, statistics, and the distribution of parameter values seen in a captured workload, a model can flag a plan that fits one cardinality but not the values actually being passed, and propose the fix whose trade-offs match the call frequency.
SprocOptimizer includes parameter-sniffing detection in its analysis stage — it is one of the recommendation types the pipeline produces. As with any rewrite, a proposed fix is then checked for logical equivalence and validated against your real parameter sets before it can be promoted, so a sniffing "fix" can never silently change results.
This concept sits inside the bigger picture: see the complete guide to stored procedure optimization.
Frequently asked questions
Parameter sniffing is when SQL Server's query optimizer builds an execution plan based on the parameter values supplied on a query's first execution, caches that plan, and reuses it for later executions — even when different parameter values would perform better. It becomes a problem when one set of values produces a plan that is inefficient for other commonly used values.
No. Parameter sniffing is a performance optimization by default — caching and reusing a compiled plan avoids recompiling on every call. It only becomes a problem when the cached plan is well suited to the first parameter values but poorly suited to other values used later, causing inconsistent performance.
The classic symptom is a procedure that is fast for some parameter values and slow for others, or that suddenly becomes slow after a plan recompiles. Confirm it by comparing the cached plan's estimated row counts against the actual rows for the slow parameters, or by testing whether OPTION (RECOMPILE) makes the slow call fast — if it does, the cached plan is the cause.
There is no single best fix; it depends on the workload. OPTION (RECOMPILE) gets a fresh optimal plan every call at the cost of compilation overhead. OPTIMIZE FOR a representative value pins a known-good plan. OPTIMIZE FOR UNKNOWN uses average statistics. Local variables obscure the values from the optimizer. Splitting into specialized procedures gives each value range its own plan. Choose based on call frequency and how widely the optimal plan varies.
Primary sources & further reading
- Redgate Simple Talk — SQL Server Parameter Sniffing: What It Is and How to Fix It.
- Brent Ozar — The Elephant and the Mouse, or Parameter Sniffing in SQL Server.
- Microsoft Q&A — How to determine a parameter sniffing problem and how to handle it.
- MSSQLTips — Different approaches to correct SQL Server parameter sniffing.
Catch sniffing before your users do
SprocOptimizer flags parameter-sniffing patterns from a captured workload, proposes a fix, and validates it against your real parameters — on-premises, with no row-level data leaving your network.
Request a Demo Read the Optimization Guide