SQL Server Parameter Sniffing: Diagnosis and Fixes for Bad Query Plans (2026)

How Parameter Sniffing Works

When SQL Server compiles a stored procedure or parameterized query for the first time, the optimizer peeks at the actual parameter values supplied. It uses those values to look up the statistics histogram and estimate row counts, then compiles a plan optimized for that cardinality.

-- Procedure compiled with @StatusID = 5 (rare status, 100 rows)
-- Optimizer estimates 100 rows → chooses Index Seek + Key Lookup
CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus
    @StatusID INT
AS
    SELECT OrderID, CustomerID, Total, OrderDate
    FROM dbo.Orders
    WHERE StatusID = @StatusID
    ORDER BY OrderDate DESC;

-- First call: rare status
EXEC dbo.GetOrdersByStatus @StatusID = 5;  -- compiles plan for 100 rows

-- Second call: common status (8 million rows) — reuses the seek+lookup plan!
EXEC dbo.GetOrdersByStatus @StatusID = 1;  -- disaster

The cached plan lives in the plan cache until it is evicted (memory pressure, explicit flush, or ALTER PROCEDURE). Every subsequent call reuses it regardless of what parameter is passed.

When Sniffing Goes Wrong

Sniffing causes problems specifically when:

• Data is skewed — a small fraction of parameter values account for most rows (status codes, country codes, category IDs)
• The first compilation uses an extreme value — a rarely-called value produces a hyper-specific plan (index seek) that is wrong for the majority of calls
• The plan was compiled during a maintenance window — bulk load or reindex flushed the plan cache; the first post-maintenance call compiled with an atypical value
• Statistics were updated mid-day — an auto-update triggered during peak hours caused a recompile with whatever parameters were active at that moment

Classic Scenario: Skewed Distribution

Consider an Orders table with a StatusID column distribution:

StatusID  Description      Row Count   % of Table
--------  ---------------  ----------  ----------
1         Completed        45,000,000    90%
2         Processing        4,000,000     8%
3         Pending             900,000     1.8%
4         Cancelled            90,000     0.18%
5         Refunded              10,000     0.02%

A plan compiled for StatusID = 5 (10,000 rows) will use an index seek + key lookup for a handful of rows. When that plan is reused for StatusID = 1 (45 million rows), SQL Server performs 45 million key lookups — each requiring a separate I/O into the clustered index. This is thousands of times slower than a clustered index scan would have been.

Conversely, a plan compiled for StatusID = 1 will choose a full table scan. When reused for StatusID = 5 it scans 50 million rows to return 10,000 — also terrible.

Diagnosing with DMVs

The plan cache DMVs let you find procedures with multiple plans (sign of recompile churn) or unusually high CPU/IO relative to execution count (sign of a bad plan):

-- Find cached plans with high average CPU — potential bad sniff
SELECT TOP 20
    OBJECT_NAME(qp.objectid)            AS proc_name,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count   AS avg_cpu_us,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count  AS avg_elapsed_us,
    qs.plan_generation_num,
    CAST(qp.query_plan AS NVARCHAR(MAX))        AS plan_xml
FROM sys.dm_exec_procedure_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE OBJECT_NAME(qp.objectid) IS NOT NULL
ORDER BY avg_logical_reads DESC;

-- Find the sniffed parameter values stored in the plan
SELECT
    OBJECT_NAME(qs.object_id)   AS proc_name,
    qp.query_plan,              -- look for  section
    qs.execution_count,
    qs.total_logical_reads
FROM sys.dm_exec_procedure_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE OBJECT_NAME(qs.object_id) = 'GetOrdersByStatus';

In the XML plan, look for the <ParameterList> node. It shows both ParameterCompiledValue (the sniffed value) and ParameterRuntimeValue (what the current call passed). When these differ significantly, you have a sniffing problem.

Diagnosing with Query Store

Query Store (SQL Server 2016+) makes sniffing diagnosis much easier — it tracks multiple plans per query and their runtime metrics:

-- Queries with multiple plans (parameter sniffing candidate)
SELECT
    q.query_id,
    qt.query_sql_text,
    COUNT(p.plan_id)                         AS plan_count,
    MAX(rs.avg_duration)                     AS max_avg_duration_us,
    MIN(rs.avg_duration)                     AS min_avg_duration_us,
    MAX(rs.avg_duration) / NULLIF(MIN(rs.avg_duration),0) AS duration_ratio
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(p.plan_id) > 1
   AND MAX(rs.avg_duration) / NULLIF(MIN(rs.avg_duration),0) > 10
ORDER BY duration_ratio DESC;

A duration_ratio greater than 10 means one plan variant runs 10× slower than another — classic sniffing signature. In SSMS, the Query Store "Regressed Queries" report shows this visually without writing SQL.

Reading the Execution Plan for Sniffing Evidence

When you capture an actual execution plan, right-click any operator and select Properties. Look for:

• Estimated Number of Rows — dramatically different from Actual Number of Rows (e.g., estimated 100, actual 4,500,000)
• Parameter List at the SELECT operator — shows compiled value vs runtime value
• Fat arrows — wide arrows between operators mean more rows than expected are flowing
• Key Lookup with thick warning triangle — lookup on millions of rows is a sniffing red flag

Fix 1: OPTION (RECOMPILE)

Adding OPTION (RECOMPILE) to the query forces a fresh compile on every execution. SQL Server sniffs the current parameter values, produces an optimal plan, executes it, then discards it. Nothing is cached.

CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus
    @StatusID INT
AS
    SELECT OrderID, CustomerID, Total, OrderDate
    FROM dbo.Orders
    WHERE StatusID = @StatusID
    ORDER BY OrderDate DESC
    OPTION (RECOMPILE);  -- compile fresh each time

When to use: Queries called infrequently (< a few times per second) with highly variable parameter selectivity. The compile overhead (~1–5 ms for a simple query) is acceptable when it prevents a 30-second scan.

Fix 2: OPTION (OPTIMIZE FOR)

OPTIMIZE FOR tells the optimizer to compile the plan as if a specific value (or UNKNOWN) were passed — regardless of what is actually supplied:

-- Compile as if @StatusID = 1 (the most common value)
SELECT OrderID, CustomerID, Total, OrderDate
FROM dbo.Orders
WHERE StatusID = @StatusID
ORDER BY OrderDate DESC
OPTION (OPTIMIZE FOR (@StatusID = 1));

-- Compile using average statistics (density) rather than a specific value
SELECT OrderID, CustomerID, Total, OrderDate
FROM dbo.Orders
WHERE StatusID = @StatusID
ORDER BY OrderDate DESC
OPTION (OPTIMIZE FOR (@StatusID UNKNOWN));

OPTIMIZE FOR UNKNOWN forces the optimizer to use the column's average density from the density vector rather than the sniffed histogram value. This produces a "middle ground" plan — not optimal for any single value, but acceptable for the full range.

Fix 3: Local Variables

SQL Server cannot sniff local variables — it has no way to inspect their values at compile time. Assigning a parameter to a local variable forces the optimizer to use average density statistics, identical to OPTIMIZE FOR UNKNOWN:

CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus
    @StatusID INT
AS
    DECLARE @LocalStatusID INT = @StatusID;  -- sniffing blocked

    SELECT OrderID, CustomerID, Total, OrderDate
    FROM dbo.Orders
    WHERE StatusID = @LocalStatusID
    ORDER BY OrderDate DESC;

This is a simple workaround but has the same trade-off as OPTIMIZE FOR UNKNOWN: the plan is generic, not optimal. Use it only when you cannot modify query hints (e.g., ORM-generated SQL that you can wrap in a procedure).

Fix 4: Multiple Stored Procedures

For two clearly distinct workload types — say, "small result" (rare statuses) and "large result" (common statuses) — two separate procedures let each have its own optimal cached plan:

-- For rare statuses (seek + lookup plan)
CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus_Small
    @StatusID INT
AS
    SELECT OrderID, CustomerID, Total, OrderDate
    FROM dbo.Orders
    WHERE StatusID = @StatusID
    ORDER BY OrderDate DESC;

-- For common statuses (scan plan)
CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus_Large
    @StatusID INT
AS
    SELECT OrderID, CustomerID, Total, OrderDate
    FROM dbo.Orders WITH (FORCESEEK)  -- or a different index hint
    WHERE StatusID = @StatusID
    ORDER BY OrderDate DESC;

-- Router procedure decides which to call
CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus
    @StatusID INT
AS
    IF @StatusID IN (1, 2)
        EXEC dbo.GetOrdersByStatus_Large @StatusID;
    ELSE
        EXEC dbo.GetOrdersByStatus_Small @StatusID;

This is the most explicit solution and produces genuinely optimal plans. The downside is added maintenance complexity.

Fix 5: Plan Guide

A plan guide lets you attach query hints to a query without modifying the source code — useful when the query comes from an ORM or third-party application:

-- Force RECOMPILE on a specific query text
EXEC sp_create_plan_guide
    @name = N'PG_GetOrdersByStatus',
    @stmt = N'SELECT OrderID, CustomerID, Total, OrderDate
              FROM dbo.Orders
              WHERE StatusID = @StatusID
              ORDER BY OrderDate DESC',
    @type = N'OBJECT',
    @module_or_batch = N'dbo.GetOrdersByStatus',
    @params = NULL,
    @hints = N'OPTION (RECOMPILE)';

-- Verify
SELECT name, scope_type_desc, hints, is_disabled
FROM sys.plan_guides
WHERE name = 'PG_GetOrdersByStatus';

Query Store: Force a Good Plan

If you identify a known-good plan in Query Store, you can force SQL Server to always use it regardless of what parameters are passed:

-- Find the good plan_id for a query
SELECT p.plan_id, p.query_id, rs.avg_duration, rs.count_executions
FROM sys.query_store_plan p
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE p.query_id = 42  -- replace with your query_id
ORDER BY rs.avg_duration;

-- Force the fastest plan
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 101;

-- Unforce when no longer needed
EXEC sys.sp_query_store_unforce_plan @query_id = 42, @plan_id = 101;

Forced plans persist across server restarts. They are the production-safe way to pin a known-good plan without touching application code.

Disabling Sniffing: DISABLE_PARAMETER_SNIFFING

SQL Server 2019+ and Azure SQL Database support a database-scoped configuration to disable sniffing globally or per-query:

-- Database level (use with caution)
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

-- Per-query hint (SQL Server 2019+)
SELECT OrderID, CustomerID, Total, OrderDate
FROM dbo.Orders
WHERE StatusID = @StatusID
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));

Decision Matrix: Which Fix to Use

Read Next

• Statistics: How They Work and Maintenance
• Wait Statistics: sys.dm_os_wait_stats
• How to Read and Interpret Query Plans
• Blocking and Deadlocks: Detection and Resolution
• All SQL Server Articles