Understanding Parameter Sniffing in SQL Server

A query runs in 50 milliseconds all week. On Friday afternoon it takes 45 seconds. Nothing in the code changed. The server is not under unusual load. The indexes are all there. What happened?

In many cases the answer is parameter sniffing — one of the most common and most misunderstood performance problems in SQL Server. This article explains what it is, why it happens, how to confirm you have it, and the first steps toward fixing it. If you have already been through this article and need the full production toolkit including PSP optimization, Query Store plan forcing, and advanced fix strategies, jump directly to the Parameter Sniffing Field Guide.

1 What Parameter Sniffing Actually Is

In SQL Server, every time a stored procedure or parameterized query runs for the first time, the query optimizer examines the parameter values being passed in and uses them to choose an execution plan. That plan is stored in the plan cache. The next time the same query runs, SQL Server reuses the cached plan rather than building a new one from scratch — because building plans is expensive.

The act of the optimizer examining those first parameter values to build the plan is called parameter sniffing. The compiled parameter values are stored alongside the plan in the cache. The plan that gets built is specifically shaped around those initial values.

-- This is what happens behind the scenes when a stored procedure first executes:

-- Step 1: SQL Server sees the parameter value (@StatusId = 1)
-- Step 2: It looks at statistics to estimate how many rows StatusId = 1 will return
-- Step 3: It builds an execution plan optimized for that row count estimate
-- Step 4: It stores the plan in the cache WITH the compiled value (@StatusId = 1)
-- Step 5: Every future execution of this procedure reuses the same plan

CREATE PROCEDURE dbo.GetOrdersByStatus
    @StatusId TINYINT
AS
    SELECT OrderId, OrderDate, CustomerId, Amount
    FROM dbo.SalesOrder
    WHERE StatusId = @StatusId
    ORDER BY OrderDate DESC;
GO

-- First execution -- plan compiled for @StatusId = 1
EXEC dbo.GetOrdersByStatus @StatusId = 1;

-- Second execution -- reuses the plan compiled for @StatusId = 1
-- Even though the parameter is now 5
EXEC dbo.GetOrdersByStatus @StatusId = 5;

2 Why It Is Usually a Good Thing

Before treating parameter sniffing as a problem to be solved, it is worth understanding why SQL Server does it. Compiling an execution plan is not free. The optimizer has to evaluate statistics, consider multiple access strategies, estimate join costs, and choose between parallel and serial execution. For complex queries this can take tens of milliseconds. On a busy server running thousands of queries per second, that compilation overhead would be crippling if it happened on every execution.

Plan reuse is one of the foundational performance mechanisms in SQL Server. When the plan compiled for the first parameter value is also a good plan for most other values, parameter sniffing provides a real performance benefit across the workload. Most stored procedures on most servers benefit from it most of the time.

3 When It Goes Wrong — The Skewed Data Problem

Parameter sniffing becomes a problem when the data in your table is not uniformly distributed — when some parameter values return a handful of rows and others return millions. The optimizer builds a plan for the first value it sees. If that value is unusual compared to what most other executions use, the cached plan is wrong for almost everyone.

The two classic failure modes:

• Rare value compiled first → bad plan for common values. A stored procedure is first run with a very selective parameter (returns 5 rows). The optimizer chooses a Nested Loops seek plan — correct for 5 rows. The same plan is later used for a parameter that returns 5 million rows. Nested Loops on 5 million rows is catastrophic — it should have used a Hash Join with a scan.
• Common value compiled first → bad plan for rare values. The procedure is first run with a non-selective parameter (returns 5 million rows). The optimizer chooses a scan-based Hash Join plan. A later execution passes a very selective parameter that should return 3 rows. The scan-based plan reads millions of rows to find 3 of them.

4 A Concrete Example You Can Run

This demo creates a table with intentionally skewed data — 99,998 rows for StatusId = 1 and only 2 rows for StatusId = 0 — and shows the plan changing based on which parameter value compiles the cached plan first.

