Parameter Sniffing

What is Parameter Sniffing?

Parameter Sniffing is the process where SQL Server "sniffs" parameter values during the compilation of stored procedures or parameterized queries.

SQL Server uses the parameter values passed during the first compilation to generate an execution plan optimized for those values. This plan is stored in the plan cache and reused for subsequent executions.

Plan Generation Flow

┌────────────────────────────────┐
│ 1. Stored Procedure Call       │
│    EXEC GetOrders @Status = 'A'│
└──────────┬─────────────────────┘
           │
           ▼
┌────────────────────────────────┐
│ 2. Parameter Sniffing          │
│    Recognizes @Status = 'A'    │
└──────────┬─────────────────────┘
           │
           ▼
┌────────────────────────────────┐
│ 3. Query Optimizer             │
│    Generates plan optimized    │
│    for 'A'                     │
└──────────┬─────────────────────┘
           │
           ▼
┌────────────────────────────────┐
│ 4. Save to Plan Cache          │
└──────────┬─────────────────────┘
           │
           ▼
┌────────────────────────────────┐
│ 5. Subsequent Execution        │
│    EXEC GetOrders @Status = 'B'│
│    -> Reuses same plan         │
└────────────────────────────────┘

Scenarios Where Parameter Sniffing Occurs

Parameter Sniffing occurs in the following types of queries:

Stored Procedures
Queries via sp_executesql
Prepared Queries

-- Example 1: Stored Procedure
CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(10)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID
    FROM Orders
    WHERE Status = @Status;
END;

-- Example 2: sp_executesql
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT * FROM Orders WHERE Status = @Status';
EXEC sp_executesql @SQL, N'@Status NVARCHAR(10)', @Status = 'Active';

-- Example 3: Prepared Query (from ADO.NET etc.)
-- Using Parameter markers
SELECT * FROM Orders WHERE Status = ?

Parameter Sniffing Problem (PSP: Parameter Sensitive Plan)

Essence of the Problem

The problem occurs when the plan optimized for the parameter values at the first compilation is not optimal for different parameter values.

Concrete Example

-- Orders Table
-- Status Column Distribution:
-- 'Active': 100,000 rows (95%)
-- 'Completed': 5,000 rows (5%)

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status;
END;

Scenario 1: First Compiled with 'Active'

-- First Execution
EXEC GetOrdersByStatus @Status = 'Active';

Generated Plan:

Rows with Status 'Active' are 95% of total (100,000 rows)
Optimizer selects Table Scan
Efficient when returning large amount of rows

Execution Plan:
┌─────────────────┐
│  Table Scan     │
│  (Orders)       │
│  Filter: Status │
└─────────────────┘

Problem:

-- Subsequent Execution
EXEC GetOrdersByStatus @Status = 'Completed';

Rows with Status 'Completed' are only 5,000 rows (5%)
However, the plan for 'Active' (Table Scan) is reused
Index Seek would be more efficient but is not used
Scans unnecessary 95,000 rows

Scenario 2: First Compiled with 'Completed'

-- First Execution
EXEC GetOrdersByStatus @Status = 'Completed';

Generated Plan:

Rows with Status 'Completed' are 5% (5,000 rows)
Optimizer selects Index Seek
Efficiently retrieves small amount of rows

Execution Plan:
┌─────────────────┐
│  Index Seek     │
│  (IX_Status)    │
│  Seek: Status   │
└─────────────────┘

Problem:

-- Subsequent Execution
EXEC GetOrdersByStatus @Status = 'Active';

Rows with Status 'Active' are 100,000 rows
However, the plan for 'Completed' (Index Seek) is reused
Using Index Seek for 100,000 rows is inefficient
Consumes massive CPU and I/O

Impact on Performance

Scenario	Optimal Plan	Used Plan	Impact
First compile 'Active' -> Run 'Completed'	Index Seek	Table Scan	Scans unnecessary 95,000 rows
First compile 'Completed' -> Run 'Active'	Table Scan	Index Seek	Inefficient with 100,000 seek operations

Detecting Parameter Sniffing Problems

1. Checking Plan Cache

-- Check if different plans exist for the same query
SELECT 
    cp.plan_handle,
    cp.usecounts,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu,
    qs.total_elapsed_time / qs.execution_count AS avg_duration,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_cached_plans AS cp
