What is parameter sniffing in SQL, and how do you resolve performance issues caused by it?

Parameter Sniffing is a behavior in SQL Server and other database engines where a stored procedure or query caches an execution plan based on the first set of parameter values it receives. This cached plan is then used for subsequent executions, regardless of whether the new parameter values are optimal for that plan.

How Does Parameter Sniffing Work?

1. When a stored procedure or parameterized query is executed for the first time, the query optimizer generates an execution plan based on the given parameter values.
2. The execution plan is cached to avoid repeated optimization, improving performance for similar queries.
3. If future executions use different parameter values, the cached plan may not be optimal, leading to performance issues.

How to Fix Parameter Sniffing Issues?

1. Use Query Hints

OPTION (RECOMPILE): Forces a new execution plan for every execution.

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

OPTIMIZE FOR UNKNOWN: Ignores the specific parameter value and generates a generic plan.

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

1. Use Local Variables

Assign the parameter to a local variable inside the procedure to prevent SQL Server from using a parameter-specific plan.

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

1. Create Multiple Query Plans

Use if-else logic to handle different cases separately.

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

1. Manually Clear the Cache

If needed, clear the stored plan using:

DBCC FREEPROCCACHE