-- Run in a sandbox database -- creates a demo table
DROP TABLE IF EXISTS dbo.OrderDemo;
CREATE TABLE dbo.OrderDemo
(
    OrderId    INT IDENTITY PRIMARY KEY,
    CustomerId INT NOT NULL,
    StatusId   TINYINT NOT NULL,   -- 0 = rare (2 rows), 1 = common (99,998 rows)
    OrderDate  DATETIME2 NOT NULL,
    Amount     DECIMAL(10,2) NOT NULL,
    INDEX IX_OrderDemo_StatusId (StatusId) INCLUDE (OrderDate, Amount)
);

-- Insert 99,998 rows for StatusId = 1
INSERT dbo.OrderDemo (CustomerId, StatusId, OrderDate, Amount)
SELECT TOP(99998)
    ABS(CHECKSUM(NEWID())) % 5000,
    1,
    DATEADD(day, -ABS(CHECKSUM(NEWID())) % 730, SYSDATETIME()),
    CAST(ABS(CHECKSUM(NEWID())) % 1000 AS DECIMAL(10,2))
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

-- Insert only 2 rows for StatusId = 0
INSERT dbo.OrderDemo (CustomerId, StatusId, OrderDate, Amount)
VALUES (9999, 0, SYSDATETIME(), 99.99),
       (9998, 0, SYSDATETIME(), 49.99);
GO

-- Create the stored procedure
CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus
    @StatusId TINYINT
AS
    SELECT OrderId, OrderDate, Amount
    FROM dbo.OrderDemo
    WHERE StatusId = @StatusId
    ORDER BY OrderDate DESC;
GO

----------------------------------------------------------------------
-- SCENARIO A: Rare value compiled first
----------------------------------------------------------------------
-- Clear plan cache for this procedure only
DECLARE @handle VARBINARY(64);
SELECT @handle = plan_handle
FROM sys.dm_exec_procedure_stats
WHERE OBJECT_NAME(object_id) = 'GetOrdersByStatus';
IF @handle IS NOT NULL DBCC FREEPROCCACHE(@handle);

-- Enable actual execution plans (Ctrl+M in SSMS) before running these:

-- First execution: @StatusId = 0 (rare -- 2 rows)
-- Optimizer builds a seek/nested loops plan appropriate for 2 rows
EXEC dbo.GetOrdersByStatus @StatusId = 0;

-- Second execution: @StatusId = 1 (99,998 rows!)
-- Reuses the seek/nested loops plan built for 2 rows
-- This will do 99,998 Key Lookups instead of a scan -- watch the logical reads
EXEC dbo.GetOrdersByStatus @StatusId = 1;

----------------------------------------------------------------------
-- SCENARIO B: Common value compiled first
----------------------------------------------------------------------
DECLARE @handle2 VARBINARY(64);
SELECT @handle2 = plan_handle
FROM sys.dm_exec_procedure_stats
WHERE OBJECT_NAME(object_id) = 'GetOrdersByStatus';
IF @handle2 IS NOT NULL DBCC FREEPROCCACHE(@handle2);

-- First execution: @StatusId = 1 (common -- 99,998 rows)
-- Optimizer builds a scan plan appropriate for large result set
EXEC dbo.GetOrdersByStatus @StatusId = 1;

-- Second execution: @StatusId = 0 (only 2 rows)
-- Reuses the scan plan built for 99,998 rows
-- Scans the entire table to find 2 rows
EXEC dbo.GetOrdersByStatus @StatusId = 0;

-- Cleanup
DROP TABLE IF EXISTS dbo.OrderDemo;

Run each scenario with Include Actual Execution Plan enabled (Ctrl+M in SSMS). Watch the operator types change between Scenario A and Scenario B for the same procedure. The plan is identical for both executions within each scenario — it was locked in by the first run. That is parameter sniffing in action.

5 How to Confirm You Have a Parameter Sniffing Problem

Symptom Checklist

• The same stored procedure runs fast sometimes and slow other times with no code changes
• The query runs fast when you execute it directly in SSMS but slow when the application calls it
• Performance gets better after a server restart or plan cache flush — then slowly degrades again
• Query Store shows wide min/max duration variance for the same query text
• The execution plan shows Estimated Rows wildly different from Actual Rows

