Same query, different speed.
Why
Parameter sniffing.
Fix
Use OPTION (RECOMPILE) selectively.
🐌 Same Query, Different Speed
Nothing changed. Except the parameters.
If a query sometimes runs in 10 ms and sometimes in 10 seconds,
you’re probably not looking at a missing index.
You’re looking at parameter sniffing.
🚨 The Core Problem
SQL Server creates an execution plan based on the first parameter values it sees.
That plan is then cached and reused — even if future parameters behave very differently.
✔ Great for performance
❌ Terrible for uneven data distribution
💥 A Real-World Example
SELECT * FROM Orders WHERE CustomerId = @CustomerId
First execution
@CustomerId = 1 -- returns 5 rows
SQL Server chooses:
-
Nested loops
-
Index seek
-
“Small result set” plan
Next execution
@CustomerId = 9999 -- returns 500,000 rows
❌ Same plan
❌ Massive scan
❌ Query crawls
Same query.
Same indexes.
Different speed.
❌ Why This Happens
-
Execution plan is compiled once
-
First parameter “sniffs” the plan
-
Cached plan reused blindly
-
Data skew breaks assumptions
SQL Server isn’t wrong — it’s over-confident.
🛠 The Fix: OPTION (RECOMPILE)
SELECT * FROM Orders WHERE CustomerId = @CustomerId OPTION (RECOMPILE);
This forces SQL Server to:
-
Recompile the plan
-
Use current parameter values
-
Generate the optimal plan each time
⚠️ Use It Selectively
Recompiling is not free.
✔ Perfect for:
-
Highly variable parameters
-
Large data skew
-
Rare but critical queries
❌ Avoid on:
-
High-frequency queries
-
Simple lookups
-
OLTP hot paths
🧠 Alternative Strategies
-
Local variables (sometimes)
-
Dynamic SQL
-
OPTIMIZE FORhint -
Query splitting
But when in doubt:
Selective recompile beats unpredictable performance.
🧾 TL;DR
❌ Same query ≠ same speed
❌ Cached plan ≠ optimal plan
✅ Parameter sniffing is the culprit
✅ OPTION (RECOMPILE) fixes it
✅ Use it carefully
SQL Server didn’t slow down.
It just trusted the wrong first impression.