SQL Server Parameter Sniffing: How to Diagnose and Fix It Permanently

Parameter sniffing is when SQL Server compiles a query plan based on the parameter values it sees the first time the plan is built, then re-uses that plan for every subsequent execution. When the parameter distribution is uniform, this is fine — great, even. When the parameter values vary in selectivity (one customer has 50 orders, another has 2 million), the cached plan is great for one and terrible for the other.

Almost every SQL Server I've inspected has at least three procedures bleeding CPU from parameter sniffing. Almost none of them are diagnosed correctly the first time, because the symptoms mimic other problems.

How to know it's parameter sniffing

The classic fingerprint:

• A query that runs fast 99% of the time, but slow at random intervals
• The same query runs fast when you copy it out of SSMS and run it ad-hoc, but slow as part of a stored procedure
• Performance changes dramatically after a service restart, an index rebuild, or a statistics update — because those events invalidate the plan cache
• Two users with similar workloads have very different experiences

The diagnostic query

SELECT TOP 25
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_us,
    qs.last_worker_time AS last_cpu_us,
    qs.min_worker_time AS min_cpu_us,
    qs.max_worker_time AS max_cpu_us,
    qs.max_worker_time * 1.0 / NULLIF(qs.min_worker_time,0) AS cpu_variance_ratio,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 50
ORDER BY cpu_variance_ratio DESC;

Sort by cpu_variance_ratio. Anything above ~10x is a strong candidate. Above 100x and it's almost certainly parameter sniffing.

The fixes (in order of preference)

Fix 1: Make sure statistics are healthy

Bad statistics produce bad plans for everyone, not just the second user. Before chasing parameter sniffing, verify statistics on the involved tables are fresh and sampled adequately. FULLSCAN on hot tables, not the default sampling, often eliminates what looked like a sniffing problem.

Fix 2: OPTION (RECOMPILE) on the offending statement

Cheapest fix for a single bad statement. The plan is rebuilt each time using actual parameter values. Cost: extra CPU for compilation. Worth it when the statement runs occasionally with wildly different parameters.

SELECT ...
FROM Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);

Fix 3: OPTION (OPTIMIZE FOR ...) hint

Tells the optimizer to build the plan as if a specific parameter value were passed — typically the most common value in your workload.

SELECT ...
WHERE OrderDate > @SinceDate
OPTION (OPTIMIZE FOR (@SinceDate = '2024-01-01'));

Use when you understand the workload and one value dominates.

Fix 4: OPTIMIZE FOR UNKNOWN

Forces the optimizer to use the average/density vector instead of any specific value. Often produces a "good enough for everybody" plan, never optimal for anyone.

Fix 5: Local variable rewrite

A pattern from before OPTIMIZE FOR UNKNOWN existed, but still occasionally useful. Copy the parameter into a local variable and use the variable. The optimizer can't sniff a local variable.

CREATE PROCEDURE GetOrders @CustID INT AS
DECLARE @Cust INT = @CustID;
SELECT * FROM Orders WHERE CustomerID = @Cust;

Fix 6: Query Store plan forcing

When you've identified a known-good plan for the query, force it. Query Store guide here.

Fix 7: Refactor into two stored procedures

Sometimes the cleanest fix is acknowledging that the workload genuinely has two distinct shapes. Split the procedure into two, optimized differently, and route at the application layer.

How to verify the fix

After applying any fix, watch the cpu_variance_ratio for that statement in Query Store or the DMV query above. If it's still high, the fix didn't work — don't celebrate based on "it felt faster this morning."

---

Recurring parameter sniffing problems in production? A SQL Server health check identifies the top sniffing victims in your environment and provides per-query remediation. Book a free call below.