Parameter Sniffing in SQL Server: Causes & Fixes

What parameter sniffing actually is

Parameter sniffing isn't a bug — it's the optimizer doing exactly what it's designed to do. The first time a parameterized query or stored procedure executes, SQL Server looks at the actual parameter value passed in, checks column statistics for that value, and compiles a plan optimized for it. That plan is then cached and reused for every subsequent call, regardless of what value is passed next:

CREATE PROCEDURE get_orders_by_status (@status VARCHAR(20))
AS
BEGIN
  SELECT * FROM orders WHERE status = @status;
END;

-- 'cancelled' matches 40 rows out of 20 million — an index seek is ideal
EXEC get_orders_by_status 'cancelled';

-- 'completed' matches 18 million of those same 20 million rows —
-- a table scan would be far better, but the cached seek plan runs anyway
EXEC get_orders_by_status 'completed';

If 'cancelled' ran first, every later call — including 'completed' — reuses that seek-based plan, even though a scan would be dramatically faster for a status matching 90% of the table. The query is correct; the cached plan is just wrong for this call's data distribution.

How to confirm it's the cause

The signature symptom is inconsistency: identical procedure, identical code, wildly different runtimes depending on which parameter value happens to hit it. Confirm it by comparing estimated rows against actual rows in the execution plan for a slow call:

SELECT qs.execution_count, qs.total_elapsed_time / qs.execution_count AS avg_time_micros,
       qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%get_orders_by_status%';

A large gap between estimated and actual row counts on a slow execution — combined with a fast execution elsewhere using the exact same cached plan — is the confirming signature of parameter sniffing rather than a missing index or stale statistics issue.

Fix 1 — OPTION (RECOMPILE)

Forces a brand-new, optimal plan for every single execution, built fresh from that call's actual parameter value:

SELECT * FROM orders
WHERE status = @status
OPTION (RECOMPILE);

Every call gets its ideal plan, but nothing is cached or reused — the compile cost is paid every time. This is the right tool when a query's optimal plan genuinely changes a lot based on the parameter, and it's applied to the specific offending statement rather than an entire large procedure.

Fix 2 — OPTIMIZE FOR UNKNOWN or a specific value

Tells the optimizer to ignore the actual parameter value at compile time and build a plan based on the average distribution across the column instead, from statistics:

SELECT * FROM orders
WHERE status = @status
OPTION (OPTIMIZE FOR (@status UNKNOWN));

-- Or pin the plan to a known "typical" value instead of the average:
OPTION (OPTIMIZE FOR (@status = 'pending'));

One plan gets cached and reused by every call, avoiding RECOMPILE's per-execution cost. It's a compromise plan — good on average across all values, not perfectly tuned for any single one — which makes it the better default when values are called often and roughly evenly, rather than dominated by one extreme case.

Fix 3 — The local variable trick

Copying the parameter into a local variable before using it in the query hides the actual value from the optimizer at compile time, which forces the same "average distribution" behavior as OPTIMIZE FOR UNKNOWN without needing the hint:

CREATE PROCEDURE get_orders_by_status (@status VARCHAR(20))
AS
BEGIN
  DECLARE @status_local VARCHAR(20) = @status;
  SELECT * FROM orders WHERE status = @status_local;
END;

This is an older, widely used trick and it still works on most instances by default — but it's implicit and easy to forget the reasoning behind later, so an explicit OPTIMIZE FOR UNKNOWN hint is usually clearer for anyone reading the procedure afterward.

Fix 4 — Trace flag 4136 (last resort)

Disables parameter sniffing for the entire SQL Server instance, making every parameterized query behave as if OPTIMIZE FOR UNKNOWN were applied everywhere:

DBCC TRACEON(4136, -1);  -- -1 applies instance-wide

Common mistakes

• Reaching for trace flag 4136 immediately instead of fixing the one or two problem queries directly.
• Applying OPTION (RECOMPILE) to an entire large, frequently-called procedure when only one statement inside it actually needs it — paying the compile cost everywhere instead of where it's needed.
• Assuming a slow query is a missing-index problem and adding indexes that don't help, without first checking whether the existing plan is simply mismatched to this call's data.
• Forgetting that clearing the plan cache (or a server restart) can temporarily "fix" symptoms by forcing a recompile — until the next atypical value recompiles and caches a new mismatched plan.