SQL Server Parameter Sniffing: A Field Guide for DBAs and Developers

1 What Parameter Sniffing Is and Why It Exists

When a parameterized query or stored procedure compiles, the optimizer examines the incoming parameter values and uses them to estimate how many rows each predicate will return. Those estimates drive every decision in the plan — which index to use, which join strategy to choose, whether to go parallel, how much memory to reserve. The compiled plan, along with the parameter values that produced it, is stored in the plan cache and reused for subsequent executions.

This is efficient. Building an execution plan is CPU-intensive. On a busy OLTP server processing thousands of queries per second, reusing cached plans avoids constant recompilation overhead. Parameter sniffing is the mechanism that makes plan reuse work, and on most servers with reasonably uniform data it works well most of the time.

The problem arises when the data distribution is skewed — 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 plan is wrong for the values that most executions use, every subsequent call pays the cost.

2 The Live Demo — Skewed Data in Action

Run this in a sandbox database to see the plan shift in real time. The table is intentionally skewed: 99,998 rows for Status = 1, only 2 rows for Status = 0.

-- Setup: skewed data
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
    OrderID    INT IDENTITY PRIMARY KEY,
    CustomerID INT NOT NULL,
    Status     TINYINT NOT NULL,   -- 0 = rare (2 rows), 1 = shipped (99,998 rows)
    OrderDate  DATETIME2 NOT NULL,
    INDEX IX_Orders_Status (Status) INCLUDE (OrderDate)
);

INSERT dbo.Orders (CustomerID, Status, OrderDate)
SELECT TOP(99998)
    ABS(CHECKSUM(NEWID())) % 50000,
    1,
    DATEADD(day, -ABS(CHECKSUM(NEWID())) % 365, SYSDATETIME())
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

INSERT dbo.Orders (CustomerID, Status, OrderDate)
VALUES (42, 0, SYSDATETIME()), (43, 0, SYSDATETIME());
GO

CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus
    @Status TINYINT
AS
    SELECT OrderID, OrderDate
    FROM dbo.Orders
    WHERE Status = @Status
    ORDER BY OrderDate DESC;
GO

-- Enable actual execution plans (Ctrl+M in SSMS) before running
-- SCENARIO: rare value compiled first
EXEC dbo.GetOrdersByStatus @Status = 0;   -- 2 rows  -- plan compiled here
EXEC dbo.GetOrdersByStatus @Status = 1;   -- 99,998 rows -- reuses bad plan

-- Compare the estimated vs actual rows on the index operator in each plan
-- The plan was built for 2 rows -- the second call pays the price

3 Reading the Evidence in Execution Plans

The execution plan is where parameter sniffing is confirmed — not suspected. Two things to look at:

Estimated vs Actual Rows

In SSMS with an Actual Execution Plan captured (Ctrl+M), hover over any operator. The tooltip shows Estimated Number of Rows and Actual Number of Rows. A massive discrepancy — estimated 2, actual 99,998 — is the proof. The plan was built for 2 rows and executed against 99,998.

Compiled vs Runtime Parameter Values

-- After capturing an actual plan, look at the plan XML for parameter values
-- In SSMS: right-click the plan → Show Execution Plan XML
-- Search for: 
-- You will find entries like:
--   
-- ParameterCompiledValue = what the plan was built for
-- ParameterRuntimeValue  = what was actually passed this execution
-- When these differ significantly AND performance is bad: confirmed sniffing mismatch

-- You can also see compiled values for cached plans without running the query:
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],
    qp.query_plan                                -- open XML in SSMS to find ParameterCompiledValue
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'
AND ps.database_id = DB_ID();

4 The Sniffing–Blocking Connection

Parameter sniffing and blocking are not the same problem, but they interact in a pattern that catches many DBAs off guard. A stored procedure with a sniffed plan that causes a full table scan — where a seek would be correct — holds shared locks on far more pages than necessary for the duration of the scan. On a busy server those extra lock holds can block writers and cause blocking chains that look completely unrelated to the query at the root of the problem.

