SQL Server parameter sniffing in depth: diagnose it, then choose between PSP optimization, hints and Query Store

The classic report: "the procedure is fast in SSMS and slow from the app" or "it was fine yesterday, now it times out, and a restart fixed it". Usually that is a plan compiled for one parameter value being reused for very different ones.

#How it happens

SQL Server compiles a plan using the parameter values of the first call ("sniffing") and caches it. If the data is skewed, for example one customer with 5 million orders and most with 20, a plan built for a small customer (nested loops, key lookups) is a disaster for the big one, and vice versa.

#Step 1: confirm it is parameter sensitivity

Query Store shows several plans with very different performance for the same query:

SELECT q.query_id, p.plan_id, rs.count_executions,
       rs.avg_duration / 1000.0 AS avg_ms, rs.max_duration / 1000.0 AS max_ms,
       rs.avg_logical_io_reads, p.is_forced_plan
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE q.object_id = OBJECT_ID('dbo.GetCustomerOrders')
ORDER BY q.query_id, rs.avg_duration DESC;

Signs:

• Two or more plans; one has a much higher average duration.
• Big gap between average and max duration for one plan.
• The compiled parameter values in the plan XML differ from the values that run slowly.

Get the sniffed values from the cached plan:

SELECT qp.query_plan
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
WHERE ps.object_id = OBJECT_ID('dbo.GetCustomerOrders');
-- look for <ParameterList><ColumnReference ParameterCompiledValue="(42)" ...

Check skew directly:

SELECT TOP 10 CustomerID, COUNT(*) AS n FROM dbo.Orders GROUP BY CustomerID ORDER BY n DESC;
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerID') WITH HISTOGRAM;

Tip

"Fast in SSMS, slow in the app" is often different SET options (SSMS uses ARITHABORT ON, most drivers OFF), which gives SSMS its own plan. Compare set_options in sys.dm_exec_plan_attributes before concluding.

#Step 2: choose a fix

#PSP optimization (SQL Server 2022, compat 160)

ALTER DATABASE SalesDB SET COMPATIBILITY_LEVEL = 160;
-- check it is on (default ON at 160)
SELECT * FROM sys.database_scoped_configurations WHERE name = 'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION';

The optimizer creates up to three plan variants for different cardinality ranges of a sensitive parameter. Query Store shows them as variants with a dispatcher plan (query_variant and query_store_query_variant catalogue views). It covers equality predicates; ranges and many complex patterns are not handled, so it may not fix your case.

Warning

Raising the compatibility level changes other optimizer behaviour too (cardinality estimator, intelligent query processing features). Test the whole workload, and keep Query Store on so you can force old plans if something regresses.

#Hints in code

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

For high-frequency queries, prefer OPTIMIZE FOR over RECOMPILE:

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

#Force a plan

EXEC sys.sp_query_store_force_plan @query_id = 4812, @plan_id = 9931;
-- later
EXEC sys.sp_query_store_unforce_plan @query_id = 4812, @plan_id = 9931;

Monitor forced plans for failures:

SELECT query_id, plan_id, force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan WHERE is_forced_plan = 1;

#Query Store hints (no code change)

EXEC sys.sp_query_store_set_hints @query_id = 4812, @query_hints = N'OPTION (RECOMPILE)';
EXEC sys.sp_query_store_clear_hints @query_id = 4812;

Available on SQL Server 2022, Azure SQL Database and Managed Instance.

#Step 3: stop the bleeding during an incident

If a bad plan is hurting production right now, evict just that plan rather than the whole cache:

SELECT plan_handle FROM sys.dm_exec_procedure_stats WHERE object_id = OBJECT_ID('dbo.GetCustomerOrders');
DBCC FREEPROCCACHE (0x05000600...);    -- the plan_handle

Caution

DBCC FREEPROCCACHE without a handle clears every plan on the instance and causes a compile storm. Never run it bare on a busy server.

Then apply a durable fix from step 2, or the next unlucky first call will bring the bad plan back.

#Make statistics part of the fix

Sniffing problems get worse with stale or sampled statistics on skewed columns:

UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH FULLSCAN;

Consider filtered statistics for the heavy values, or PERSIST_SAMPLE_PERCENT so automatic updates keep a good sample.

#Prevent it next time

• Query Store on (read-write) for every user database, with enough space and capture mode AUTO.
• Alert on plan regressions (Automatic tuning FORCE_LAST_GOOD_PLAN on Azure SQL and SQL Server Enterprise).
• Load-test procedures with both extreme and typical parameter values.