INNER JOIN sys.dm_exec_query_stats AS qs 
    ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE '%GetOrdersByStatus%'
ORDER BY avg_cpu DESC;

2. Using Query Store

-- Check different plans for the same query in Query Store
SELECT 
    q.query_id,
    qt.query_sql_text,
    p.plan_id,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.count_executions
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt 
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p 
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs 
    ON p.plan_id = rs.plan_id
WHERE qt.query_sql_text LIKE '%GetOrdersByStatus%'
ORDER BY q.query_id, rs.avg_duration DESC;

3. Sudden Changes in Performance

-- Detect large fluctuations in execution time
SELECT 
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.min_elapsed_time AS min_elapsed_ms,
    qs.max_elapsed_time AS max_elapsed_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.max_elapsed_time > (qs.min_elapsed_time * 10)  -- Difference of 10x or more
ORDER BY qs.max_elapsed_time - qs.min_elapsed_time DESC;

Solutions for Parameter Sniffing Problems

Solution 1: OPTION (RECOMPILE) Hint

Recompiles the query every time and generates the optimal plan for the current parameter values.

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (RECOMPILE);
END;

Pros:

✅ Always optimal plan
✅ Easy to implement

Cons:

❌ Compilation overhead (Increased CPU usage)
❌ Not suitable for frequently executed queries

Applicable Scenarios:

Queries executed infrequently
Queries where execution plan differs significantly by parameter value

Solution 2: OPTION (OPTIMIZE FOR @parameter = value)

Optimizes the plan for a specific parameter value.

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (OPTIMIZE FOR (@Status = 'Active'));
END;

Pros:

✅ Plan cache reuse
✅ Predictable performance

Cons:

❌ Optimized for fixed value (No flexibility)
❌ Need to identify optimal value

Applicable Scenarios:

Specific parameter value is used frequently
95% of workload runs with specific value

Solution 3: OPTION (OPTIMIZE FOR UNKNOWN)

Generates a plan using the average density of statistics.

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (OPTIMIZE FOR UNKNOWN);
END;

Equivalent Method: Using Local Variables

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    -- Copy parameter to local variable
    DECLARE @LocalStatus NVARCHAR(20);
    SET @LocalStatus = @Status;
    
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @LocalStatus;  -- Use local variable
END;

Pros:

✅ Plan cache reuse
✅ Stably obtain "reasonably good" plan

Cons:

❌ May not be optimal
❌ Inefficient if data distribution is skewed

Applicable Scenarios:

Parameter values are evenly distributed
Stable performance is important

Solution 4: OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Completely disables Parameter Sniffing.

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'));
END;

Pros:

✅ Same effect as OPTIMIZE FOR UNKNOWN
✅ Explicit expression of intent

Cons:

❌ Available only in SQL Server 2016 and later

Solution 5: OPTION (KEEPFIXED PLAN)

Prevents plan recompilation.

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20)
AS
BEGIN
    SELECT OrderID, OrderDate, CustomerID, TotalAmount
    FROM Orders
    WHERE Status = @Status
    OPTION (KEEPFIXED PLAN);
END;

Pros:

✅ Prevent plan change due to statistics update
✅ Keep "good plan" in cache

Cons:

❌ Cannot adapt to data changes
❌ Initial plan is critical

Applicable Scenarios:

Stable data distribution
Want to avoid frequent plan recompilation

Solution 6: DBCC FREEPROCCACHE (Temporary Fix)

Clears the plan cache.

-- Clear only specific plan
DBCC FREEPROCCACHE (plan_handle);

-- Clear all plans (Not recommended)
DBCC FREEPROCCACHE;

Caution:

⚠️ Temporary solution
⚠️ All plans will be recompiled
⚠️ Use with caution in production environment

Solution 7: Parameter Sensitive Plan Optimization (SQL Server 2022+)

SQL Server 2022 and later introduced PSP optimization features.

-- Automatically enabled with database compatibility level 160
ALTER DATABASE MyDatabase
SET COMPATIBILITY_LEVEL = 160;

Behavior:

Generates multiple plans for each range of parameter values
Automatically selects appropriate plan at execution time

