Parameter Sniffing in SQL Server: What It Is, Why It Happens, and How to Fix It

What Is Parameter Sniffing?

Parameter sniffing is the process by which SQL Server's query optimizer looks at ("sniffs") the actual parameter values passed into a stored procedure, function, or parameterized query the first time it's compiled, and builds an execution plan optimized specifically for those values. That plan is then cached and reused for every subsequent call, regardless of which parameter values are passed in afterward.

EXEC dbo.GetOrdersByCustomer @CustomerId = 47 compiles a plan assuming ~12 rows for customer 47.
EXEC dbo.GetOrdersByCustomer @CustomerId = 1 reuses that same plan, even though customer 1 has 2.4 million rows.

This is not a bug. It's the intended, documented behavior of the cost-based optimizer, and most of the time it's exactly what you want: compiling a plan tailored to real data is far cheaper and more accurate than guessing. The problem only shows up when the data behind different parameter values is skewed enough that no single plan is good for all of them.

Why Does It Happen?

Plan Caching Is the Whole Point

Compiling an execution plan is expensive: cardinality estimation, index selection, join order, memory grant sizing. SQL Server avoids paying that cost on every execution by caching the plan and matching future calls to it by query text or object signature. That's what makes stored procedures and parameterized queries fast under normal, repeated load.

Cardinality Estimation Assumes the First Value Is Representative

When the plan is first compiled (or recompiled), the optimizer uses the parameter value supplied at that moment against the column's statistics histogram to estimate how many rows will qualify. It then picks operators, join strategies, and memory grants sized for that row count:

Few estimated rows: nested loop joins, index seeks, small memory grant
Many estimated rows: hash or merge joins, index/table scans, large memory grant

If the data distribution across parameter values is roughly even, this works fine no matter which value compiled the plan. If it's skewed, whichever value happens to compile first "wins," and every other value inherits a plan shaped for the wrong row count.

Common Triggers for a Fresh Compile

SQL Server service restart or failover (plan cache is empty)
DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE
Statistics update, which invalidates the cached plan
Schema change on a referenced object
Memory pressure evicting the plan from cache
The plan simply aging out of an underused cache

Any of these can hand the next incoming call "compile duty," and whichever value that call happens to carry becomes the template for everyone else until the next recompile.

Good Sniffing vs. Bad Sniffing

Data pattern	Effect of sniffing	Typical outcome
Uniform distribution	One plan fits all values reasonably well	Fast, consistent, no action needed
Skewed distribution (e.g. a status column that's 99% "Closed")	Plan compiled for the rare value is disastrous for the common one, or vice versa	Intermittent, value-dependent slowness
Ascending key / date range	Estimate is based on the histogram at compile time, which drifts as new rows arrive	Plans degrade gradually until the next recompile
NULL-heavy columns	NULL vs. non-NULL row counts can differ by orders of magnitude	Plan good for NULL search is terrible for specific values

Recognizing Parameter Sniffing

The classic symptom: the exact same stored procedure call is fast for some inputs and slow for others, and re-running it with the "slow" parameter is fast again right after a recompile, then slow again later. Confirm it rather than assume it:

Compare estimated vs. actual row counts in the actual execution plan; a large gap on a specific operator is the signature of a bad estimate
Check sys.dm_exec_query_stats for the same query_hash with wildly different min_worker_time / max_worker_time or min_logical_reads / max_logical_reads
Use Query Store (sys.query_store_plan) to see multiple plans for the same query, and compare their average duration
Reproduce manually: run DBCC FREEPROCCACHE (on a test system, never production) with the "good" value first, then call again with the "bad" value using the same cached plan

SELECT qs.query_hash,
       qs.min_worker_time, qs.max_worker_time,
       qs.min_logical_reads, qs.max_logical_reads,
       qs.execution_count,
       st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%GetOrdersByCustomer%'
ORDER BY qs.max_worker_time DESC;

A large spread between min_worker_time and max_worker_time for the same query is the tell: one cached plan is serving very different workloads well and badly.

What to Do About It

1. Update Statistics First

Before reaching for a query hint, rule out the boring cause: stale or low-sampled statistics make every estimate worse, sniffed or not.

UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

2. OPTION (RECOMPILE)

Forces a fresh plan, tailored to the current parameter values, on every single execution. This eliminates bad sniffing entirely at the cost of paying full compilation overhead every time.

SELECT OrderId, OrderDate, Total
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

Best for queries that run infrequently, where compile cost is small relative to execution cost, or where the row-count skew is severe enough that no single cached plan could ever be right.

3. OPTIMIZE FOR a Specific Value

Pin the optimizer to a representative value instead of whatever happens to compile first. Useful when you know the "typical" case and are willing to accept a suboptimal (but predictable) plan for outliers.

SELECT OrderId, OrderDate, Total
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 47));

