1

SQL Server Standard Edition has a limit to how much memory it can use for the data cache. With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache? Lets assume we have 400GB of memory- so much of that will be allocated to the data cache, plan cache and working memory.

How does SQL Server decide how much of this 400GB goes to each respective area?

2

With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache?

SQL Server memory( Buffer Pool) is designed to adjust its requirement according to available memory on the system (I mean RAM present on the Windows Server). The exact algorithm is something no one knows but what I can tell you is when SQL Server boots up it start allocating memory keeping in mind three flags, these are resource monitor flags.

  1. MEMPHYSICAL_HIGH
  2. MEMPHYSICAL_LOW
  3. RESOURCE_MEM_STEADY

Actually there is lot of things underneath but I am trying to simplify things for you. SQL Server will keep allocating memory based on its need as long as MEMPHYSICAL_HIGH notification is signaled in window or untill it feels it no longer requires memory and most pages it needs are cached in memory. If there is a memory shortage MEMPHYSICAL_LOW is flagged and SQL Server will try to trim its various caches. When a steady state is reached flag RESOURCE_MEM_STEADY is flagged and this means SQl Server has memory somewhere between high and low flag. I would quote from Karthick PK's blog

The default level of available memory that signals a LowMemoryResourceNotification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. (By default, the threshold is 64mb on most systems).

The default level that signals a high-memory-resource notification event is three times the default low-memory value (By default, the threshold is 64*3=192 MB on most systems).

Key points:

  1. Once the available memory on the system goes below 192 MB HighMemoryResourceNotification (MEMPHYSICAL_HIGH) signal is revoked by windows and SQL Server will not grow its Bpool.

  2. Once the available memory on the system goes below 64 MB LowMemoryResourceNotification (MEMPHYSICAL_low) is signaled by windows and SQL Server will shrink its Bpool (reduce its memory usage).

  3. When the available memory in the system is between 192Mb and 64 Mb (I.e between LowMemoryThreshold and HighMemoryThreshold) SQL Server will not grow or shrink its usage (With some exceptions which we will see in a while)

You of course have option to change the threshold values from the registry.

How does SQL Server decide how much of this 400GB goes to each respective area?

Again its dynamic and depends on requirement. So you have 400 GB memory and suppose you have set max server memory 350 GB. SQL Server will not, at startup, try to grab all 350 GB. It depends on workload. If, after startup you are running heavy workload it will take at max to 350 GB and if it further needs memory it will take help of Lazywriter and Checkpoint process to create some space in buffer pool by flushing dirty pages to disk and pull more pages. The moral here is there is no fixed rule, lot of things depend on workload and SQL Server configuration.

The above also holds true for data cache which takes chunk of buffer pool.

PLAN CACHE:

Plan cache is bit interesting. Let us talk after SQL Server 2005 Sp2 when there was no upper limit on plan cache in SQL Server. The plan cache would normally try to grow when queries start hitting SQL server but it cannot grow indefinitely. There is a formula which keeps track on what maximum plan cache can grow before old/stale plans be removed from it to create space for new one. See below pic taken From Kalen Delaneys's blog.

The Cache pressure limit defines after what limit old entries from plan cache would be removed. To calculate Target memory which would be used in calculating pressure limit see This Q&A Blog from Kalen

enter image description here

I do not have the formula for newer versions. The things changed with arrival of query store in SQL Server 2016 but I am not sure whether this formula changed. Anyways I do not think there is whole point in discussing this formula.

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.