How does SQL Server determine how much memory to allocate to Data cache 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?
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.


MEMPHYSICAL_HIGH
MEMPHYSICAL_LOW
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:
  
  
  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.
  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).
  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&amp;A Blog from Kalen



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.