┌─────────────────────────────┐
│ Dispatcher Plan             │
│ (Manages multiple variants) │
└──────────┬──────────────────┘
           │
    ┌──────┴──────┬──────────┐
    │             │          │
    ▼             ▼          ▼
┌────────┐  ┌────────┐  ┌────────┐
│Plan 1  │  │Plan 2  │  │Plan 3  │
│Range:  │  │Range:  │  │Range:  │
│1-1000  │  │1001-   │  │Unknown │
│        │  │10000   │  │        │
└────────┘  └────────┘  └────────┘

Enabling:

-- Database level (Enabled by default)
ALTER DATABASE SCOPED CONFIGURATION 
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

-- Disable at query level
SELECT * FROM Orders WHERE Status = @Status
OPTION (USE HINT ('DISABLE_PARAMETER_SENSITIVE_PLAN'));

Pros:

✅ Automatically selects optimal plan
✅ No application changes required

Constraints:

⚠️ SQL Server 2022 and later only
⚠️ Requires compatibility level 160

Solution Selection Guide

Scenario	Recommended Solution	Reason
Low execution frequency	RECOMPILE	Compilation cost is acceptable
Specific value is frequent	OPTIMIZE FOR value	Optimize for majority
Even parameter distribution	OPTIMIZE FOR UNKNOWN	Stable performance
SQL Server 2022+	PSP Optimization	Automatic optimization
Data distribution changes easily	RECOMPILE	Always use latest statistics
High frequency + Stable plan	KEEPFIXED PLAN	Prevent recompilation

Practical Example: Comprehensive Approach

Dynamic Solution

CREATE PROCEDURE GetOrdersByStatus
    @Status NVARCHAR(20),
    @OptimizationHint NVARCHAR(20) = NULL  -- 'RECOMPILE', 'UNKNOWN', NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Get statistics
    DECLARE @RowCount INT;
    SELECT @RowCount = COUNT(*)
    FROM Orders
    WHERE Status = @Status;
    
    -- Process dynamically based on row count
    IF @RowCount < 1000
    BEGIN
        -- Small data: Force Index Seek
        SELECT OrderID, OrderDate, CustomerID, TotalAmount
        FROM Orders WITH (INDEX(IX_Orders_Status))
        WHERE Status = @Status;
    END
    ELSE IF @OptimizationHint = 'RECOMPILE'
    BEGIN
        -- Use RECOMPILE
        SELECT OrderID, OrderDate, CustomerID, TotalAmount
        FROM Orders
        WHERE Status = @Status
        OPTION (RECOMPILE);
    END
    ELSE
    BEGIN
        -- Default: OPTIMIZE FOR UNKNOWN
        SELECT OrderID, OrderDate, CustomerID, TotalAmount
        FROM Orders
        WHERE Status = @Status
        OPTION (OPTIMIZE FOR UNKNOWN);
    END
END;

Monitoring and Alerting

-- Detect Parameter Sniffing issues in plan cache
WITH PlanStats AS
(
    SELECT 
        qs.sql_handle,
        qs.plan_handle,
        qs.execution_count,
        qs.total_elapsed_time / qs.execution_count AS avg_elapsed,
        qs.min_elapsed_time,
        qs.max_elapsed_time,
        CASE 
            WHEN qs.min_elapsed_time > 0 
            THEN CAST(qs.max_elapsed_time AS FLOAT) / qs.min_elapsed_time
            ELSE 0
        END AS elapsed_variance_ratio
    FROM sys.dm_exec_query_stats AS qs
)
SELECT 
    ps.execution_count,
    ps.avg_elapsed / 1000 AS avg_elapsed_ms,
    ps.min_elapsed_time / 1000 AS min_elapsed_ms,
    ps.max_elapsed_time / 1000 AS max_elapsed_ms,
    ps.elapsed_variance_ratio,
    SUBSTRING(st.text, 1, 500) AS query_text
FROM PlanStats ps
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st
WHERE ps.elapsed_variance_ratio > 10  -- Difference of 10x or more
  AND ps.execution_count > 10  -- Sufficient execution count
ORDER BY ps.elapsed_variance_ratio DESC;

Best Practices

1. Design Phase

