Part 164: Parameter Sniffing & Plan Cache Poisoning

When a parameterized query suddenly becomes 1000x slower due to an edge-case cached plan.

1. What is Parameter Sniffing?

To avoid recompiling execution plans for every query execution, relational engines compile and cache the plan using the parameters passed on the very first execution: 1. Execution 1 passes @Country = 'Iceland' (matches 5 rows). - Optimizer compiles an Index Seek + Nested Loop plan optimized for 5 rows. 2. Execution 2 passes @Country = 'United States' (matches 5,000,000 rows). - Optimizer reuses the cached plan! It runs a Nested Loop 5 million times instead of a Hash Join or Table Scan! - Result: High CPU, disk thrashing, and application timeouts.

2. Diagnosing Plan Cache Poisoning

If a query runs in 5 milliseconds for hours, and then suddenly spikes to 45 seconds without any schema or code change, suspect parameter sniffing.

3. Mitigation Strategies

Approach A: Query Recompile Hints

Forces the engine to generate a fresh plan on each execution:

-- SQL Server:
SELECT * FROM orders WHERE country = @Country OPTION (RECOMPILE);

-- PostgreSQL:
-- Disables generic plan caching in prepared statements:
SET plan_cache_mode = force_custom_plan;

Approach B: Optimize for Unknown / Average Distribution

Tells the engine to use average histogram density rather than sniffing the parameter value:

-- SQL Server:
SELECT * FROM orders WHERE country = @Country OPTION (OPTIMIZE FOR (@Country UNKNOWN));

Approach C: Local Variable Masking

CREATE PROCEDURE dbo.GetOrdersByCountry @Country VARCHAR(50) AS
BEGIN
    DECLARE @LocalCountry VARCHAR(50) = @Country;
    -- The optimizer cannot sniff local variables at compile time!
    SELECT * FROM orders WHERE country = @LocalCountry;
END;

Key Takeaways

• Parameter sniffing is intended as an optimization, but skewed data distributions turn it into a production hazard.

In Part 165, we explore Database Query Hints: When to Use & When to Avoid!