The order lookup procedure had run in fifty milliseconds for years. Then, one Tuesday at 9:14 AM, an auto statistics update on the Orders table forced a recompile, and the first execution after it came from the nightly report job running for the largest customer — 2.1 million orders out of a 38-million-row table. The new plan was a parallel hash join over a full scan, exactly right for that customer, and it got cached for everybody. By 9:20 the small customers who normally read four hundred orders apiece were each waiting six seconds on a plan built for two million rows, the connection pool was exhausted, and the dashboard said CPU at ninety-four percent with everything queued behind itself.
The incident closed when someone recompiled the procedure by hand and a small customer's parameters happened to arrive first. That is not a fix, that is a coin flip. This piece is what parameter sniffing actually is, why it is the correct default behavior, the four tools that fix it for real, and how to find the procedures quietly suffering from it. Everything here applies to SQL Server 2016 through 2022, with one section specific to 2022's parameter-sensitive plan optimization.
What is parameter sniffing, exactly?
Parameter sniffing is the optimizer reading the literal parameter values of the first execution that triggers a compile, and using those values to estimate row counts and choose the plan — a plan that is then cached and reused for every subsequent value. When a stored procedure compiles, SQL Server does not see @CustomerId as an opaque variable. It sees the number 1187, looks up that value in the column histogram, learns that customer 1187 owns about four hundred rows, and picks an index seek with a key lookup. The next caller passes the customer with 2.1 million orders, and the seek plan dutifully performs 2.1 million key lookups, because nothing re-estimates at execution time. The plan was compiled once, against one value, and it is the plan until it leaves the cache.
The triggers worth memorizing: first execution after service start, after an explicit recompile, after the plan ages out of cache, and after a statistics update invalidates the plan — which is what happened to me at 9:14 AM. Any of these resets the dice, and whichever parameter values show up first own the plan.
Why is sniffing usually a feature, and when is it a bug?
It is a feature because value-specific estimates beat average-case estimates almost every time. The alternative is the density vector: the optimizer knows the CustomerId column averages roughly a thousand rows per value, and without a sniffed value it would plan for that average every single execution. For uniformly distributed data the average is fine and sniffing changes nothing. For real data — the kind where one customer is five thousand times bigger than the median — the sniffed value produces a genuinely better plan for the values that resemble it. Sniffing is the optimizer trying to help, and most of the time it does.
It becomes a bug under three recognizable patterns. First, skewed data: one plan cannot serve both the four-hundred-row customer and the two-million-row customer, so whichever compiles first makes the other side miserable, and the side that suffers is whichever one you care about more. Second, optional parameters: a search procedure with ten nullable parameters, where WHERE clauses read like (@Status IS NULL OR Status = @Status), compiles one plan that must serve every combination of supplied and absent filters — no single plan is right for all of them, and the first caller's combination wins. Third, the catch-all query built from dynamic SQL concatenation, which at least gets a fresh plan per shape but pays a compile per shape instead. If your incident smells like any of these three, you are in the right article.
Which fix belongs in the toolbox, and what does each cost?
Four tools, four different trade-offs. OPTIMIZE FOR pins the compile to a value you choose: OPTION (OPTIMIZE FOR (@CustomerId = 1187)) compiles every execution as if the typical small customer had called, forever. It is deterministic and free at runtime, and it is the right answer when one value profile is what you want to serve — you are deliberately sacrificing the whale's forty seconds to protect forty thousand small customers. Its cost is maintenance: the pinned value is a bet about your data, and data drifts. OPTIMIZE FOR UNKNOWN is the blunter sibling: it tells the optimizer to sniff nothing and plan from the density vector, the average case, for every execution. Deterministic, no runtime cost, and frequently good enough — but for badly skewed data the average case can be the plan that is merely mediocre for everyone instead of great for most.
CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
@CustomerId int
AS
BEGIN
SELECT o.OrderId, o.OrderDate, o.Total
FROM dbo.Orders AS o
WHERE o.CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1187));
END;RECOMPILE goes the other direction: WITH RECOMPILE on the procedure, or OPTION (RECOMPILE) on the one problem statement, throws the plan away and compiles fresh against the actual values on every single execution. The estimates are always right, and the statement-level form even lets the optimizer treat the current values as constants inside the plan. The cost is a compile per execution — trivial for a report running twice a day, and a genuine CPU tax for a procedure called two thousand times a second, where compilations can land in the double-digit percent of total CPU. My rule: statement-level RECOMPILE for anything called fewer than a few hundred times a minute, and never for the hot path without measuring compile CPU first. The local variable trick is the folk remedy: copy the parameter into a local variable and filter on that instead, and the optimizer cannot sniff it, so it falls back to the density vector — which makes it OPTIMIZE FOR UNKNOWN with extra steps and none of the clarity. It works for exactly that reason, and I still avoid it in new code, because the next person to read the procedure cannot tell the indirection was load-bearing. If you want unknown, say UNKNOWN.
Does SQL Server 2022 fix this for me?
Partially, through parameter-sensitive plan optimization, and only at compatibility level 160 where it is on by default. PSP lets the engine cache multiple plan variants for one parameterized query — an initial dispatcher picks between variants based on which selectivity bucket the runtime parameter value falls into, so the small customer can get the seek plan while the whale gets the hash join, from the same procedure, without hints. It is the first engine-level answer to the skewed-data pattern rather than a workaround for it.
The honest caveats keep it from being a free lunch. The eligibility rules in the initial release are strict — it targets skewed equality predicates with usable statistics, and plenty of real sniffing cases, including the optional-parameter catch-all, fall outside them. The multi-plan cache also makes plan analysis noisier: one query now owns several plans, which you will see in Query Store as plan variants under one query id. You can force it off per statement with the DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION use hint, which tells you Microsoft expected edge cases. My posture: on compat 160, leave it on, and keep the toolbox for the cases it does not catch — which, so far, is most of the ugly ones.
How do I find the procedures quietly suffering from this?
Look for variance, not slowness. A sniffed-bad procedure has a distinctive signature: the same query text with executions that differ by two or three orders of magnitude in duration depending on the parameter values, and often two plan shapes in cache at different times. Query Store makes this a one-query answer — group runtime stats by plan and sort by the ratio of max to average duration:
SELECT q.query_id,
p.plan_id,
qt.query_sql_text,
rs.count_executions,
rs.avg_duration / 1000.0 AS avg_ms,
rs.min_duration / 1000.0 AS min_ms,
rs.max_duration / 1000.0 AS max_ms
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
WHERE rs.count_executions > 50
AND rs.max_duration > 100 * rs.avg_duration
ORDER BY rs.max_duration / rs.avg_duration DESC;Anything near the top of that list with a parameter in the text is a suspect; cross-check by looking at whether the same query id owns multiple plan ids with different operators — a seek plan and a hash plan under one query is the smell. Without Query Store, sys.dm_exec_procedure_stats gives you min_elapsed_time and max_elapsed_time per cached procedure, and a max-to-min ratio in the hundreds on a frequently called proc deserves a look at its parameters. One warning from my own incident log: the whale's executions were also when the blocking piled up, because forty-second scans held locks forty seconds long — if your top-variance procedure also shows up in your blocking chain notes as a frequent lead blocker, that is the same disease presenting twice.
Watching plan variance with MonPG when SQL Server support ships
The counters that would have paged me before the tickets did are duration variance per procedure, plan count per query id, and compiles per second next to batch requests — the recompile event itself, timestamped, so the 9:14 AM statistics update stops being archaeology. MonPG monitors PostgreSQL in production today; SQL Server monitoring is in active development and on the roadmap, and the SQL Server monitoring (coming soon) page carries the current status. Until it lands, the Query Store query above on a schedule, charted yourself, is a perfectly good early warning system — as long as the chart exists before the coin flip does.