Detection Query 1 — Find the Compiled Parameter Values in Cache

-- Find cached plans for a specific stored procedure
-- and see what parameter values were used when the plan was compiled
SELECT
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count   AS [Avg Elapsed (us)],
    qs.total_logical_reads / qs.execution_count  AS [Avg Logical Reads],
    qs.creation_time                              AS [Plan Compiled At],
    -- The query_plan XML contains the compiled parameter values
    -- Open it in SSMS and look for  → 
    qp.query_plan
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = ps.plan_handle
WHERE OBJECT_NAME(ps.object_id) = 'GetOrdersByStatus'  -- replace with your proc name
AND ps.database_id = DB_ID();
------
-- Click the query_plan XML link in SSMS results
-- In the plan XML, find: 
-- Look for ParameterCompiledValue -- this is the value the plan was built for
-- Compare it to the parameter values the application is actually passing

Detection Query 2 — Find Unstable Queries via Query Store

-- Find queries with high performance variance in the last 7 days
-- High max_min_ratio on the same query text = strong parameter sniffing signal
-- Run in the context of the database you want to analyze
SELECT TOP(25)
    qsqt.query_sql_text,
    qsp.plan_id,
    qsrs.count_executions,
    ROUND(qsrs.avg_duration / 1000.0, 1)          AS [Avg Duration (ms)],
    ROUND(qsrs.min_duration / 1000.0, 1)          AS [Min Duration (ms)],
    ROUND(qsrs.max_duration / 1000.0, 1)          AS [Max Duration (ms)],
    CONVERT(DECIMAL(10,1),
        NULLIF(qsrs.max_duration, 0) /
        NULLIF(qsrs.min_duration, 1))              AS [Max/Min Ratio],
    qsrs.avg_logical_io_reads                      AS [Avg Logical Reads]
FROM sys.query_store_runtime_stats   qsrs
JOIN sys.query_store_plan            qsp  ON qsp.plan_id        = qsrs.plan_id
JOIN sys.query_store_query           qsq  ON qsq.query_id       = qsp.query_id
JOIN sys.query_store_query_text      qsqt ON qsqt.query_text_id = qsq.query_text_id
WHERE qsp.last_execution_time > DATEADD(day, -7, GETDATE())
AND qsrs.count_executions >= 10
ORDER BY [Max/Min Ratio] DESC;
------
-- A Max/Min Ratio of 10x or more on a query with many executions
-- is a strong indicator of parameter sniffing
-- Click into those queries in SSMS Query Store reports to compare plans

6 The Five Most Common Fixes

Parameter sniffing has no single universal fix. The right approach depends on your data distribution, query complexity, SQL Server version, and how much flexibility you have to change code. Here are the five most common options with honest trade-offs for each.

Quick Code Examples for Each Fix

-- Fix 1: OPTION (RECOMPILE) -- fresh plan every time
SELECT OrderId, OrderDate, Amount
FROM dbo.SalesOrder
WHERE StatusId = @StatusId
ORDER BY OrderDate DESC
OPTION (RECOMPILE);

-- Fix 2: OPTIMIZE FOR a specific value -- bias toward StatusId = 1 (most common)
SELECT OrderId, OrderDate, Amount
FROM dbo.SalesOrder
WHERE StatusId = @StatusId
ORDER BY OrderDate DESC
OPTION (OPTIMIZE FOR (@StatusId = 1));

-- Fix 3: OPTIMIZE FOR UNKNOWN -- use statistical average
SELECT OrderId, OrderDate, Amount
FROM dbo.SalesOrder
WHERE StatusId = @StatusId
ORDER BY OrderDate DESC
OPTION (OPTIMIZE FOR UNKNOWN);

-- Fix 4: Force a known-good plan via Query Store
-- Step 1: Find the query_id and plan_id of the good plan
SELECT qsp.query_id, qsp.plan_id,
       ROUND(qsrs.avg_duration/1000.0, 1) AS [Avg Duration (ms)]
