Parameter sniffing is SQL Server doing exactly what it’s supposed to do: compiling a query plan based on the actual parameter value passed in the first time it runs, then reusing that plan for later calls. That’s normally the right behavior, a plan built for the real value is usually better than a generic one. The problem shows up when the data behind a parameterized query is skewed, one value matches a handful of rows, another matches most of the table, and whichever value happened to compile the plan first determines how every later call performs, regardless of what it’s actually being asked to do.
This post reproduces it directly: a table with deliberately skewed data, a stored procedure, and the exact estimated-vs-actual row counts SQL Server produced.
The Setup
A table with two categories, wildly uneven in size, and an index that covers the query:
CREATE TABLE dbo.SniffTest (id INT IDENTITY PRIMARY KEY, cat INT NOT NULL, filler CHAR(200));
-- cat = 1: 10,000 rows cat = 2: 10 rows
CREATE INDEX ix_sniff_cat ON dbo.SniffTest(cat) INCLUDE (filler);
CREATE PROCEDURE dbo.GetByCategory @cat INT AS
BEGIN
SELECT COUNT(*) AS row_count FROM dbo.SniffTest WHERE cat = @cat;
END
Step 1: Compile Against the Rare Value First
EXEC dbo.GetByCategory @cat = 2; -- 10 rows, compiles and caches the plan here
This returns 10 rows, correctly, and the plan SQL Server caches is optimized for a 10-row result.
Step 2: Call It Again With the Common Value
EXEC dbo.GetByCategory @cat = 1; -- actually 10,000 rows, but reuses the cached plan
Same procedure, same query text, but the actual row count coming back is 10,000, not 10. SQL Server doesn’t recompile just because the parameter changed, it reuses the plan compiled in Step 1. SET STATISTICS PROFILE ON shows exactly what that plan estimated versus what actually happened:
| Actual Rows | Estimated Rows | Estimated Cost | |
|---|---|---|---|
Index Seek (sniffed plan, compiled for cat=2) |
10,000 | 10.0 | 0.0033 |
Index Seek (fresh plan, compiled for cat=1) |
10,000 | 10,000.0 | 0.214 |
The estimate is off by a factor of 1,000. In this specific example the index still covers the query either way, so the physical operator (an index seek) doesn’t change, only the row and cost estimate do, but that gap is exactly what drives a bad decision in a less trivial query: an under-costed operation gets a smaller memory grant than it needs, gets picked as the wrong side of a join, or doesn’t get the parallelism a correctly-estimated 10,000-row operation would have received. A query with a JOIN or ORDER BY layered on top of this same pattern is where a 1,000x cardinality miss turns into a real, visible slowdown, not just a wrong number in a query plan.
Step 3: Force a Fresh Plan Per Call
ALTER PROCEDURE dbo.GetByCategory @cat INT AS
BEGIN
SELECT COUNT(*) AS row_count FROM dbo.SniffTest WHERE cat = @cat OPTION (RECOMPILE);
END
With OPTION (RECOMPILE), the second call (@cat = 1) compiles its own plan instead of reusing the one from @cat = 2. The estimate this time: 10000.0, exact. OPTION (RECOMPILE) pays a real compilation cost on every single execution, that’s the trade, not a free fix, worth reaching for on a query that’s actually hurt by sniffing, not applied by default everywhere.
How to Actually Confirm This Is What’s Happening
Don’t guess from symptoms alone (“sometimes it’s fast, sometimes it’s slow” is consistent with several other problems too). Confirm the estimate mismatch directly:
SELECT qs.execution_count, qs.total_worker_time / qs.execution_count AS avg_cpu_per_exec,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.sql_handle = (SELECT sql_handle FROM sys.dm_exec_procedure_stats WHERE object_id = OBJECT_ID('dbo.GetByCategory'));
Open the plan and compare EstimateRows against the actual row count for the parameter value currently causing complaints. A large, consistent gap on a specific operator is the signature, not a one-off blip.
Common Causes and Fixes
- A shared procedure serving both a common-case and a rare-case caller. The classic shape: a scheduled job or batch process calls the rare-value path first thing after a service restart or plan cache clear, caching a plan every regular user then inherits.
OPTION (RECOMPILE)on the specific statement, orOPTION (OPTIMIZE FOR UNKNOWN)to get a distribution-average estimate instead of a sniffed one, both address this without recompiling the entire procedure on every call. - Highly skewed data with no good general-purpose plan. If one value genuinely needs a seek and another genuinely needs a scan, no single cached plan serves both well. This is a real case for
OPTION (RECOMPILE)on the affected statement specifically, accepting the per-call compile cost as the price of a correct plan every time. - Plan cache cleared at an inconvenient moment (a failover, a restart, an explicit
DBCC FREEPROCCACHE, or a large ad-hoc workload evicting the plan). Whatever value happens to run first after that recompiles the shared plan. Check Get Trace Flags and Resource Governor Configuration if Query Store isn’t already enabled, it lets you force a specific known-good plan rather than hoping the next compile lands on a good one. - Stale statistics making every estimate wrong regardless of sniffing. Confirm statistics are current before concluding sniffing is the root cause, an outdated histogram produces bad estimates independent of which parameter compiled the plan.
Best Practices
- Confirm the estimate-vs-actual mismatch directly (
SET STATISTICS PROFILE ON, an actual execution plan, orsys.dm_exec_query_stats) before reaching forOPTION (RECOMPILE), don’t apply it as a reflex to every slow parameterized query. - Enable Query Store on instances where this recurs; it shows plan changes over time and lets you force a specific plan without touching the procedure’s code.
- Reserve
OPTION (RECOMPILE)for the specific statement that actually needs it, not the whole procedure, recompiling only what’s sensitive to the parameter keeps the compile cost as small as possible. - Re-test after any fix with the same skewed values that originally exposed the problem, a plan that looks fine for the common case can still be wrong for the rare one.
Related Scripts
- Get Query Performance Deep-Dive, for finding which cached plan and parameter combination is behind a slow procedure
- Get Trace Flags and Resource Governor Configuration, to check whether Query Store is already available for plan forcing
- Table Variables vs Temp Tables: What the Optimizer Actually Sees, a related cardinality-estimate mismatch with a different root cause
- Performance and Troubleshooting (area)
Leave a Reply