Sql-server – How does SQL Server determine how much memory to allocate to Data cache

buffer-poolcachememoryplan-cachesql server

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?

Best Answer

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&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.

Related Solutions

Sql-server – How much memory is a table taking up

with bd as (
    select count(*) as pages_in_memory, bd.allocation_unit_id
    from sys.dm_os_buffer_descriptors bd
    where bd.database_id = db_id()
    group by bd.allocation_unit_id)
select p.object_id,
    p.index_id,
    p.partition_number,
    bd.pages_in_memory,
    au.total_pages as pages_on_disk,
    au.type_desc
from bd 
join sys.allocation_units au 
    on au.allocation_unit_id = bd.allocation_unit_id
join sys.partitions p
    on p.partition_id = au.container_id

Sql-server – Quick look at how much RAM is allocated to SQL Server

You could NEVER, EVER trust Task Manager to tell you how much memory SQL Server is using (maybe you are remembering a 32-bit system with a very small amount of memory). Stop using Task Manager for this, period. Use the performance counter - you can also query the performance counter using DMVs:

SELECT object_name, cntr_value 
  FROM sys.dm_os_performance_counters
  WHERE counter_name = 'Total Server Memory (KB)';

You could save that as a query shortcut in Tools > Options > Environment > Keyboard > Query Shortcuts, and get accurate results in a query window much faster than getting inaccurate results from Task Manager.

You can also check for memory pressure (and whether you can do anything about it) using these queries:

SELECT object_name, cntr_value
  FROM sys.dm_os_performance_counters
  WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');

-- SQL Server 2012:
SELECT physical_memory_kb FROM sys.dm_os_sys_info;

-- Prior versions:
SELECT physical_memory_in_bytes FROM sys.dm_os_sys_info;

EXEC sp_configure 'max server memory';

Best Answer

Related Solutions

Sql-server – How much memory is a table taking up

Sql-server – Quick look at how much RAM is allocated to SQL Server

Related Question