Otdfbt

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.

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.

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