What is Query Sniffing in MS SQL Server?

Query sniffing, often called parameter sniffing, is a behavior in SQL Server where the query optimizer “sniffs” the parameter values passed to a stored procedure or parameterized query the first time it compiles the execution plan.

How It Works

When you execute a stored procedure or parameterized query, SQL Server looks at the actual parameter values during the first execution. It uses these values to create an execution plan optimized for those specific parameters. This plan is then cached and reused for subsequent executions — even if those later executions have very different parameter values.

Why Is This Important?

If the parameter values vary widely and need different execution strategies, reusing the same plan may cause poor performance because the cached plan might be perfect for the initial parameters but inefficient for others.

Example

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

Suppose the first execution uses @CustomerID = 1, which has only a few orders. SQL Server generates a plan optimized for a small result set. Later, the procedure is executed with @CustomerID = 1000, who has thousands of orders. The cached plan may still be optimized for small sets, resulting in slow queries.

How to Detect Parameter Sniffing Issues

• Queries that run fast sometimes and slow other times with different parameters.
• Execution plans reused but not optimal for all parameter values.
• Large differences between estimated and actual row counts.

How to Fix or Avoid Parameter Sniffing

Method	Description
OPTION (RECOMPILE)	Forces a new plan compilation for each execution.
WITH RECOMPILE	Stored procedure recompiles on every execution.
OPTIMIZE FOR hint	Tells optimizer to use a specific parameter value.
Use local variables	Prevents optimizer from sniffing parameters directly.
Plan guides	Advanced control over query optimization.
Query rewrite	Splitting queries or procedures based on parameter patterns.

Summary

Parameter sniffing is a performance optimization feature but can cause unexpected slowdowns when data or parameters vary greatly. Understanding and managing it properly helps maintain consistent query performance.