Parameter Sniffing and Plan Reuse
In this lesson, you will learn how the SQL Server optimizer makes decisions based on the first set of parameters it "sniffs" and how this can cause performance bottlenecks.
The "First Impression" Problem
When you execute a stored procedure for the first time, the database engine compiles an execution plan. It looks at the parameters you passed, checks the statistics (histograms), and builds a plan tailored specifically for those values. This plan is then stored in the plan cache so the engine doesn't have to rebuild it next time.
However, if your data has "skew"—where some values are common and others are rare—this reuse can become a liability. Consider this procedure:
CREATE PROCEDURE GetOrdersByStatus @Status VARCHAR(10)
AS
SELECT * FROM Orders WHERE Status = @Status;
When Reuse Backfires
Imagine you run EXEC GetOrdersByStatus 'Shipped'. Because 'Shipped' represents 99% of your table, the optimizer chooses a Table Scan, as it's faster than jumping back and forth to an index. The engine caches this "Scan" plan.
Later, you run EXEC GetOrdersByStatus 'Cancelled'. Only 5 orders are cancelled. Ideally, the engine should use an Index Seek. But since the plan is already in the cache, the engine "sniffs" the initial 'Shipped' plan and forces the system to perform a massive table scan for just 5 records. You are now suffering from performance variability: the procedure is fast for common inputs but agonizingly slow for rare ones.
Mitigating the Variability
To fix this, we can force the optimizer to look at the parameters every single time by adding the WITH RECOMPILE option to the procedure definition:
CREATE PROCEDURE GetOrdersByStatus @Status VARCHAR(10)
WITH RECOMPILE
AS
SELECT * FROM Orders WHERE Status = @Status;
While WITH RECOMPILE adds a tiny bit of CPU overhead for every execution because it skips the cache, it ensures that every query gets an execution plan perfectly suited to the specific data it needs to retrieve, eliminating the "first impression" bias.
Key Takeaway: Parameter sniffing occurs when a cached plan is reused for different inputs, so use WITH RECOMPILE if your data distribution is skewed and specific inputs require drastically different execution strategies.