The chain looks like this: sniffed plan → full scan instead of seek → shared locks held on millions of pages → writers blocked waiting for lock release → blocking chain grows → users report timeouts. Fixing the sniffing fixes the scan, which reduces the lock hold time, which resolves the blocking.

5 Hunting Queries — Query Store and Plan Cache

Find High-Variance Queries in Query Store

-- High max/min duration ratio = strong parameter sniffing signal
-- Run in the database context you want to analyze
SELECT TOP(50)
    qsq.query_id,
    qsp.plan_id,
    qsqt.query_sql_text,
    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 >= 5
ORDER BY [Max/Min Ratio] DESC;
------
-- Max/Min ratio above 10 with many executions = investigate
-- Look at query_id entries with multiple plan_ids -- multiple plans = confirmed sniffing

Find Queries with Multiple Plans for the Same Text

-- Same query text with multiple plans and a large performance gap
SELECT
    qsq.query_id,
    qsqt.query_sql_text,
    COUNT(DISTINCT qsp.plan_id)              AS [Plan Count],
    MIN(qsrs.avg_duration)                   AS [Best Avg Duration (us)],
    MAX(qsrs.avg_duration)                   AS [Worst Avg Duration (us)],
    MAX(qsrs.avg_duration) -
    MIN(qsrs.avg_duration)                   AS [Duration Gap (us)]
FROM sys.query_store_query             qsq
JOIN sys.query_store_query_text        qsqt ON qsqt.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan              qsp  ON qsp.query_id        = qsq.query_id
JOIN sys.query_store_runtime_stats     qsrs ON qsrs.plan_id        = qsp.plan_id
WHERE qsp.last_execution_time > DATEADD(day, -7, GETDATE())
GROUP BY qsq.query_id, qsqt.query_sql_text
HAVING COUNT(DISTINCT qsp.plan_id) > 1
AND MAX(qsrs.avg_duration) > MIN(qsrs.avg_duration) * 2
ORDER BY [Duration Gap (us)] DESC;

Surface Suspects from Plan Cache

-- Top queries by average elapsed time -- candidates for plan investigation
SELECT TOP(20)
    qs.plan_handle,
    qs.total_elapsed_time / qs.execution_count  AS [Avg Elapsed (us)],
    qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads],
    qs.execution_count,
    DB_NAME(st.dbid)                             AS [Database],
    st.[text]                                    AS [SQL Text]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_elapsed_time / qs.execution_count DESC;
-- Use the plan_handle from results to open the cached plan and check ParameterCompiledValue

6 Confirming the Mismatch — Compiled vs Runtime Values

A high Max/Min ratio is a signal, not a confirmation. Confirmation requires comparing the compiled parameter value stored in the cached plan against the runtime values that are actually causing poor performance.

-- Step 1: Capture an actual execution plan for the "slow" parameter value
-- In SSMS: Ctrl+M to enable actual plan, then run the procedure with the slow value
EXEC dbo.GetOrdersByStatus @Status = 1;  -- the one that is slow

-- Step 2: In the plan, right-click → Show Execution Plan XML
-- Search for: ParameterCompiledValue
-- If ParameterCompiledValue = "0" but you called it with Status = 1:
-- the plan was built for Status = 0 (2 rows) and is now being used for Status = 1 (99,998 rows)
-- That is your confirmation

-- Step 3: Capture the same plan for the "fast" parameter value to compare operators
EXEC dbo.GetOrdersByStatus @Status = 0;

-- Step 4: Compare the two plans side by side (SSMS: right-click plan → Compare Showplan)
-- Seek vs scan, Nested Loops vs Hash Join, memory grant size -- all will likely differ

7 Fix A — Targeted RECOMPILE All Versions

Adding OPTION (RECOMPILE) to a specific statement forces the optimizer to generate a fresh plan on every execution using the actual runtime parameter values. The key word is targeted — apply it to the specific statement that has the sensitivity problem, not to the entire procedure.

