The complaint always sounds the same: "the report is instant for me, but it takes two minutes for the client with the big account." Nobody changed the query. Nobody changed the index. What changed is which customer's data happened to be sitting in cache the first time that stored procedure compiled, and SQL Server has been reusing that one plan for everyone ever since.
Why SQL Server Does This on Purpose
Compiling a plan is expensive, so SQL Server caches the plan for a parameterized query or stored procedure the first time it runs and reuses it on subsequent calls, only the parameter values change, the plan doesn't recompile. To build that first plan, the optimizer looks at the actual parameter values passed in that first call and estimates row counts based on statistics for exactly those values. This is called parameter sniffing, and most of the time it's exactly the optimization you want: why pay the compilation cost twice for the same query shape?
It stops being helpful when the data is skewed. A plan optimized for a customer with 40 orders (a nested loop, small memory grant) is a disaster for a customer with 4 million orders (needs a hash join, a large memory grant). Whichever parameter value compiled the plan first decides which customer gets the good experience.
How to Recognize It
- The same stored procedure has wildly inconsistent duration depending on which parameters were passed, not depending on server load.
- A query "was fine yesterday, terrible today" with no code or index changes, often right after a service restart, index rebuild, or
sp_recompileflushed the cached plan and a different parameter value happened to trigger the next compile. - The actual execution plan shows a large gap between estimated and actual rows on an operator, that gap is the signature of a plan compiled for a different data distribution than the one it's currently running against.
DBCC FREEPROCCACHE (on a test system, never blindly in production) using the parameter that's slow, then again with the parameter that's fast. If the two runs produce different plan shapes for the same query text, that's parameter sniffing, not a missing index.
The Fixes, and When Each One Applies
| Approach | What it does | Best for |
|---|---|---|
OPTION (RECOMPILE) |
Recompiles the query every execution using that call's actual parameter values | Queries run infrequently enough that compile cost is cheap compared to a bad plan |
OPTIMIZE FOR a specific value |
Always compiles as if a chosen "representative" value were passed | One value clearly represents the typical case and you're willing to accept worse performance for outliers |
OPTIMIZE FOR UNKNOWN |
Compiles using average statistics density instead of any specific value | Data is fairly evenly distributed and you want a consistent, middling plan rather than a great-or-terrible one |
| Split into separate procedures | Route small vs. large customers to different stored procedures, each gets its own cached plan | You can clearly identify the skew ahead of time (e.g. by account size or tenant) |
| Query Store forced plan | Pins a known-good plan regardless of which parameters compiled it | You've already identified the plan that performs acceptably across the board |
OPTION (RECOMPILE) is not free. It solves the bad-plan problem by paying the compilation cost on every single call. For a query that runs thousands of times a second, that trade is almost always wrong, the CPU cost of constant recompilation can dwarf whatever the bad plan was costing. Reserve it for queries that run occasionally, not hot-path OLTP procedures.
Common Gotchas
- Blaming the index when it's the plan. Adding another index doesn't fix parameter sniffing if the optimizer would still pick a mismatched join strategy or memory grant for the skewed value, verify it's actually a sniffing problem before reaching for index changes.
- Local variables "fixing" it by accident. Assigning parameters to local variables inside the procedure disables sniffing entirely (the optimizer can't see the actual value, so it estimates from statistics density), this can look like a fix but just replaces one uncontrolled behavior with another, use
OPTIMIZE FOR UNKNOWNinstead if that's the effect you actually want, it's the same idea done deliberately. - Ad-hoc queries aren't immune. Parameter sniffing is usually discussed in terms of stored procedures, but any parameterized query plan, including auto-parameterized ad-hoc statements, can be affected the same way.
- Testing with a cold cache masks the problem. If you only ever test right after a deployment or restart, when the cache is empty and your test data compiles the plan, you'll never see the failure mode a real customer's parameter value would trigger.
The Bottom Line
Parameter sniffing isn't a bug, it's the plan cache doing exactly what it's designed to do: compile once, reuse often. The problem only shows up when your data is skewed enough that no single plan is good for everyone. Diagnose it by comparing estimated to actual rows in the plan, then pick the narrowest fix that solves it, OPTIMIZE FOR a representative value or a Query Store forced plan, before reaching for the blunter and more expensive OPTION (RECOMPILE).
For the parameter fundamentals underneath sniffing, OUTPUT parameters, table-valued parameters, and the data type mismatch that quietly kills an index seek, see Stored Procedure Parameters in SQL Server, Explained.