Resources for getting a much greater understanding of the SQL Server query plan/procedure cache

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.

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

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:

1. Structure of the Plan Cache and Types of Cached Objects (original URL)
2. Sql_Handle and Plan_Handle Explained (Original URL)
3. How Cache Lookups Work
4. Query Parameterization
5. Retrieving Query Plans from Plan Cache DMV's
6. Best Programming Practices
7. Costing Cache Entries
8. Factors that affect Batch Cache-ability
9. Memory Pressure Limits
10. Plan Cache Flush
11. Temporary Tables, Table Variables and Recompiles
12. Plan Cache Trace Events and Performance

They also offer a series of extensions to that, on troubleshooting:

• Machine Configuration Information That Can Impact Plan Cache Size/Performance
• Diagnosing Plan Cache Related Performance Problems and Suggested Solutions
• Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2
• Useful Queries on DMV's to understand Plan Cache Behavior

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

My Own Talks

• One for CF audiences, Understanding, Improving, and Resolving Issues with Database Prepared Statements, or the Performance Impact of CFQUERYPARAM
• And one for SQL Server Admins, Understanding, Improving, and Resolving Issues with the SQL Server Procedure Cache

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.

For more content like this from Charlie Arehart:

• Signup to get his blog posts by email:
• Follow his blog RSS feed
• View the rest of his blog posts
• View his blog posts on the Adobe CF portal

Need more help with problems?

• If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
• See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed