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?
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
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