1

I have the max_server_memory setting configured to 141 GB on our production SQL Server and target/total server memory is 144 GB.

I thought max_server_memory represents how much memory I am willing to let the SQL Server engine use, if so why is it using more than that?

Running SQL Server 2012 SP3, physical machine with 160 GB memory installed, no other instances running on the box but we are also running Integration Services.

We have 75 user databases, for a total size of 928 GB. I am not sure if columnstore is being used, or how could I check that. This is a vendor application.

Right now target memory = 144,384,008 and total = 144,378,261.

The instance has been up since last night, 2019-05-01 22:20:21.097 EST. We had some infrastructure maintenance that required the instance to be stopped. However I have noticed for quite some time that total/target is always at 144 while max has always been set to 141. PLE during main business hours Monday-Friday is about an .5 to 1 hour on average, sometimes it jumps to 2 hours.

6

The max server memory setting does not represent how much memory you are willing to let the SQL engine use. From the Memory Management Architecture Guide:

Starting with SQL Server 2012 (11.x), SQL Server might allocate more memory than the value specified in the max server memory setting. This behavior may occur when the Total Server Memory (KB) value has already reached the Target Server Memory (KB) setting (as specified by max server memory). If there is insufficient contiguous free memory to meet the demand of multi-page memory requests (more than 8 KB) because of memory fragmentation, SQL Server can perform over-commitment instead of rejecting the memory request.

As soon as this allocation is performed, the Resource Monitor background task starts to signal all memory consumers to release the allocated memory, and tries to bring the Total Server Memory (KB) value below the Target Server Memory (KB) specification. Therefore, SQL Server memory usage could briefly exceed the max server memory setting. In this situation, the Total Server Memory (KB) performance counter reading will exceed the max server memory and Target Server Memory (KB) settings.

This behavior is typically observed during the following operations:

  • Large Columnstore index queries.

  • Columnstore index (re)builds, which use large volumes of memory to perform Hash and Sort operations.

  • Backup operations that require large memory buffers.

  • Tracing operations that have to store large input parameters.

0

Did you converted the total and target server memory in GB to see what is the actual value in GB. Since you are comparing it with max server memory which is set to 141 GB you must convert the values in GB to see are they really greater than max server memory.

If you convert both total and target server memory to GB it would come

Total Server memory= 144,378,261 KB which comes to ((144378261/1024)/1024)= 137.69 GB
Target Server Memory= 144,384,008 KB which comes to (144,384,008/1024)/1024)=137.68 GB

In both cases total and target server memory is less than max server memory. I agree with what Joe has quoted and to add a bit further

Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

So total SQL Server memory consumption can go beyond the limit set in max server memory.

There are 2 things

  1. Physical memory used by SQL Server
  2. Total memory used by SQL Server (physical RAM+ page file).

To get above you just need to run below

select 
(physical_memory_in_use_kb/1024) total_physical_mem_in_MB
(virtual_address_space_committed_kb/1024) Total_mem_in_mb_RAMAndPagefile
from sys.dm_os_process_memory

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.