If you're in the Kansas City area tomorrow night I'm going to be doing a presentation on the procedure cache, parameterization and parameter sniffing. Details are on the Kansas City SQL Server User Group web site. I'm a last minute fill in but this is a presentation I've been working on for a while. Just to get you started I'll post two queries that I use in the introduction:
This is a companion discussion topic for the original entry at http://billg.sqlteam.com/2007/05/09/presentation-understanding-the-procedure-cache-writing-efficient-reusable-queries/