Tao Klerks (4/28/2009)
You've just added another dimension of complexity to SQL Performance tuning for me, now I have to go away and think about this. 🙂
The good news is that learning never stops. I start to worry if I don't come across something I didn't know every day...
Tao Klerks (4/28/2009)
1) How are things different (if at all) in SQL 2000? The article targets 2005, but mentions procs in 2000...
SS2K had the same issue, see http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
There are more options for working around this behaviour in 2K5 and 2K8 (which introduces OPTIMIZE FOR...UNKNOWN)
Tao Klerks (4/28/2009)
2) What is the relationship between query plan caching and parameter sniffing, eg in a stored procedure? Isn't parameter sniffing essentially doing the opposite of query plan caching? Are there circumstances under which "sniffed" parameters will cause a stored procedure to be recompiled? (or can multiple query plans be cached for a single proc, and be selected based on sniffed parameters?)
See http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/30/did-you-know-estimated-vs-actual-plans.aspx for information which, with a little thought, will allow you to answer this for yourself.
Enjoy!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi