Taming Parameter Sniffing in MS-SQL with Query Store and OPTIMIZE FOR Hints

1. Parameter Sniffing in One Minute

You already know the basics: SQL Server compiles an execution plan for a parameterized query or stored procedure and reuses it for later executions.

Parameter sniffing is simply this:

• The first execution’s parameter values are used to generate the plan.
• That plan is cached and reused for subsequent executions.
• If later executions have very different data distributions, the reused plan can be a terrible fit.

Typical symptoms:

• Same procedure, same parameters, wildly different durations across the day.
• Queries that are “fast after RECOMPILE” but slow again later.
• Performance that depends on which user “warms up” the cache.

The goal is not to “turn off parameter sniffing” globally, but to control when and how it happens.

2. A Simple Repro: Skewed Data, Skewed Plans

Let’s build a quick example you can run on a test database.

-- Demo table with skewed data
CREATE TABLE dbo.Sales
(
    SalesId       INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId    INT,
    Region        VARCHAR(20),
    Amount        DECIMAL(18,2),
    SalesDate     DATE
);

-- Skew: 90% of data in 'EU', 10% in 'US'
INSERT INTO dbo.Sales (CustomerId, Region, Amount, SalesDate)
SELECT TOP (90000)
       ABS(CHECKSUM(NEWID())) % 10000,
       'EU',
       RAND(CHECKSUM(NEWID())) * 1000,
       DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, CAST(GETDATE() AS DATE))
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

INSERT INTO dbo.Sales (CustomerId, Region, Amount, SalesDate)
SELECT TOP (10000)
       ABS(CHECKSUM(NEWID())) % 10000,
       'US',
       RAND(CHECKSUM(NEWID())) * 1000,
       DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, CAST(GETDATE() AS DATE))
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

CREATE INDEX IX_Sales_Region_SalesDate ON dbo.Sales (Region, SalesDate);
GO

CREATE OR ALTER PROC dbo.GetSalesByRegion
    @Region VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Region, SUM(Amount) AS TotalAmount
    FROM dbo.Sales
    WHERE Region = @Region
    GROUP BY Region;
END;
GO
``

Now run it twice:

```sql
EXEC dbo.GetSalesByRegion @Region = 'EU';  -- likely first sniff
EXEC dbo.GetSalesByRegion @Region = 'US';  -- reuses plan

Depending on which region runs first, you may see:

• A plan optimized for EU (large result set) reused for US (small result set).
• Or a plan optimized for US reused for EU.

Either way, one of them will be suboptimal.

3. Why “Classic” Fixes Are Not Enough Anymore

Older advice usually rotates around:

• OPTION (RECOMPILE)
• WITH RECOMPILE on the procedure
• OPTIMIZE FOR UNKNOWN
• Local variables inside the proc

These still work, but they have trade-offs:

1. RECOMPILE everywhere

   • Pros: Always fresh plans, no sniffing.
   • Cons: CPU overhead, especially on chatty OLTP systems.

2. OPTIMIZE FOR UNKNOWN

   • Pros: Uses average density, less sensitive to first parameter.
   • Cons: “Average” can be bad for skewed data; still one-size-fits-all.

3. Local variable trick (copy parameter into a local variable)

   • Pros: Forces a different cardinality estimate.
   • Cons: Same issue as UNKNOWN, plus harder to maintain.

Modern SQL Server (2016+ with Query Store, 2017+ with automatic tuning) gives you more precise tools:

• Query Store for plan history and forced plans.
• OPTIMIZE FOR hints to lock in good behavior per query.
• Query Store hints (newer versions) to apply hints without touching code.

4. Using Query Store to See Parameter Sniffing in Action

First, enable Query Store if it isn’t already.

ALTER DATABASE YourDbName SET QUERY_STORE = ON;
GO

ALTER DATABASE YourDbName SET QUERY_STORE
( 
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 512,
    QUERY_CAPTURE_MODE = AUTO
);
GO

Now run your procedure with different parameters multiple times.

EXEC dbo.GetSalesByRegion @Region = 'EU';
EXEC dbo.GetSalesByRegion @Region = 'US';
EXEC dbo.GetSalesByRegion @Region = 'US';
EXEC dbo.GetSalesByRegion @Region = 'EU';

Inspect Query Store for multiple plans and variable performance:

SELECT
    qsq.query_id,
    qsq.object_id,
    qsp.plan_id,
    qsp.last_execution_time,
    qsp.avg_duration,
    qsp.avg_cpu_time,
    qsp.avg_logical_io_reads
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp
    ON qsq.query_id = qsp.query_id
WHERE qsq.object_id = OBJECT_ID('dbo.GetSalesByRegion')
ORDER BY qsp.avg_duration DESC;

What you’re looking for:

• Same query, multiple plans with very different average durations.
• Plans that work well for one parameter but poorly for another.

This is your confirmation that parameter sniffing is affecting real workloads.

5. Mitigation Pattern #1: Force a Good Plan with Query Store

Once you identify which plan is stable and performant for your typical workload, you can force it.

5.1 Identify the best plan

From the previous query, pick a plan_id that:

• Has acceptable duration across most executions.
• Is not skewed heavily to a single atypical parameter.

5.2 Force the plan

EXEC sys.sp_query_store_force_plan
    @query_id = 123,   -- replace with your query_id
    @plan_id  = 456;   -- replace with your chosen plan_id

Now SQL Server will always use this plan unless you unforce it or it becomes invalid (e.g., schema change).

5.3 Pros and cons

Pros:

• No code changes.
• Reversible and trackable.
• Great for third-party applications where you can’t touch the SQL.

Cons:

• Still one plan for all parameters.
• Needs monitoring: the “best plan” can change as data evolves.

This is often the lowest-friction fix for production issues when you need stability fast.

6. Mitigation Pattern #2: OPTIMIZE FOR Hints in Code

For queries you control, OPTIMIZE FOR lets you shape the plan based on realistic scenarios.

6.1 OPTIMIZE FOR UNKNOWN

Applied at statement level:

CREATE OR ALTER PROC dbo.GetSalesByRegion_Unknown
    @Region VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Region, SUM(Amount) AS TotalAmount
    FROM dbo.Sales
    WHERE Region = @Region
    GROUP BY Region
    OPTION (OPTIMIZE FOR (@Region UNKNOWN));
END;

This tells the optimizer: “don’t sniff the actual parameter value; use general stats instead.”

Use when:

• Parameter distribution is messy.
• No single parameter is clearly dominant.

6.2 OPTIMIZE FOR specific values

If your workload is dominated by one or two parameter values, you can optimize for those explicitly.

CREATE OR ALTER PROC dbo.GetSalesByRegion_Optimized
    @Region VARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Region, SUM(Amount) AS TotalAmount
    FROM dbo.Sales
    WHERE Region = @Region
    GROUP BY Region
    OPTION (OPTIMIZE FOR (@Region = 'EU'));
END;

This locks the plan to the cardinality estimates for @Region = 'EU'.

Use when:

• One value (e.g. EU) accounts for most traffic.
• You’re okay with suboptimal plans for rare outliers.

6.3 Combining with RECOMPILE selectively

For some edge cases, you might mix strategies:

SELECT Region, SUM(Amount) AS TotalAmount
FROM dbo.Sales
WHERE Region = @Region
GROUP BY Region
OPTION (OPTIMIZE FOR (@Region UNKNOWN), RECOMPILE);

This is more expensive but can be valid for:

• Low-frequency reports.
• Critical end-of-day jobs where correctness and consistency trump CPU.

7. Mitigation Pattern #3: Query Store Hints (No Code Change)

On newer SQL Server versions, Query Store supports query store hints: you can apply hints like OPTIMIZE FOR UNKNOWN to a query without editing the actual code.

Note: Exact syntax and availability depend on your SQL Server version; check your documentation and test in a non-production environment first.

Typical workflow:

1. Identify a problematic query in Query Store.
2. Decide on an appropriate hint (e.g. OPTIMIZE FOR UNKNOWN).
3. Apply the hint via Query Store.

Conceptually, it looks like:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @value    = N'OPTION (OPTIMIZE FOR (@Region UNKNOWN))';

Benefits:

• No deployment needed.
• Easy to roll back.
• Great for tuning vendor apps or legacy code.

Again, test thoroughly: hints can fix one issue and introduce another if used blindly.

8. When to Use Which Pattern

A quick decision guide:

1. Production incident, no time, no code change allowed

   • Use Query Store forced plan as a stabilizer.

2. You own the code, skewed parameters, predictable workload

   • Use OPTIMIZE FOR specific value if one parameter dominates.

3. You own the code, unpredictable parameters

   • Use OPTIMIZE FOR UNKNOWN and validate with Query Store metrics.

4. Vendor app or legacy stack, but you have Query Store

   • Use Query Store hints where supported.

5. High-frequency OLTP vs. low-frequency reporting

   • For OLTP: be conservative; avoid heavy RECOMPILE.
   • For reporting / ETL: more freedom to use RECOMPILE or aggressive hints.

9. Practical Takeaway: Build a Parameter Sniffing Playbook

Don’t wait for the next production fire. Build a small, repeatable playbook:

1. Baseline: Enable Query Store with sensible retention.
2. Detect: Use Query Store to find queries with multiple plans and unstable durations.
3. Classify: Decide if the query is OLTP, reporting, or ETL.
4. Apply:
   • Forced plan (short-term), then
   • Code-level OPTIMIZE FOR or Query Store hints (longer-term).
5. Review: Revisit forced plans and hints periodically as data volumes and patterns change.

If you turn this into a habit, parameter sniffing becomes a controlled behavior rather than a random performance surprise.