-- Targeted recompile on the sensitive statement only
CREATE OR ALTER PROCEDURE dbo.GetOrdersByStatus
    @Status TINYINT
AS
    SELECT OrderID, OrderDate
    FROM dbo.Orders
    WHERE Status = @Status
    ORDER BY OrderDate DESC
    OPTION (RECOMPILE);   -- this statement recompiles each time; proc-level plan remains cached
GO

Trade-offs to know:

• No plan caching benefit for this statement — CPU cost on every execution.
• SQL Server 2019 and later: OPTION (RECOMPILE) disables Memory Grant Feedback for the statement. If the query has memory grant issues alongside sniffing, you lose one of the automatic feedback mechanisms.
• Best for: procedures that run infrequently, have highly variable selectivity, or where the recompile CPU overhead is small relative to query execution time.
• Erik Darling’s preference: use statement-level RECOMPILE over WITH RECOMPILE on the procedure — it is more surgical and preserves plan cache benefit for other statements in the procedure.

8 Fix B — OPTIMIZE FOR and OPTIMIZE FOR UNKNOWN All Versions

OPTIMIZE FOR a Specific Value

This biases the compiled plan toward a value you specify. The plan is cached and reused, but it is built as if the parameter had the value you chose — not the value that happened to arrive first.

-- Bias toward Status = 1 (the most common value covering 99% of executions)
-- Rare calls with Status = 0 will get a suboptimal plan -- acceptable if they are rare
SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE Status = @Status
ORDER BY OrderDate DESC
OPTION (OPTIMIZE FOR (@Status = 1));

OPTIMIZE FOR UNKNOWN

Tells the optimizer to ignore the runtime value entirely and use column-level statistical averages instead. The result is a stable “middle of the road” plan that is neither optimal for any specific value nor catastrophically wrong for unusual ones.

-- Use average statistical density -- stable but not optimal for any specific case
SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE Status = @Status
ORDER BY OrderDate DESC
OPTION (OPTIMIZE FOR UNKNOWN);

9 Fix C — Query Store Plan Forcing SQL Server 2016+

When you have identified a known-good plan in Query Store, you can force it for future executions. This is the right short-term fix while you work on a permanent solution — it stops the bleeding without touching application code.

-- Step 1: Find query_id and plan_id for the target query
SELECT
    qsq.query_id,
    qsp.plan_id,
    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)],
    qsrs.count_executions,
    qsp.last_execution_time
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;
-- Pick the plan_id with the best avg_duration as your "good plan"

-- Step 2: Force it
EXEC sys.sp_query_store_force_plan
    @query_id = 123,   -- replace with your actual query_id
    @plan_id  = 456;   -- replace with the plan_id of the good plan

-- Step 3: Monitor for force 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 qsp
WHERE qsp.is_forced_plan = 1;
-- force_failure_count > 0 means the plan is no longer applicable (schema change, stats change)
-- Unforce and investigate when this happens
EXEC sys.sp_query_store_unforce_plan @query_id = 123, @plan_id = 456;

10 Fix D — Indexes and Statistics (the Real Root Cause Fix) All Versions

Hints and plan forcing are workarounds. The root cause of most parameter sniffing problems is that no single plan is good for all parameter values — which usually means the indexing is not adequate for the full range of access patterns the query needs to support. Brent Ozar makes this point clearly: covering indexes frequently eliminate the plan instability entirely because they give the optimizer a consistent, efficient access path regardless of selectivity.

-- Example: instead of letting the optimizer choose between
-- an index seek (for Status = 0) and a scan (for Status = 1),
-- a covering index with appropriate key columns can make both cases efficient

-- Before: index only on Status
CREATE NONCLUSTERED INDEX IX_Orders_Status
    ON dbo.Orders (Status) INCLUDE (OrderDate);