4. OPTIMIZE FOR UNKNOWN

Tells the optimizer to ignore the actual parameter value and use the column's average density/histogram instead, producing one consistent "average case" plan for every call.

SELECT OrderId, OrderDate, Total
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR UNKNOWN);

Good middle ground when values are unpredictable but not wildly skewed: nobody gets the best possible plan, but nobody gets the worst one either.

5. Local Variable Trick (Use With Caution)

Assigning the parameter to a local variable before using it in the query defeats sniffing implicitly, because the optimizer can't sniff a local variable's value:

CREATE PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId INT
AS
BEGIN
    DECLARE @CustomerIdLocal INT = @CustomerId;
    SELECT OrderId, OrderDate, Total
    FROM dbo.Orders
    WHERE CustomerId = @CustomerIdLocal;
END

This behaves the same as OPTIMIZE FOR UNKNOWN, but is easy to introduce accidentally and just as easy to forget you did. Prefer the explicit hint so the intent is visible in the query text.

6. Force a Known-Good Plan With Query Store

If one specific plan is reliably good across your real workload, Query Store lets you force it regardless of which parameter value triggers the next compile:

EXEC sys.sp_query_store_force_plan
    @query_id = 1234,
    @plan_id  = 5678;

This is a targeted fix for a known plan regression, not a general-purpose defense; it needs monitoring, because a forced plan doesn't automatically adapt if data volumes shift.

7. Split the Procedure by Case

When a column has genuinely different traffic patterns, e.g. a status filter that's 99% one value, consider two code paths (an IF branch calling different queries, or two procedures) so each gets its own plan tuned for its own row count, instead of forcing one plan to serve both.

8. Trace Flag 4136 or Legacy Cardinality Estimator (Last Resort)

Trace flag 4136 disables parameter sniffing instance-wide or at the query level, making every query behave like OPTIMIZE FOR UNKNOWN. This is a blunt instrument: it removes bad sniffing outcomes, but also removes the good ones for every other query on the instance. Reach for the query-level hints above first, and only consider this after confirming sniffing is a systemic problem rather than a handful of queries.

Choosing the Right Fix

Situation	Recommended approach
Query runs rarely, compile cost is negligible	`OPTION (RECOMPILE)`
Query runs constantly, values vary but aren't wildly skewed	`OPTIMIZE FOR UNKNOWN`
One value is clearly the "typical" case	`OPTIMIZE FOR (@Param = value)`
One specific plan is known-good and stable	Force the plan via Query Store
A column has two genuinely distinct traffic patterns	Split into separate query paths
Sniffing is a widespread, instance-level problem	Investigate trace flag 4136, only after ruling out targeted fixes

The Bottom Line

Parameter sniffing isn't a defect to eliminate everywhere. It's the optimizer doing exactly what it's designed to do: build a plan from real data instead of guesswork. It only becomes a problem when the data behind your parameters is skewed enough that one plan can't serve every value well.

Don't disable parameter sniffing by default. Find the specific query where it's causing harm, confirm it with estimated-vs-actual row counts, and apply the narrowest hint that fixes it.

Start by updating statistics, confirm the problem with execution plans and sys.dm_exec_query_stats, then reach for OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN on the specific offending query. Save instance-wide changes for when you've proven the problem is instance-wide too.

For the parameter mechanics one level below sniffing, defaults, OUTPUT parameters, table-valued parameters, and the data type mismatch that silently turns a seek into a scan, see Stored Procedure Parameters in SQL Server, Explained.

← Back to Blog