Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
SQL Server uses allocated server memory for different kind of purposes. Two of them are plan cache and data cache which are used to store execution plans and actual data correspondingly.
My question: Do these two caches have different allocated space section in Buffer pool, or in contrary, they have just one section in Buffer pool which they share between each other?
In other words, if plan cache is filling up, does space for data cache is reducing as well?
Yes. The Plan Cache and Page Cache share the available server memory. The Plan Cache is capped, as growing a large plan cache rarely provides much marginal benefit.
AFAIK, this old doc page is still accurate:
Memory Allocated To Plan Caching
Most memory used by SQL Server is allocated to the Buffer Pool, which is used to store data pages. SQL Server steals a proportion of this memory for use in caching query plans.
Cache Pressure Limit SQL Server 2008 and SQL Server 2005 SP2 [and later]
75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB
Which means that the plan cache will be trimmed when it exceeds that target, effectively capping the amount of memory used for plan caching.