-- After: consider whether OrderDate as a key column
-- (if queries also filter or sort on OrderDate) helps further
CREATE NONCLUSTERED INDEX IX_Orders_Status_Date
    ON dbo.Orders (Status, OrderDate DESC) INCLUDE (CustomerID);
-- Now the optimizer can seek by Status AND have OrderDate in order
-- eliminating the Sort operator -- one consistent plan for all Status values

-- Always update statistics after significant data loads
-- Out-of-date statistics are the underlying cause of bad cardinality estimates
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Or for the specific index:
UPDATE STATISTICS dbo.Orders IX_Orders_Status WITH FULLSCAN;

11 Fix E — IF/ELSE Branching for Kitchen-Sink Procedures All Versions

For stored procedures with many optional predicates — the “search everything” pattern — the most effective fix is to branch the logic so that genuinely different access patterns execute as separate statements. Each branch can then compile its own optimal plan.

-- Kitchen-sink search procedure with optional parameters
-- The single SELECT tries to satisfy all combinations -- one plan cannot be optimal for all
CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @CustomerId INT        = NULL,
    @StatusId   TINYINT   = NULL,
    @StartDate  DATE       = NULL,
    @EndDate    DATE       = NULL
AS
-- Branching: handle the most selective cases as separate statements
-- Each branch compiles its own plan appropriate for its access pattern
IF @CustomerId IS NOT NULL AND @StatusId IS NOT NULL
BEGIN
    -- Both provided: narrow seek on CustomerId and StatusId
    SELECT OrderId, OrderDate, Amount
    FROM dbo.SalesOrder
    WHERE CustomerId = @CustomerId
    AND StatusId   = @StatusId
    AND (@StartDate IS NULL OR OrderDate >= @StartDate)
    AND (@EndDate   IS NULL OR OrderDate <  DATEADD(day,1,@EndDate))
    OPTION (RECOMPILE);  -- still volatile -- recompile this branch
END
ELSE IF @CustomerId IS NOT NULL
BEGIN
    -- CustomerId only: moderate selectivity
    SELECT OrderId, OrderDate, Amount
    FROM dbo.SalesOrder
    WHERE CustomerId = @CustomerId
    AND (@StartDate IS NULL OR OrderDate >= @StartDate)
    AND (@EndDate   IS NULL OR OrderDate <  DATEADD(day,1,@EndDate));
    -- No recompile -- CustomerId is usually selective enough for a stable plan
END
ELSE
BEGIN
    -- Date range or status only: wider scan -- needs its own plan
    SELECT OrderId, OrderDate, Amount
    FROM dbo.SalesOrder
    WHERE (@StatusId  IS NULL OR StatusId  = @StatusId)
    AND (@StartDate IS NULL OR OrderDate >= @StartDate)
    AND (@EndDate   IS NULL OR OrderDate <  DATEADD(day,1,@EndDate))
    OPTION (RECOMPILE);  -- still volatile without CustomerId
END

12 Fix F — PSP Optimization (SQL Server 2022+) SQL Server 2022

Parameter Sensitive Plan optimization is SQL Server 2022's automatic answer to the most common parameter sniffing scenarios. When enabled, the optimizer identifies predicates with skewed data distributions and creates multiple plan variants — one per "selectivity bucket" — for the same parameterized statement. Each variant has its own plan optimized for the row count range it covers. SQL Server routes each execution to the appropriate variant based on the runtime parameter value.

No code changes are required. The engine handles it transparently.

-- Requirements for PSP optimization:
-- 1. Database compatibility level 160 (SQL Server 2022 native)
-- 2. Query Store enabled and in READ_WRITE mode
-- 3. Parameter sniffing must NOT be disabled (TF 4136 or db-scoped config)

-- Verify requirements:
SELECT
    name,
    compatibility_level,
    is_query_store_on
FROM sys.databases
WHERE name = DB_NAME();

SELECT actual_state_desc, desired_state_desc
FROM sys.database_query_store_options;

