Stored Procedure Running Slower Than Query (aka What does a parameter smell like?)

A colleague at work came to me with an interesting problem that he was at loss to figure out. Turns out a stored procedure he was working on was running like 10x slower than if he ran it as a regular query. I verified the ad hoc query and the SP were indeed identical and I could reproduce the performance issue. It did seem quite strange for there to be such a difference in execution times. A quick google turned up the issue. It seems that SQL Server 2000 has a feature called "parameter sniffing" where it looks at the parameters to a SP to help come up with an (optimal) execution plan. Long story short, in this case parameter sniffing was giving less than optimal results. A good explanation of parameter sniffing can be foundin this forum post. So if you ever wondered what a parameter smells like, there you go!