✅ Understand distribution of parameter values
✅ Keep statistics up to date
✅ Enable and monitor Query Store

-- Enable Query Store
ALTER DATABASE MyDatabase
SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024
);

2. Development Phase

✅ Test with various parameter values
✅ Check execution plans
✅ Record performance metrics

-- Check execution plan
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

EXEC GetOrdersByStatus @Status = 'Active';
EXEC GetOrdersByStatus @Status = 'Completed';

3. Operation Phase

✅ Regularly check Query Store
✅ Monitor performance anomalies
✅ Flush plans if necessary

4. SQL Server 2022+

✅ Leverage PSP Optimization
✅ Use compatibility level 160
✅ Check multiple plans in Query Store

Summary

About Parameter Sniffing

✅ Parameter Sniffing is usually a beneficial feature
⚠️ Becomes a problem when optimality of execution plan differs by parameter value
🎯 "Correct plan for correct parameter" is important

Signs of Problem

Execution time fluctuates significantly for the same query
Different plans exist in plan cache
Significantly slow with specific parameter values

Solution Selection

Priority	Solution	Condition
1	PSP Optimization	SQL Server 2022+
2	OPTIMIZE FOR value	Specific value is frequent
3	OPTIMIZE FOR UNKNOWN	Even distribution
4	RECOMPILE	Low frequency execution

Important Notes

❌ DBCC FREEPROCCACHE is only a temporary fix
❌ RECOMPILE is not needed for all queries
✅ Understand data distribution and execution patterns
✅ Continuously monitor with Query Store

What is Parameter Sniffing?​

Plan Generation Flow​

Scenarios Where Parameter Sniffing Occurs​

Parameter Sniffing Problem (PSP: Parameter Sensitive Plan)​

Essence of the Problem​

Concrete Example​

Scenario 1: First Compiled with 'Active'​

Scenario 2: First Compiled with 'Completed'​

Impact on Performance​

Detecting Parameter Sniffing Problems​

1. Checking Plan Cache​

2. Using Query Store​

3. Sudden Changes in Performance​

Solutions for Parameter Sniffing Problems​

Solution 1: OPTION (RECOMPILE) Hint​

Solution 2: OPTION (OPTIMIZE FOR @parameter = value)​

Solution 3: OPTION (OPTIMIZE FOR UNKNOWN)​

Solution 4: OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))​

Solution 5: OPTION (KEEPFIXED PLAN)​

Solution 6: DBCC FREEPROCCACHE (Temporary Fix)​

Solution 7: Parameter Sensitive Plan Optimization (SQL Server 2022+)​

Solution Selection Guide​

Practical Example: Comprehensive Approach​

Dynamic Solution​

Monitoring and Alerting​

Best Practices​

1. Design Phase​

2. Development Phase​

3. Operation Phase​

4. SQL Server 2022+​

Summary​

About Parameter Sniffing​

Signs of Problem​

Solution Selection​

Important Notes​

Reference Links​

What is Parameter Sniffing?

Plan Generation Flow

Scenarios Where Parameter Sniffing Occurs

Parameter Sniffing Problem (PSP: Parameter Sensitive Plan)

Essence of the Problem

Concrete Example

Scenario 1: First Compiled with 'Active'

Scenario 2: First Compiled with 'Completed'

Impact on Performance

Detecting Parameter Sniffing Problems

1. Checking Plan Cache

2. Using Query Store

3. Sudden Changes in Performance

Solutions for Parameter Sniffing Problems

Solution 1: OPTION (RECOMPILE) Hint

Solution 2: OPTION (OPTIMIZE FOR @parameter = value)

Solution 3: OPTION (OPTIMIZE FOR UNKNOWN)

Solution 4: OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))

Solution 5: OPTION (KEEPFIXED PLAN)

Solution 6: DBCC FREEPROCCACHE (Temporary Fix)

Solution 7: Parameter Sensitive Plan Optimization (SQL Server 2022+)

Solution Selection Guide

Practical Example: Comprehensive Approach

Dynamic Solution

Monitoring and Alerting

Best Practices

1. Design Phase

2. Development Phase

3. Operation Phase

4. SQL Server 2022+

Summary

About Parameter Sniffing

Signs of Problem

Solution Selection

Important Notes

Reference Links