1

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?

2

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.

Plan Caching in SQL Server 2008

1
  • 1
    I was about to quote the same document, you beat me. I also believe this still holds true till SQL Server 2019.
    – Shanky
    May 26 '20 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.