Ever since I upgraded from SQL Server 2000 to SQL Server 2005 I’ve seen significant plan cache bloat. Nothing’s changed in my application, so why the increase?

Filed under: Administration, Internals and Architecture, SQL Server 2005, Tuning and Optimization — Ari Weil at 7:56 am on Tuesday, January 29, 2008

Depending on how you look at things, this can be considered to be either a feature, a bug, or a miscalculation in the way caching was initially implemented in SQL Server 2005. Either way, as of SP2 this behavior has been changed.

Reading Microsoft’s whitepapers on Statistics Used by the Query Optimizer in Microsoft SQL Server and Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 provide lots of good information on what’s changed in plan caching and reuse between SQL Server 2000 and SQL Server 2005.

But, your question deals specifically with the size of the cache. Here’s a breakdown of the sizing by SQL Server version:

SQL Server 2000: 4GB upper cap on the plan cache
SQL Server 2005 RTM & SP1: 75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB
SQL Server 2005 SP2: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB

Do you see the difference? To use a practical example, consider a SQL Server with 64 GB total SQL Server memory.

SQL Server 2005 RTM and SP1 will end up with a cachestore limit of:
75% * 8 + 50% * ( 64 - 8 ) = 34GB

Now, if you upgrade that same SQL Server 2005 instance to SP2:
75% * 4 + 10% * ( 64 - 4 ) = 12GB

There is also a significant difference in query plan costing where the cache is concerned. For a detailed breakdown of these differences read the SQL Programmability & API Development Team Blog’s article on Changes in Caching Behavior between SQL Server 2000, SQL Server 2005 RTM and SQL Server 2005 SP2.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>