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 EngineersFirst, 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:
- 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 BooksOnlineOf 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 TalksI'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:
- 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:
Need more help with problems?
- Signup to get my blog posts by email:
- Follow my blog RSS feed
- View the rest of my blog posts
- View my blog posts on the Adobe CF portal
- If you may prefer direct help, rather than digging around here/elsewhere or via comments, I can help via my consulting services
- See that for more on how I can help a) over the web, safely and securely, b) usually very quickly, c) teaching you as we go, and d) with satisfaction guaranteed