-- Check database-scoped config for PARAMETER_SNIFFING
SELECT [name], [value]
FROM sys.database_scoped_configurations
WHERE name = 'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION';
-- 1 = enabled (default), 0 = disabled

-- Enable if needed:
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 160;
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- To disable PSP for a specific query without disabling globally:
SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE Status = @Status
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));

-- Monitor PSP activity via Extended Events
-- PSP creates dispatcher plans in the plan cache -- visible via:
SELECT
    cp.objtype,
    SUBSTRING(st.text, 1, 100) AS [SQL Text],
    cp.usecounts,
    cp.size_in_bytes
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.objtype = 'Prepared'
-- PSP variants appear as separate plan cache entries with different plan handles
-- for the same query text

13 Fix G — OPPO: Optional Parameter Plan Optimization (SQL Server 2025+) SQL Server 2025

SQL Server 2025 (compatibility level 170) extends the PSP architecture with Optional Parameter Plan Optimization — OPPO. It targets the classic "kitchen-sink search procedure" pattern: stored procedures with many optional predicates where @param IS NULL OR column = @param conditions make the access pattern wildly variable depending on which parameters are supplied.

OPPO applies the same multi-variant plan approach as PSP but for the optional predicate pattern, automatically maintaining different plans for different combinations of supplied vs NULL parameters. It is built on the same architecture as PSP and shares the same Extended Events infrastructure.

-- OPPO requirements:
-- SQL Server 2025 (build 17.x)
-- Database compatibility level 170
-- Query Store enabled
-- PSP optimization not disabled

-- Enable:
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 170;

-- OPPO is on by default at compat 170
-- Verify via database-scoped configuration:
SELECT [name], [value]
FROM sys.database_scoped_configurations
WHERE name = 'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION';

-- Note from the field (Erik Darling, 2025 Data Community Summit material):
-- OPPO was still new at release time and early real-world results were mixed.
-- Monitor affected procedures carefully after upgrading to compat 170.
-- PSP for equality predicates remains the more mature and reliable feature.

14 Last Resort — Database-Wide Sniffing Off

Disabling parameter sniffing at the database level tells the optimizer to never use runtime parameter values during compilation — it always produces average-statistic plans. This stabilizes the procedure that was causing pain but degrades every other procedure in the database that was benefiting from sniffing. It also disables PSP optimization entirely.

Apply only after exhausting all targeted fixes and after thorough testing. Expect some queries to get slower.

-- Database-scoped configuration (equivalent to TF 4136 per database)
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;

-- Revert:
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;

-- For a specific query only -- does not affect the rest of the database:
SELECT ... OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));

15 Short-Term Triage Without Touching the Cache Globally

When you are in a production incident and need to evict a bad plan immediately, target the specific plan — do not run DBCC FREEPROCCACHE without arguments. A global cache flush forces every query on the instance to recompile simultaneously, creating a CPU spike that can make a struggling server substantially worse.

-- Targeted flush for a specific stored procedure
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
BEGIN
    DBCC FREEPROCCACHE(@handle);
    PRINT 'Bad plan evicted. Next execution will recompile.';
END
ELSE
    PRINT 'Procedure not found in cache.';

-- If using Query Store: force the good plan immediately (preferred over cache flush)
-- This gives you control over WHICH plan runs next, not just clearing the bad one
EXEC sys.sp_query_store_force_plan @query_id = 123, @plan_id = 456;

16 Showing Your Work — Metrics for Developers and Leadership

Parameter sniffing investigations are often collaborative — you need to demonstrate the problem to developers before they will agree to change application code, and to leadership before resources are allocated for the fix. Having a clean before/after metric package makes both conversations faster.

Before/After Metric Template

-- Capture baseline before your fix
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Run the procedure with the problematic parameter value
EXEC dbo.GetOrdersByStatus @Status = 1;
-- Record: CPU time, elapsed time, logical reads from Messages tab

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

