Memory utilization by SQL Server 2016

Question

I am running a SQL Server 2016 Enterprise Edition on Windows Server, with 128 GB of memory. During installation, I assigned 124 GB to the SQL server, since the server is dedicated to SQL only, leaving the rest to the OS.

Now, the Memory consumption per task manager shoots up to 97% when DML/DDL queries run or any maintenance plans runs. But. the PLE remains in a good state (i.e. > 300 seconds) and no performance issue observed. Once the jobs and queries complete, the SQL isn't releasing memory for quite a while and I have to do a memory capping or wait for the whole day for the memory to get released.

Is there any way to let the SQL Server to release memory, other than memory capping and why SQL is not able to release the memory as soon as all the job gets completed.

Answer 1

This is by design. SQL Server does not arbitrarily give memory back to Windows, unless Windows requests it.

While SQL Server is running, it begins caching data in memory as transactions are executed. This could include query plans and data pages. This is to avoid subsequent recompilations of the same plan or additional reads from disk for the same data pages later. After all, reading from disk is much slower than reading from memory. Once SQL Server has the memory it wants (or at least as much as it can get), it's up to Windows to request SQL Server to release memory.

As you said, nothing else is running on the server and performance is not an issue. So, if you're ok with SQL Server using 124GB, begin to expect that it will always be using that much memory.

However, I would suggest you check out this article from Jonathan Kehayias, and use that as a baseline for how much memory you should give SQL Server.

Answer 2

After looking at your RAM assignment for SQL Server and Operating System I would recommend setting the max server memory (MB) setting of your SQL Server instance to a lower value.

Why? Well bare with me...

There is an article from Jonathan Kehayias called:

How much memory does my SQL Server actually need?

In his article he explains how he calculates the amount of memory to reserve for the OS and SQL Server:

...and in my book the recommendation that I make is to reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

I created an Excel Chart based on that formula that looks like this:

Reading out the values for 128 GB on your system I would set the max server memory (MB) setting to 109 GB ( 111616 MB) . This leaves you with adequate memory for the operating system and any other task that SQL Server starts outside of the max server memory (GB) setting.

Microsoft goes even further and recommends you to ...

• From the total OS memory, subtract the equivalent of potential SQL Server thread memory allocations outside the max server memory control, which is comprised of stack size 1 * calculated max worker threads 2.
• Then subtract 25% for other memory allocations outside the max server memory control, such as backup buffers, extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers. This is a generic approximation, mileage may vary.
• What remains should be the max_server_memory setting for a single instance setup.

^{Reference: Server memory configuration options (Microsoft | SQL Docs)}

Answering Your Observations / Questions

Now, the Memory consumption per task manager shoots up to 97% when DML/DDL queries run or any maintenance plans runs. But. the PLE remains in a good state (i.e. > 300 seconds) and no performance issue observed. Once the jobs and queries complete, the SQL isn't releasing memory for quite a while and I have to do a memory capping or wait for the whole day for the memory to get released.

PLE is the amount of time that a (data) page stays in memory so as not to be re-retrieved from disk, which is IO which will cause delays. The higher the PLE the better. 300 was once a good recommendation. Higher can be better.

When your memory manager in Task manager hits 97% this can be because your tasks are running outside the max server memory (MB) setting as outlined in the document I referenced. It could also be because more data is being loaded into memory, because of rebuilding indexes (reading data), reorganizing indexes (reading data) or performing backups (reading data / disk IO / Task outside of max server memory (MB), backup buffer mentioned in the quoted article).

This behaviour is to be expected. Memory consumption will rise due to these tasks.

If your max server memory (MB) is too high, then the OS of your server might be competing with the SQL Server instance for memory. You might be able to observe this when you query the SQL Server Memory DMVs.

SELECT system_high_memory_signal_state, 
       system_low_memory_signal_state 
FROM sys.dm_os_sys_memory;

Depending on the results you will have an idea how your system is performing in relation to available memory:

system_high_memory_signal_state = 1        Available physical memory is high
and
system_low_memory_signal_state = 0
system_high_memory_signal_state = 0        Available physical memory is low
and
system_low_memory_signal_state = 1
system_high_memory_signal_state = 0        Physical memory usage is steady
and
system_low_memory_signal_state = 0
system_high_memory_signal_state = 1        Physical memory state is transitioning
and
system_low_memory_signal_state = 0

^{Reference: sys.dm_os_sys_memory (Transact-SQL) ()}

Is there any way to let the SQL Server to release memory, other than memory capping and why SQL is not able to release the memory as soon as all the job gets completed.

SQL Server will not release memory once it is in use, because (as also pointed out by others): Retrieving data from disks is expensive. Time-wise and IO-wise.

If the memory is being released at some later time, then this could be because data (pages) are being released from memory due to some ageing out of pages (data, plan-cache, other)

You could look at sys.dm_os_process_memory to see if the SQL Server is encountering some form of memory pressure:

Most memory allocations that are attributed to the SQL Server process space are controlled through interfaces that allow for tracking and accounting of those allocations. However, memory allocations might be performed in the SQL Server address space that bypasses internal memory management routines. Values are obtained through calls to the base operating system. They are not manipulated by methods internal to SQL Server, except when it adjusts for locked or large page allocations.

^{Reference: sys.dm_os_process_memory (Transact-SQL) (Microsoft | SQL Docs)}

I like to use this query:

SELECT dopm.physical_memory_in_use_kb, 
       dopm.process_physical_memory_low, 
       dopm.process_virtual_memory_low,
       dopm.memory_utilization_percentage
FROM sys.dm_os_process_memory dopm;

Possible Solution

• Query the DMVs and decide if your system has some form of memory pressure.
• Reduce the amount of memory assigned to your SQL Server instance.
• Observe the PLE.
• Query the DMVs and decide again if your system has some form of memory pressure.
• Add physical RAM to the SQL Server if required.

It is generally better to have lots of RAM, but there is a maximum after which the memory consumption of an SQL Server will not rise and PLE will stay stable.