Most “solutions” online are myths.
Here’s the real fix.
🧨 The Problem
SQL Server caches a plan using the first parameter value.
If first call is small dataset → plan optimized for small
If next call is huge → BAD performance
✔ The Correct Fix (Rarely Understood)
Use OPTIMIZE FOR UNKNOWN:
SELECT * FROM Orders WHERE CustomerId = @id OPTION (OPTIMIZE FOR UNKNOWN);
SQL generates a generic plan instead of a specific one.
✔ Or use LOCAL variables inside SP
DECLARE @localId INT = @id; SELECT * FROM Orders WHERE CustomerId = @localId;
💡 Life-Saving Hint
Using RECOMPILE everywhere is a performance killer.
Do NOT do this blindly.