Parameter Sniffing in SQL Server: Symptoms and Fixes

2020-10-08 · mssql, performance, tsql

"It's fast for me and slow for her" is my cue to check for parameter sniffing. A stored procedure that runs in 50 ms for one customer and 40 seconds for another, with no change in code or data, is its classic face. It isn't a bug. SQL Server compiles a plan for the first parameter values it sees and reuses it for every later call. When the data is skewed, a plan that's perfect for a rare value can be terrible for a common one, and the other way round.

Below are the fixes I actually use, roughly in the order I try them. I'm assuming SQL Server 2016 or later (for Query Store) and permission to view plans (SHOWPLAN), plus ALTER on the database for Query Store.

First, confirm it really is sniffing

Compare the compiled and runtime values in the cached plan. The XML plan stores what the procedure was compiled with:

SELECT TOP (20)
       qs.execution_count,
       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,
       qs.max_elapsed_time / 1000 AS max_ms,
       qs.min_elapsed_time / 1000 AS min_ms,
       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,
       qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.max_elapsed_time DESC;

A huge gap between min_ms and max_ms for the same statement is the tell. Open the plan XML and look for <ParameterList>: ParameterCompiledValue is what the plan was built for, ParameterRuntimeValue is what the slow call used.

Reproduce with both values

Run the procedure with the "good" value first, then the "bad" one, and capture actual plans:

SET STATISTICS IO, TIME ON;
EXEC dbo.usp_OrdersByCustomer @CustomerID = 12;      -- small customer
EXEC dbo.usp_OrdersByCustomer @CustomerID = 90210;   -- customer with millions of rows

If the second call reuses the nested-loops plan built for the small customer, you will see enormous logical reads and a plan whose estimated rows are far below actual rows.

Picking a fix

Fix A: OPTION (RECOMPILE) on the offending statement

My first try, and often the right answer for a procedure called a few hundred times a day:

SELECT o.OrderID, o.OrderDate, o.Total
FROM dbo.Orders AS o
WHERE o.CustomerID = @CustomerID
OPTION (RECOMPILE);

Only the statement recompiles, and the optimiser sees the real parameter value every time. The cost is CPU on each execution, so avoid it on statements that run thousands of times per second.

Fix B: OPTIMIZE FOR

When one value is representative of most calls, pin the plan to it:

OPTION (OPTIMIZE FOR (@CustomerID = 12));

OPTIMIZE FOR UNKNOWN uses the density vector instead of any specific value, which gives an "average" plan. It removes the variance but the average plan may be mediocre for everyone.

Fix C: Local variable copy (the old trick)

DECLARE @cid INT = @CustomerID;
SELECT ... WHERE CustomerID = @cid;

This behaves like OPTIMIZE FOR UNKNOWN. I mention it because you will find it in old code; prefer the explicit hint so the intent is visible.

Fix D: Split the procedure

If the two workloads are truly different (a lookup versus a report), create two procedures or branch with IF into two separate statements. Each statement gets its own plan.

Fix E: Force a good plan with Query Store

On SQL Server 2016 and later, if a known-good plan exists, force it:

ALTER DATABASE CURRENT SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
-- find the query and plan ids
SELECT q.query_id, p.plan_id, rs.avg_duration, rs.count_executions
FROM sys.query_store_query AS q
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 q.object_id = OBJECT_ID('dbo.usp_OrdersByCustomer');

EXEC sys.sp_query_store_force_plan @query_id = 41, @plan_id = 118;

This is a strong tool but also a maintenance debt: a forced plan can become wrong after an index change, so review forced plans after every schema change.

Did it work?

After the change, clear just that procedure's plan rather than the whole cache:

EXEC sp_recompile N'dbo.usp_OrdersByCustomer';

Re-run both parameter values and compare avg_ms in sys.dm_exec_query_stats. The maximum should drop and the minimum should not rise much.

Things that fooled me

DBCC FREEPROCCACHE fixed it for an hour. That confirms sniffing but is not a fix; the next compile sniffs again. Apply one of the fixes above.
The plan looks fine but is still slow. Check for out-of-date statistics with DBCC SHOW_STATISTICS; sniffing and stale statistics look alike. Run UPDATE STATISTICS dbo.Orders WITH FULLSCAN and retest.
OPTION (RECOMPILE) did nothing. Make sure the hint is on the statement inside the procedure, not on the EXEC call, and that the procedure was not created WITH RECOMPILE at the top (that recompiles everything and disables plan caching entirely).
Trace flag 4136 disables sniffing server-wide. It exists, but it turns every query into an "unknown" plan. I have never seen it be the right choice on a mixed workload.

Prove the compiled-versus-runtime mismatch before touching anything, then use the narrowest fix that makes both cases acceptable. I've wasted enough time "fixing" problems that turned out to be stale statistics.