Sql Parameter Spoofing

This is kinda like a not-well-known 'secret' of MS-Sql.

If you have SQL that regularly runs in a query window faster than a sproc then you're suffering from Sql Parameter Spoofing.

Luckily the solution is trivial - in the sproc copy any parameters passed in to local variables.

The reasons are that the query optimizer thinks the parameter changes the plan everytime, whereas copying to a local means the query can determine only minor scalar variance, and thus keep the plan.

Anyway, just try it if you've seen the above symptoms.