Parameter sniffing is NOT a bug.
It’s SQL Server being too smart… sometimes.
😈 The Problem
SQL caches a plan for one parameter and reuses it:
EXEC GetOrdersByCustomer @customerId = 1; -- cached plan EXEC GetOrdersByCustomer @customerId = 9999; -- wrong plan reused
⚠ Symptoms
-
Extreme slowdowns
-
Terrible cardinality estimates
-
“It’s fast for me but slow for others!”
✔ Fix Options
-
🟡 Recompile selectively
OPTION (RECOMPILE)
-
🔵 Use OPTIMIZE FOR UNKNOWN
-
🟢 Add smarter indexes
-
🟣 Use table variables (carefully)
💡 Bonus:
SQL 2022 Intelligent Query Processing makes this MUCH better.