FROM sys.query_store_plan qsp
JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq ON qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text qsqt ON qsqt.query_text_id = qsq.query_text_id
WHERE qsqt.query_sql_text LIKE '%GetOrdersByStatus%'
ORDER BY qsrs.avg_duration;
-- Step 2: Force the best plan (replace IDs with your actual values)
EXEC sys.sp_query_store_force_plan @query_id = 123, @plan_id = 456;

-- Fix 5: PSP Optimization (SQL Server 2022+) -- no code change needed
-- Just ensure these two conditions are met:
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160;  -- SQL Server 2022
-- Verify Query Store is on:
SELECT actual_state_desc FROM sys.database_query_store_options;
-- SQL Server builds multiple plans per parameter bucket automatically

7 What Not to Do

Do Not Use WITH RECOMPILE on the Entire Stored Procedure

Adding WITH RECOMPILE to the procedure definition recompiles the entire procedure on every execution — every statement inside it. If the procedure has ten statements and only one of them is sniffing-sensitive, you are paying recompilation overhead for all ten on every call. Use statement-level OPTION (RECOMPILE) on the specific query instead.

CREATE PROCEDURE dbo.GetOrders
    @StatusId TINYINT
WITH RECOMPILE  -- ALL statements recompile
AS
    -- Statement 1: sniffing-sensitive
    SELECT ... WHERE StatusId = @StatusId;
    -- Statement 2: not sniffing-sensitive
    SELECT ... FROM dbo.Config;

CREATE PROCEDURE dbo.GetOrders
    @StatusId TINYINT
-- No WITH RECOMPILE here
AS
    -- Only this statement recompiles each time
    SELECT ... WHERE StatusId = @StatusId
    OPTION (RECOMPILE);
    -- This one keeps its cached plan
    SELECT ... FROM dbo.Config;

Do Not Turn Off Parameter Sniffing Instance-Wide or Database-Wide as a First Response

Disabling parameter sniffing at the database level with ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF (equivalent to trace flag 4136) tells the optimizer to ignore parameter values entirely and always produce average-statistic plans. This might stabilize the procedure that was causing you pain — but it also degrades plans for every other procedure in the database that was benefiting from sniffing. It also disables PSP optimization. Treat this as a last resort, not a first response.

Do Not Flush the Entire Plan Cache in Production

DBCC FREEPROCCACHE without arguments removes every cached plan on the instance. Every query that runs immediately afterward must recompile from scratch — a massive CPU spike that can make an already struggling server much worse. If you need to evict a specific bad plan, target it by its plan handle.

-- Targeted plan eviction -- only removes the one bad plan
DECLARE @handle VARBINARY(64);
SELECT @handle = plan_handle
FROM sys.dm_exec_procedure_stats
WHERE OBJECT_NAME(object_id) = 'GetOrdersByStatus'
AND database_id = DB_ID();

IF @handle IS NOT NULL
    DBCC FREEPROCCACHE(@handle);

8 When to Go Deeper

This article covers the essential knowledge for understanding and first-diagnosing parameter sniffing. When you are ready for the production-level toolkit — live demo with skewed data, the full Query Store hunting queries, PSP optimization on SQL Server 2022, OPPO (Optional Parameter Plan Optimization) on SQL Server 2025, the “show your work” communication template for developers, and the proactive prevention checklist — the Field Guide has everything you need.

If you are working through a broader performance problem and are not sure whether parameter sniffing is the cause, the SQL Server Performance Tuning: The Complete DBA and Developer Guide covers the full diagnostic workflow from wait statistics through execution plan reading, with a decision table that maps plan symptoms to root causes.

References

• Microsoft Docs — Parameter Sensitive Plan Optimization (SQL Server 2022+)
• Microsoft Docs — Query Hints (RECOMPILE, OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN)
• Microsoft Docs — Monitoring Performance Using Query Store
• Brent Ozar — Parameter Sniffing Explained
• Erik Darling — The Art of the SQL Server Stored Procedure: Parameter Sniffing
• Erik Darling — Parameter Sensitivity Training (Data Community Summit 2025 material)
• SQLYARD — SQL Server Parameter Sniffing: A Field Guide for DBAs and Developers
• SQLYARD — SQL Server Performance Tuning: The Complete DBA and Developer Guide