Parameter Sniffing, Plainly

Parameter sniffing is not a defect. It is the optimizer doing something useful: when a stored procedure is first executed, it looks at ("sniffs") the actual parameter values and builds a plan optimized for them. That plan is then cached and reused for every subsequent execution.

For most workloads this is exactly right — you get a plan tailored to real values instead of a generic guess.

It becomes a problem when different parameter values need structurally different plans. Reports are the textbook case.

The report scenario

CREATE OR ALTER PROCEDURE dbo.usp_OrdersByDateRange
    @From date,
    @To   date
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.OrderId, o.OrderDate, o.CustomerId, o.Total
    FROM   dbo.Orders AS o                          -- placeholder table
    WHERE  o.OrderDate >= @From
      AND  o.OrderDate <  @To;
END;

Two ways users run it:

One week → about 2,000 rows out of 50 million. The right plan is an index seek on OrderDate, with key lookups for the remaining columns. Fast.
Five years → about 40 million rows. The right plan is a clustered index scan with a hash aggregate. Also fast, for what it is asked to do.

Now consider the caching:

If the one-week call compiles the plan first, the seek-plus-lookup plan is cached. The five-year call then reuses it and performs 40 million individual key lookups. It runs for many minutes.
If the five-year call compiles first, the scan plan is cached. The one-week call reuses it and reads all 50 million rows to return 2,000. It takes 30 seconds instead of 50 milliseconds.

Same procedure. Same code. Whichever ran first decides everyone's performance until the plan is evicted.

The symptom to recognise

The same query is sometimes fast and sometimes slow, with no code change in between. Specifically:

It was fine yesterday and slow today, and nothing was deployed.
It went fast again after a restart, an index rebuild, or a statistics update — all of which evict plans.
It is fast in SSMS and slow from the application, or vice versa. (SSMS often uses different SET options than the application, so it gets a different cache entry and therefore possibly a different plan. This is the most confusing presentation of the problem and it sends people looking for application bugs that do not exist.)
One user reports it as slow and another cannot reproduce it, because they use different filters.

If any of those match your report, investigate sniffing before you design another index.

Confirming it

Query Store answers this directly: one query, multiple plans, wildly different durations.

SELECT  qsq.query_id,
        qsp.plan_id,
        qsp.is_forced_plan,
        SUM(rs.count_executions)                            AS executions,
        MIN(rs.min_duration)/1000.0                         AS min_ms,
        AVG(rs.avg_duration)/1000.0                         AS avg_ms,
        MAX(rs.max_duration)/1000.0                         AS max_ms,
        AVG(rs.avg_logical_io_reads)                        AS avg_reads,
        AVG(rs.avg_rowcount)                                AS avg_rows
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan  AS qsp ON qsp.plan_id  = rs.plan_id
JOIN sys.query_store_query AS qsq ON qsq.query_id = qsp.query_id
WHERE qsq.query_id = 1234                    -- placeholder: your query_id
GROUP BY qsq.query_id, qsp.plan_id, qsp.is_forced_plan
ORDER BY avg_ms DESC;

Two plans for one query, one averaging 90 ms and one averaging 65 seconds, is the diagnosis.

Without Query Store, the plan cache shows the compiled values. On an actual or cached plan, open the Parameter List in the plan XML (or right-click the SELECT operator → Properties → Parameter List) and compare:

Parameter Compiled Value — what the plan was built for.
Parameter Runtime Value — what this execution actually passed.

A compiled value of one week and a runtime value of five years is the whole story in two lines.

-- Pull compiled parameter values out of cached plans
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (25)
       SUBSTRING(t.text, 1, 200) AS query_snippet,
       n.value('(@Column)[1]',                'sysname')       AS parameter_name,
       n.value('(@ParameterCompiledValue)[1]','nvarchar(200)') AS compiled_value,
       qs.execution_count,
       qs.total_elapsed_time / NULLIF(qs.execution_count,0) / 1000 AS avg_ms
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
CROSS APPLY p.query_plan.nodes('//ParameterList/ColumnReference') AS pl(n)
ORDER BY qs.total_elapsed_time DESC;

Reproducing it deliberately

Useful for convincing yourself, and for demonstrating the problem to a colleague:

-- 1. Clear this query's plan, then run the SELECTIVE case first
DBCC FREEPROCCACHE;   -- DEV/TEST ONLY
EXEC dbo.usp_OrdersByDateRange @From = '2026-08-01', @To = '2026-08-08';   -- narrow, compiles
EXEC dbo.usp_OrdersByDateRange @From = '2021-01-01', @To = '2026-08-18';   -- wide, reuses -> slow

-- 2. Clear again and run the WIDE case first
DBCC FREEPROCCACHE;   -- DEV/TEST ONLY
EXEC dbo.usp_OrdersByDateRange @From = '2021-01-01', @To = '2026-08-18';   -- wide, compiles
EXEC dbo.usp_OrdersByDateRange @From = '2026-08-01', @To = '2026-08-08';   -- narrow, reuses -> slow

Capture actual plans for all four executions. The same procedure produces two different plans depending only on execution order, and in each pass the second call is the slow one.

Skew makes it worse

Sniffing is most damaging when data distribution is uneven. A CustomerId parameter where one customer has 4 million orders and most have 12 is the same problem in a sharper form: a plan compiled for a typical customer performs terribly for the large one, and vice versa.

Check for skew before deciding on a fix — it changes which fix is right:

SELECT TOP (20) CustomerId, COUNT_BIG(*) AS order_count
FROM   dbo.Orders                       -- placeholder table
GROUP BY CustomerId
ORDER BY order_count DESC;

If the top value has thousands of times the rows of the median, OPTIMIZE FOR UNKNOWN (next lesson) will produce a plan that suits neither, and RECOMPILE is the better answer.

The short version

Sniffing means the plan is compiled for whichever parameter values ran first and then reused. Reports are the classic case because a one-week range and a five-year range want structurally different plans — a seek with lookups versus a scan with a hash aggregate — and whichever compiles first penalizes the other. The tell is a query that is sometimes fast and sometimes slow with no deployment in between, and Query Store confirms it: one query_id with two plan_ids and very different average durations.