Despite this post's title looking a bit like a spam subject line, this is a serious post about an issue we ran into today at work. We have a stored procedure that gathers some statistics for us, and a really strange thing was happening with it. When run from SSMS, it took less than one second to execute, but when run from code, it actually timed out while executing over two minutes. Why would it run so fast through SSMS and yet so slow through our code?

The answer, it turns out, is a SQL Server feature called "parameter sniffing". It is supposed to optimize the query by looking at the actual parameters that are being passed in, instead of generating a generic execution plan. However, sometimes it can cause performance issues instead of alleviating them. The reasons for that are discussed in the Microsoft Query Optimization Team's blog.

The fastest and easiest solution is to completely disable parameter sniffing. To do that, simply declare a local variable for each passed in parameter, assign the parameter values to those variables, and use them instead of the parameters inside the query. While that might not be the most efficient thing to do as it skips some optimization, it solves the problem at hand.