Thanks Paul!

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 things that are most unclear for me now are:

1) How are things different (if at all) in SQL 2000? The article targets 2005, but mentions procs in 2000...

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?)

I don't expect you to "answer" this, I understand it's probably food for hours of investigation and/or discussion; I'll save this for my "cool stuff to look at when I have extra time on my hands (never)" pile.