SpDev Install free

Parameter sniffing in SQL Server: what it is and how to fix it

Why a stored procedure is fast for one customer and slow for another, how to prove it's parameter sniffing, and the fixes that work, from a covering index to OPTION (RECOMPILE).

By Areeb Ahmer, . 6 minute read.

A procedure runs in milliseconds all morning. After lunch, the same call takes seconds. Nobody deployed anything. Someone runs sp_recompile or restarts the server, and it’s fast again. A week later it happens again.

That pattern is almost always parameter sniffing. SQL Server compiles a plan for the parameter values of the first call, caches it, and reuses it for every later call, including calls whose values need a completely different plan.

The examples use the SpDevDemo database. Its dbo.Orders table is skewed on purpose: customer 1 is a marketplace account with 20,000 orders, while a typical customer has 9 or 10.

The procedure

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
SELECT OrderId, OrderNumber, OrderDate, Total
FROM dbo.Orders
WHERE CustomerId = @CustomerId;

There is an index on CustomerId, but it doesn’t contain the other columns. So SQL Server has two reasonable plans:

  • Seek and look up: seek the 9 rows for the customer in IX_Orders_CustomerId, then fetch each row’s other columns from the clustered index (a key lookup per row). Cheap for a few rows.
  • Scan: read the whole clustered index once and keep the matching rows. Expensive for 9 rows, cheap compared to 20,000 lookups.

Which one is right depends on the value of @CustomerId.

Watch it happen

Call it first for a typical customer, then for customer 1:

EXEC sp_recompile 'dbo.GetCustomerOrders';   -- start from an empty plan for this procedure

SET STATISTICS IO, TIME ON;
EXEC dbo.GetCustomerOrders @CustomerId = 42;
EXEC dbo.GetCustomerOrders @CustomerId = 1;
SET STATISTICS IO, TIME OFF;
-- @CustomerId = 42: compiles a plan for ~10 rows
Table 'Orders'. Scan count 1, logical reads 29

-- @CustomerId = 1: reuses it
Table 'Orders'. Scan count 1, logical reads 60037
 SQL Server Execution Times: CPU time = 90 ms, elapsed time = 122 ms

The plan built for 9 rows ran 20,000 key lookups for customer 1: 60,037 logical reads instead of 29. Timings vary from run to run; reads don’t, which is why the rest of this article compares reads.

Now the other way round:

EXEC sp_recompile 'dbo.GetCustomerOrders';

SET STATISTICS IO ON;
EXEC dbo.GetCustomerOrders @CustomerId = 1;
EXEC dbo.GetCustomerOrders @CustomerId = 42;
SET STATISTICS IO OFF;
-- @CustomerId = 1: compiles a scan
Table 'Orders'. Scan count 1, logical reads 1340

-- @CustomerId = 42: reuses the scan
Table 'Orders'. Scan count 1, logical reads 1340

Now every small customer pays 1,340 reads for a query that needs 29. Neither plan is wrong. Each is right for the value it was compiled for, and wrong for the other.

Sniffing itself is a feature: compiling for the real value usually gives better plans than compiling for an average. It becomes a problem when the data is skewed and the values vary.

How to prove it is sniffing

Get the actual execution plan of a slow call and open the properties of the root operator. Under Parameter List, every parameter has two values:

  • Parameter Compiled Value: the value the plan was built for.
  • Parameter Runtime Value: the value of this call.

If the slow call ran with 1 but the plan was compiled for 42, you have found it. In the plan XML, the same information is in the ParameterCompiledValue and ParameterRuntimeValue attributes.

Two more signs:

  • In the actual plan, the estimated and actual row counts of the seek or scan are far apart (estimate 9, actual 20,000).
  • Recompiling the procedure “fixes” it until the next unlucky first call.

Fixes, from best to last resort

1. Remove the choice with an index

The cleanest fix is often to make one plan good for every value. Here the dilemma exists only because the index on CustomerId doesn’t cover the query. Add the columns it returns:

