[Looking for Charlie's main web site?]

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.
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:

  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:

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.

For more content like this from Charlie Arehart: 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
Comments
Great stuff Charlie.
Here are some SQL Server 2005 articles your readers may also find useful.

http://russ.michaels...
Thanks for sharing those, Russ. Hope they may help others.

Since you mention some may have challenges moving to 2005's Mgt Studio, and as long as we're offering tips to folks, I'll point out I did an entry in my other blog with some keyboard shortcuts for the tool:

http://www.tipicalch...
I'd like to follow up this post from 2007 (on the sometimes surprising performance problems related to CFQUERYPARAM) with an update pointing out several blog entries from others who went on to talk about it subsequently

http://www.matrixgro...

http://www.codersrev...

http://www.coldfusio...

http://www.bennadel....

http://blog.pengowor...

BTW, commenter Russ had offered in a previous comment a link to blog post of his, on related matters. It's no longer accessible but I found it in archive.org as being available at:

https://web.archive....://russ.michaels.me.uk/index.cfm/2007/8/15/SQL-Server-2005-security-and-best-practices
Still another follow-up, and just to show that this problem (of possible problems with SQL queries being sometimes fast, sometimes slow, due to plan caching issues), see this blog entry from which was pointed to by SQL Server troubleshooting expert, Brent Ozar, just today in his weekly blog post/email blast listing interesting SQL Server links (http://us2.campaign-...

He pointed to the blog of "database whisperer" Michael Stewart, who llast week discussed how he too had been burned by the issue and he offers some of the suggested solutions I did back in 2007:

http://michaeljswart...

Note that while he doesn't discuss is it in the context of CF (of course), it just reiterates that the problem is not unique to it. In fact, while when I first brought this up back then, it was in the context of cfqueryparam (and prepared statements), notice he is discussing it simply in the context of using stored procedure arguments input args to a query, which of course would also act like prepared statements.

Thought that may interest some, especially those who thought I was blathering on about some really esoteric problem. I think instead it's one of those things that few know even exists, and fewer still understand. Hope these resources (in the blog post, in my previous comment, and in this one) may help shed more light on the issue and help someone.
Copyright ©2024 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting