Fixes in Order of Bluntness

There are six standard responses to a parameter sniffing problem. They differ in how much they give up. Work from the top of this list down, and stop as soon as the problem is solved.

0. Fix the underlying cause first

Before applying any hint, check whether the plan instability is really a statistics problem or a missing index. A query whose statistics are three months stale produces unstable plans for reasons that have nothing to do with parameters, and a hint would be papering over it. Update statistics, confirm the indexes support the query's access paths, and re-measure. This costs ten minutes and sometimes ends the investigation.

1. OPTION (RECOMPILE)

Compile a fresh plan on every execution, with the actual values known.

SELECT o.OrderId, o.OrderDate, o.CustomerId, o.Total
FROM   dbo.Orders AS o                   -- placeholder table
WHERE  o.OrderDate >= @From
  AND  o.OrderDate <  @To
OPTION (RECOMPILE);

Gives up: plan reuse, and the CPU to compile each time. Gains: every execution gets the right plan for its own values; accurate cardinality estimates; constant folding of NULL branches (Module 4 Lesson 6).

For a reporting query running dozens or hundreds of times a day, a few milliseconds of compile against tens of seconds of runtime is an easy trade. This is usually the correct answer for reports. It becomes wrong when execution frequency is high enough that compile CPU is material — watch total_worker_time before and after.

You can also apply it at procedure level (CREATE PROCEDURE ... WITH RECOMPILE), which recompiles the whole procedure. Prefer the statement-level hint: it targets only the statement that needs it.

2. OPTIMIZE FOR (a specific value)

Compile for a value you choose, regardless of what is passed.

SELECT o.OrderId, o.OrderDate, o.Total
FROM   dbo.Orders AS o
WHERE  o.OrderDate >= @From AND o.OrderDate < @To
OPTION (OPTIMIZE FOR (@From = '2026-01-01', @To = '2026-02-01'));

Gives up: optimality for values unlike the one you named. Gains: stable, predictable plans with no recompile cost.

Reasonable when one parameter shape dominates — for instance a report that is run for the current month 95% of the time and for a full year at month-end. You optimize for the common case and accept that the rare case is slower.

The maintenance concern: a hard-coded date drifts out of relevance. Prefer a representative value whose shape stays true (a typical range width) over a literal date if you can express it, and leave a comment explaining the choice.

3. OPTIMIZE FOR UNKNOWN

Ignore the actual parameters entirely; use the average density from the statistics histogram.

SELECT o.OrderId, o.Total
FROM   dbo.Orders AS o
WHERE  o.CustomerId = @CustomerId
OPTION (OPTIMIZE FOR UNKNOWN);

Gives up: any tailoring to actual values. Gains: consistency. Every execution gets the same plan, sized for the average case.

This is the "make it uniformly mediocre" option, and sometimes that is genuinely what the business wants: a report that reliably takes 8 seconds is more usable than one that takes 2 seconds usually and 90 seconds unpredictably.

Where it fails is skewed data. With one customer holding 4 million rows and the rest holding a dozen, the average is not a useful description of either, and the resulting plan serves nobody. Check the skew (previous lesson) before choosing this.

4. Local variables

Assign the parameters to local variables and use those in the query. The optimizer cannot sniff a local variable's value at compile time, so it falls back to density estimates — the same effect as OPTIMIZE FOR UNKNOWN, achieved by accident of syntax.

CREATE OR ALTER PROCEDURE dbo.usp_OrdersByCustomer @CustomerId int
AS
BEGIN
    DECLARE @LocalCustomerId int = @CustomerId;   -- deliberately defeats sniffing
    SELECT OrderId, Total FROM dbo.Orders WHERE CustomerId = @LocalCustomerId;
END;

This works, and you will find it in older codebases. Prefer OPTIMIZE FOR UNKNOWN for the same outcome, because the hint states the intent and the local-variable version looks like a mistake to the next reader. If you do find this pattern in existing code, recognize it as deliberate before "tidying" it away.

5. Query Store hints

Apply a hint to a query without changing the query text. This is the significant modern option, and the reason it matters here: your report's SQL may live inside a Telerik report definition, a third-party application, or an ORM, where you cannot add OPTION (RECOMPILE) to the text.

-- Find the query_id (Module 1), then attach a hint to it
EXEC sys.sp_query_store_set_hints
     @query_id = 1234,                                  -- placeholder
     @query_hints = N'OPTION(RECOMPILE)';

-- Multiple hints in one statement
EXEC sys.sp_query_store_set_hints
     @query_id = 1234,
     @query_hints = N'OPTION(RECOMPILE, MAXDOP 4)';

-- Inspect what is currently applied
SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason_desc
FROM sys.query_store_query_hints;

-- Remove it
EXEC sys.sp_query_store_clear_hints @query_id = 1234;

Available in SQL Server 2022, and in Azure SQL. They persist across restarts, survive plan cache eviction, and are removable in one statement. They are the modern replacement for plan guides, which were notoriously difficult to author and validate.

6. Forcing a plan

The most direct intervention: pin a specific historical plan.

EXEC sp_query_store_force_plan @query_id = 1234, @plan_id = 5678;

Gives up: the optimizer's ability to adapt as data changes. Gains: immediate, predictable restoration of known-good performance.

Legitimate as an emergency lever — it fixes production in seconds with no deployment. Treat it as temporary. Set a reminder to revisit, and monitor for silent forcing failures:

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

force_failure_count > 0 means the forced plan is no longer valid (an index it used was dropped, for example) and the query has quietly reverted to whatever the optimizer picks.

The blunt instruments — know they exist, avoid them

-- Disables sniffing for EVERY query in the database. Very rarely the right call.
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

-- Instance-wide equivalent, even blunter
-- DBCC TRACEON (4136, -1);

These turn off a feature that is usually helping, for the whole database, to fix a handful of queries. If you find one already set on a server you inherit, that is a signal someone hit this problem before and reached for the largest available lever — and it is worth revisiting with the targeted tools above.

The decision path

1. Statistics stale, or the right index missing? Fix that. Re-measure.
2. Report query, moderate execution frequency, you can edit the SQL → OPTION (RECOMPILE).
3. Cannot edit the SQL (third-party, ORM, report definition) → Query Store hint applying RECOMPILE.
4. One parameter shape dominates → OPTIMIZE FOR that shape.
5. Consistency matters more than peak speed, and data is not badly skewed → OPTIMIZE FOR UNKNOWN.
6. Production is on fire right now → force the good plan, then come back and do 1–5.

In practice

Work from least to most invasive. First check whether it is really stale statistics or a missing index. For a report you can edit, OPTION (RECOMPILE) is usually the answer — the compile cost is nothing against the runtime and every execution gets the right plan. If the SQL is inside a report definition or an ORM and you cannot touch the text, SQL Server 2022 lets you attach the same hint through Query Store without a deployment. Forcing a plan is the emergency lever, not the fix.