CREATE INDEX IX_Orders_CustomerId_Covering
    ON dbo.Orders (CustomerId) INCLUDE (OrderNumber, OrderDate, Total);

EXEC sp_recompile 'dbo.GetCustomerOrders';

SET STATISTICS IO ON;
EXEC dbo.GetCustomerOrders @CustomerId = 42;
EXEC dbo.GetCustomerOrders @CustomerId = 1;
SET STATISTICS IO OFF;
Table 'Orders'. Scan count 1, logical reads 3      -- customer 42
Table 'Orders'. Scan count 1, logical reads 103    -- customer 1, same plan

A seek on a covering index reads only what it returns, so the same plan is now right for both. Not every query can be covered, but when one can, this beats every hint.

2. OPTION (RECOMPILE) on the statement

SELECT OrderId, OrderNumber, OrderDate, Total
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

The statement gets a fresh plan for every call, built for that call’s values. The price is compile time, usually a few milliseconds of CPU. That’s nothing for a procedure that runs a few times a minute, and too much for one that runs 2,000 times a second. Put it on the statement that suffers, not on the whole procedure (WITH RECOMPILE), so the rest of the procedure keeps its cached plans.

3. OPTIMIZE FOR

OPTION (OPTIMIZE FOR (@CustomerId = 1));        -- always plan for the big customer
OPTION (OPTIMIZE FOR (@CustomerId UNKNOWN));    -- plan for an average value

OPTIMIZE FOR a specific value makes the plan predictable: you choose which customers get the best plan. OPTIMIZE FOR UNKNOWN ignores the sniffed value and uses the column’s average density, here about 10 rows per customer. That gives the seek-and-lookup plan every time, which is still slow for customer 1. Copying the parameter into a local variable, the old trick, has the same effect as UNKNOWN.

4. Split the procedure

When a few values behave differently from the rest, and you know which ones, give them their own plan:

IF @CustomerId IN (SELECT CustomerId FROM dbo.LargeAccounts)
    EXEC dbo.GetCustomerOrders_Large @CustomerId = @CustomerId;
ELSE
    EXEC dbo.GetCustomerOrders_Small @CustomerId = @CustomerId;

Each inner procedure caches its own plan, compiled for the kind of customer it serves.

5. Force a plan with Query Store

If you can’t change the code, Query Store can pin a known-good plan to the query (sp_query_store_force_plan). On SQL Server 2022 you can also attach a hint such as RECOMPILE without touching the code, using sp_query_store_set_hints. Treat both as a fix you will revisit: a forced plan doesn’t adapt when the data changes.

What about SQL Server 2022’s PSP optimization?

SQL Server 2022 added Parameter Sensitive Plan optimization, at compatibility level 160. When it detects skew in the statistics of a predicate, it can cache up to three plans for one query and choose one per call. It’s a welcome safety net, but it doesn’t trigger for every skewed query. It didn’t for the example above, on SQL Server 2022 at level 160. Fix the procedures you know about; let PSP catch some of the ones you don’t.

Things that look like sniffing but aren’t

  • Different SET options. SSMS sets ARITHABORT ON; most applications don’t. The same procedure then has two cached plans, one per set of options. That is why “it’s fast in SSMS but slow from the app” often has nothing to do with the code. Compare the plans for the app’s options, not for yours.
  • Out-of-date statistics. A plan compiled from stale statistics is bad for every value, not just some.
  • Blocking. If the plan is the same and the reads are the same but the time isn’t, the query is waiting on something. Check its waits before you look at its plan.

In short

  1. Compare the compiled and runtime parameter values in the actual plan.
  2. See whether an index can make one plan right for every value.
  3. If not, recompile the one statement that suffers, or pick the values the plan is built for.
  4. Keep Query Store on, so you can see when a query’s plan changes and force the old one while you fix it.

SpDev Pro does the first step for you: when it profiles a procedure, it compares the compiled and runtime values of every captured plan. When a statement ran with values its plan wasn’t built for, and the estimates were off because of it, the finding says so on that line: “compiled for @CustomerId = 42, ran with 1”.