Note: This blog post is from 2007. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.
Ever wanted to understand the SQL Server query plan/procedure cache better? It can be vital to good performance of SQL statements, especially with respect to the concept of "bind variables" or "parameterized queries" (using CFQUERYPARAM in a CFML context, or Parameters.Add() in a C# context, among other ways).
Update: If you may wonder why any of this is important, see some subsequent talks I gave on the topic, that give far more context on the problem. While you rarely hear about it from others, it is a source of potentially significant problems so is worth understanding more completely. See the "My Own Talks" section at the bottom of this entry.
Yet we so often just kind of assume the database will do what's best. Or we blindly use (or recommend use of) bind variables without fully understanding why, nor understanding some implications when it may not always be such a good idea.
And did you know that if you can't (or don't) change your code to cause parameterization, did you know that the database can do it for you, either automatically in some simple cases, or by way of an enforced parameterization, either for an entire table of for queries that meet certain "plan guides"? There are many implications to understand in all this.
And how do you track the plan cache (using DMVs, the profiler, perfmon)? And how does it work (allocation of memory, flushing the cache automatically or manually)?
Fortunately, I've found many great resources to help you understand, and I've not found them listed (all together) anywhere else yet.
The info applies to 2005, 2000, and 7, though some aspects may differ, as the resources indicate. (There are even substantive changes in 2005 SP2 that are important to note over the RTM and SP1 releases.)
Huge Plan Caching Article Series by MS Engineers
First, I'll point out that some MS engineers have put together a long and very resourceful series of article-length blog posts on the topic, with explanations, code, demos, troubleshooting techniques, and lots more.
They do kindly offer a "table of contents" page listing all the topics covered, with bullet points about topics within each entry.
Sadly, that page doesn't offer URLs to the articles, nor have any that I found there, nor do the articles link to each other. You can dig around and find the URLs, even finding a category page that lists them in rather random order. But I've looked around the web and can't find any page that lists them all with their links, in order, so here you go:
- Structure of the Plan Cache and Types of Cached Objects (original URL)
- Sql_Handle and Plan_Handle Explained (Original URL)
- How Cache Lookups Work
- Query Parameterization
- Retrieving Query Plans from Plan Cache DMV's
- Best Programming Practices
- Costing Cache Entries
- Factors that affect Batch Cache-ability
- Memory Pressure Limits
- Plan Cache Flush
- Temporary Tables, Table Variables and Recompiles
- Plan Cache Trace Events and Performance
They also offer a series of extensions to that, on troubleshooting:
Chapter in "Practical SQL Server 2005 Troubleshooting" book
Next, I'd point out that one of the contributors to that, Bart Duncan, is a Microsoft Support engineer who I saw speak on the topic at the SQL Server PASS 2006 conference, where I first learned a lot of this info.
He identified then that a lot of the info was in a chapter of a new book, SQL Server 2005 Practical Troubleshooting: The Database Engine, which I've since gotten and was indeed very useful.
If you're a member of the O'Reilly Safari service (or join for their trial), you can find the chapter online.
Of course, both the talk and the book came out before the article series above, and certainly before SP2, so I would recommend you consider both. (I've not yet done a comparison to determine what may be in the chapter that's not in the articles.)
SQL Server BooksOnline
Of course, it always pays to read the docs, and there are indeed some discussions of this concept and related features in the
SQL Server Books Online, but I honestly found the info above either easier to find or just more complete.
My Own Talks
I've made my own attempts to communicate some of this info myself in a couple of talks I've given to both CFML and SQL Server audiences:
But really, there is just too much to communicate in any one hour. If this topic seems of interest, take a few hours to digest all the above. I think you'll be AMAZED at what you learn.
The tool presents the data aggregated over time, so you can view it per hour, day, week, etc. Great for both drilling down to find hot spots, and for viewing how coding/config improvements (resulting from your responding to the analyses) have led to performance improvements over time. 
The tool runs with low overhead: it reads data that the DB provides, storing it in a database and providing a web-based interface to view that data. The process to read the data and create the repository (and present the web-based interface) can (and should) be done on a server separate from the server being monitored. 
