Understanding Query Plan Cache in SQL 2005 SP2 - why it's changed from SQL 2005 RTM/SP1?

  • Article

Query execution plan gets stored in Plan Cache - the portion of the memory pool allocated for storing execution plans.   There is a very good blog written by SQL Programmability team on why Query Plan Cache behavior changed in SQL 2005 SP2 vs. SQL 2000 and SQL 2005 RTM/SP1  - https://blogs.msdn.com/sqlprogrammability/archive/2007/01/22/3-0-changes-in-caching-behavior-between-sql-server-2000-sql-server-2005-rtm-and-sql-server-2005-sp2.aspx

Based on this the plan cache size is different between SQL 2000, SQL 2005 SP1/RTM, and SQL 2005 SP2:

SQL Server Version Maximum Limit on Plan Cache Size
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

SQL Server 2000 4GB upper cap on the plan cache

Example:

For a SQL Server with 32Gb total SQL server memory, SQL Server 2005 RTM and SP1 cachestore limit will be 75% X 8 + 50% X (32 - 8) = 18GB

SQL Server 2005 SP2 cachestore limit will be 75% X 4 + 10% X (32-4) = 5.8GB

The reason for less maximum limit on Plan Cache in SQL 2005 SP2 is because Limiting the maximum size of the plan cache will ensure enough room for the database pages, and therefore improved throughput. 

A large page cache is good and normal, and increasing the size of a large page cache usually improves performance. 

A large plan cache is bad and abnormal, and increasing the size of a large plan cache rarely improves performance.

There are only a fixed number of pages in a database, so increasing the size of the page cache should always improve performance.  However there is no limit to the number of different SQL statements that a poorly written application can generate.  A very large plan cache usually indicates that you’re not getting much plan reuse, and caching more plans that don’t get reused isn’t a very good strategy.  Better to cap the plan cache and use that memory for the page cache instead.  

If you find that your plan cache is anywhere near the maximum that SQL will allow, you should fix the application to get more shared SQL and plan reuse through coding changes, forced parameterization, or plan guides.