What is Parameter Sniffing?

When an SQL Server executes a stored procedure or a parameterized query, it sniffs (reads) the first set of parameter values and generates an execution plan optimized for those values. This plan is then cached and reused for subsequent executions.

Sounds great, right? Well, yes—if all parameter values result in similar execution patterns. But if different parameters require different execution strategies, SQL Server might reuse an inefficient plan, leading to serious performance issues.

Example of Parameter Sniffing Issue

Consider a table of Orders with millions of records:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(10,2)
);

Now, suppose we create a stored procedure to retrieve orders for a given customer:

CREATE PROCEDURE GetOrdersByCustomer @CustomerID INT  
AS  
BEGIN  
    SELECT * FROM Orders WHERE CustomerID = @CustomerID;
END;

Case 1. First Execution (Good Plan)

Case 2. Second Execution (Bad Plan)

This is the parameter sniffing problem—SQL Server optimized for one case, but that plan isn’t ideal for other cases.

Why Does Parameter Sniffing Happen?

SQL Server caches execution plans to avoid repeated compilation, which saves CPU and improves performance. However, this caching assumes that future queries will work well with the same execution plan, which isn’t always the case.

How to Fix Parameter Sniffing Issues

If parameter sniffing is causing slow queries, here are some ways to fix it:

1. Use OPTION (RECOMPILE)

This forces SQL Server to generate a fresh plan for every execution:

SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);

2. Use OPTIMIZE FOR UNKNOWN

This tells SQL Server not to use the first execution’s parameter value for optimization but instead generate a generic plan:

SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (OPTIMIZE FOR UNKNOWN);

3. Use Local Variables

Declaring a local variable prevents SQL Server from sniffing parameters, forcing it to optimize the query without specific values:

CREATE PROCEDURE GetOrdersByCustomer @CustomerID INT  
AS  
BEGIN  
    DECLARE @LocalCustomerID INT = @CustomerID;  
    SELECT * FROM Orders WHERE CustomerID = @LocalCustomerID;  
END;

4. Use IF-ELSE Logic for Different Plans

If your data is highly skewed, manually creating different plans can help:

IF @CustomerID < 100  
    SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (RECOMPILE);  
ELSE  
    SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (OPTIMIZE FOR UNKNOWN);

5. Manually Clear Cache (Temporary Fix)

If performance issues arise due to a bad cached plan, you can clear it:

DBCC FREEPROCCACHE;

Conclusion

Parameter Sniffing is a powerful optimization technique in SQL Server, but it can also cause performance issues when queries deal with highly variable data distributions.

Best Practices

By applying these strategies, you can balance performance and optimization, ensuring that your SQL Server queries run efficiently under various workloads.