-- Apply fix (add OPTION RECOMPILE, create index, force plan, etc.)

-- Capture after metrics with identical parameter value
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
EXEC dbo.GetOrdersByStatus @Status = 1;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
-- Compare: logical reads reduction is the clearest evidence

Query Store Comparison

-- Export avg duration and logical reads before and after from Query Store
-- This is the most credible metric set for communication
SELECT
    qsp.plan_id,
    qsp.last_execution_time,
    qsp.is_forced_plan,
    ROUND(qsrs.avg_duration   / 1000.0, 1) AS [Avg Duration (ms)],
    ROUND(qsrs.max_duration   / 1000.0, 1) AS [Max Duration (ms)],
    ROUND(qsrs.avg_cpu_time   / 1000.0, 1) AS [Avg CPU (ms)],
    qsrs.avg_logical_io_reads              AS [Avg Logical Reads],
    qsrs.count_executions
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 qsp.last_execution_time DESC;

Minimum Documentation for a Change

• Problem: Procedure X running with parameter value Y compiled a plan for value Z — show the ParameterCompiledValue vs ParameterRuntimeValue from plan XML.
• Evidence: Before metrics — logical reads, avg duration, max duration from Query Store.
• Change: Exactly what was changed — one sentence, one line of code if possible.
• Why it works: One sentence explaining the mechanism — "adding OPTION RECOMPILE forces a fresh plan for each execution using actual runtime values."
• After metrics: Same Query Store metrics post-fix.
• Rollback: How to revert the change if needed.

17 Proactive Prevention

The best parameter sniffing fix is one you never need to apply. Most of the production incidents that turn into parameter sniffing investigations could have been prevented with a few design habits.

• Design procedures with stability in mind. Procedures that handle both highly selective and non-selective cases in a single statement are sniffing accidents waiting to happen on skewed data. If you know the access patterns will differ dramatically, branch the logic (Section 11) or use PSP on SQL Server 2022.
• Keep statistics current. Most sniffing problems are ultimately statistics problems — the optimizer made a wrong choice because its row count estimates were wrong. Scheduled statistics maintenance with appropriate sample rates reduces the frequency of bad plan compilations.
• Use adequate covering indexes. Covering indexes that give the optimizer a consistent, efficient path regardless of selectivity reduce the chance of plan divergence. A covering index often makes the "right plan" the same plan for all parameter values.
• Monitor Query Store for variance. Set up a weekly review of high Max/Min ratio queries. Catching sniffing problems before they become incidents is faster than firefighting them during a production outage.
• On SQL Server 2022: use compatibility level 160 and keep Query Store on. This enables PSP optimization and Memory Grant Feedback persistence — two features that reduce manual sniffing intervention substantially.
• Know your tradeoffs. RECOMPILE removes Memory Grant Feedback. OPTIMIZE FOR UNKNOWN produces mediocre plans. Disabling sniffing database-wide is a sledgehammer. The right fix depends on the query, the data, and the version. There is no one-size-fits-all answer.

References

• Microsoft Docs — Parameter Sensitive Plan Optimization (SQL Server 2022+)
• Microsoft Docs — Query Hints (RECOMPILE, OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN)
• Microsoft Docs — ALTER DATABASE SCOPED CONFIGURATION (PARAMETER_SNIFFING)
• Microsoft Docs — Monitoring Performance Using Query Store
• Microsoft Docs — Intelligent Query Processing Feature Catalog
• Brent Ozar — Parameter Sniffing: Causes, Effects, and Solutions
• Erik Darling — The Art of the SQL Server Stored Procedure: Parameter Sniffing
• Erik Darling — Parameter Sensitivity Training (Data Community Summit 2025)
• Kimberly Tripp (SQLskills) — Designing High-Performance Stored Procedures
• SQLYARD — Understanding Parameter Sniffing in SQL Server (Beginner Introduction)
• SQLYARD — SQL Server Performance Tuning: The Complete DBA and Developer Guide