SQL Server Parameter Sniffing: What It Is and How to Fix It
"The stored procedure was fast yesterday and slow today, and nothing changed." If you have heard that sentence, you have probably met parameter sniffing. It is one of the most common causes of sudden, unexplained slowdowns in SQL Server, and once you understand it, it becomes easy to diagnose.
What parameter sniffing is
When a parameterised query or stored procedure runs for the first time, SQL Server looks at ("sniffs") the parameter values, builds an execution plan that is optimal for those values, and caches it. Every later call reuses that plan, whatever values it passes.
Most of the time this is good: compiling once saves CPU. It becomes a problem when your data is skewed. Imagine an orders table where one large customer has 2 million rows and most customers have 50:
CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
@CustomerId INT
AS
SELECT OrderId, OrderDate, Total
FROM dbo.Orders
WHERE CustomerId = @CustomerId;- If the first call is for a small customer, SQL Server picks an index seek plus key lookups. Perfect for 50 rows, terrible for 2 million.
- If the first call is for the large customer, it picks a scan. Fine for 2 million rows, wasteful for 50.
Whichever value arrives first after a restart, statistics update or plan eviction decides performance for everyone else. That is why the problem seems to appear and disappear at random.
How to confirm it
- Symptom: the same procedure is fast for some parameter values and slow for others, or performance changes suddenly with no code change.
- In the actual execution plan: compare Estimated Number of Rows with Actual Number of Rows. A huge gap on the main table access is the classic sign.
- Parameter values: in the plan's properties, the root operator shows Parameter Compiled Value and Parameter Runtime Value. If they differ a lot, you have found it.
- In Query Store: the "Regressed Queries" report shows queries whose plan changed and got slower, with each plan side by side.
Fixes, from quickest to most robust
1. Force the good plan in Query Store (short term)
If Query Store shows a good plan and a bad plan, you can force the good one from the Query Store UI or with sp_query_store_force_plan. This stops the bleeding immediately without code changes, but it locks in one plan, so revisit it later.
2. OPTION (RECOMPILE)
Adding OPTION (RECOMPILE) to the statement builds a fresh plan for every execution using the actual values:
SELECT OrderId, OrderDate, Total
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);Every call gets the right plan, at the cost of compiling every time. Great for queries that run a few times a minute; avoid it on statements that run thousands of times per second.
3. OPTIMIZE FOR
If one plan is "good enough" for most values, tell SQL Server which value to optimise for, or to use average statistics instead of the sniffed value:
-- Optimise for a typical customer
OPTION (OPTIMIZE FOR (@CustomerId = 42));
-- Or ignore the sniffed value and use average density
OPTION (OPTIMIZE FOR UNKNOWN);This gives a stable plan, but one that may be mediocre for the outliers.
4. Separate code paths for skewed values
When you know which values are unusual (for example, a handful of very large customers), route them to a separate procedure or statement so each path gets its own cached plan. This is more code, but it gives both groups an optimal plan without recompiling.
5. Better indexes
Sometimes the real fix is an index that makes one plan good for every value. In the example above, a covering index on (CustomerId) that INCLUDEs OrderDate and Total removes the key lookups, so a seek works well for small and large customers alike.
6. Parameter Sensitive Plan optimization (SQL Server 2022+)
In SQL Server 2022 and Azure SQL Database, with database compatibility level 160, Parameter Sensitive Plan (PSP) optimization can cache several plans for the same query and pick one based on the parameter value at run time. It does not cover every case, but it removes many classic sniffing problems automatically. Check your compatibility level before relying on it.
What not to do
- Do not clear the whole plan cache (
DBCC FREEPROCCACHE) on a production server to "fix" one query. It forces every query to recompile and often just moves the problem. - Do not copy parameters into local variables as a blanket fix. It disables sniffing in an uncontrolled way, similar to
OPTIMIZE FOR UNKNOWN, and can make other queries worse.
Quick checklist
- Compare compiled and runtime parameter values in the actual plan.
- Check Query Store for plan changes and regressions.
- Force a known good plan for immediate relief.
- Choose a lasting fix:
RECOMPILE,OPTIMIZE FOR, split paths or a better index. - On SQL Server 2022+, consider compatibility level 160 for PSP optimization.
Related reading:
Comments
Post a Comment