SQL Server not using all memory

Question

I have SQL Server 2014 with max memory set to 6GB (physical memory is 8GB).

The Target Server Memory is sometimes 6GB and then drops back to Total Server Memory (approx 5.3GB, never reaches 6GB). I used committed_kb in sys.dm_os_sys_info to check the memory used by SQL Server.

When I monitor sys.dm_os_buffer_descriptors, I see that pages are dropped from the cache - but there is still 700MB of memory left. If nothing needed the memory, how would you explain the fact that pages are removed from cache? I would expect that SQL Server only removes pages when it needs memory.

Deallocated temp tables are not a problem on this server. My PLE is 3632. The procedure cache is 2182 MB.

I would expect that pages only would get dropped when there is no memory left, but I have 700MB free or am I misunderstand this?

Can someone please try to explain this behavior?

SQL Server is also reading from disk, so I think I may conclude that not all the pages needed are in memory.

I did some more research and I read a huge amount of pages from disk into memory and noticed something in taskmanager during the reads:

• The memory in use went from 7.0GB -> 7.2GB -> 7.0GB -> 7.2GB ->...
• Sqlservr.exe went from 5.3GB -> 5.5GB -> 5.3GB -> 5.5GB -> ...

It is just like Windows doesn't let sqlservr.exe grow to 6GB.

I ran the query provided by Shanky:

select
(physical_memory_in_use_kb/1024) Physical_Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(Virtual_address_committed_kb/1024 )Total_Memory_in_MB,--RAM+ Pagefile
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

This gave the following result:

Physical_Memory_usedby_Sqlserver_MB: 5247
Locked_pages_used_Sqlserver_MB: 0
Total_Memory_in_MB: 5625
process_physical_memory_low: 0
process_virtual_memory_low: 0

What I don't understand is why Total_Memory_in_MB isn't equal to 6144 (max memory)?

In sys.dm_os_ring_buffers I found RESOURCE_MEMPHYSICAL_LOW, so I think Windows was running low on memory and SQL Server must return some. But there is approx 1GB of memory available => why is Windows telling it is running low on memory?

<Record id="13861" type="RING_BUFFER_RESOURCE_MONITOR" time="20635079241">   
   <ResourceMonitor>
        <Notification>RESOURCE_MEMPHYSICAL_LOW</Notification>
        <IndicatorsProcess>0</IndicatorsProcess>
        <IndicatorsSystem>2</IndicatorsSystem>
        <NodeId>0</NodeId>
        <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>
        <Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">85827186</Effect>
        <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>   
   </ResourceMonitor>   
   <MemoryNode id="0">
        <TargetMemory>6050080</TargetMemory>
        <ReservedMemory>67208656</ReservedMemory>
        <CommittedMemory>5423548</CommittedMemory>
        <SharedMemory>0</SharedMemory>
        <AWEMemory>0</AWEMemory>
        <PagesMemory>4975656</PagesMemory>   
   </MemoryNode>   
   <MemoryRecord>
        <MemoryUtilization>100</MemoryUtilization>
        <TotalPhysicalMemory>8387608</TotalPhysicalMemory>
        <AvailablePhysicalMemory>1048452</AvailablePhysicalMemory>
        <TotalPageFile>11142348</TotalPageFile>
        <AvailablePageFile>2887916</AvailablePageFile>
        <TotalVirtualAddressSpace>137438953344</TotalVirtualAddressSpace>
        <AvailableVirtualAddressSpace>137371168056</AvailableVirtualAddressSpace>
        <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace
   </MemoryRecord> 
</Record>

Update
After some more research why there was always 1GB memory available, I think I found something.
Is it possible that SQL Server only can allocate free memory and that available memory is ignored? When running Process Explorer (Sysinternals) I saw free memory was 0.

Answer 1

To start with I must say you have set max server memory to 6 GB and total memory is 8 GB so you have just left 2 GB for the OS, which in many cases, even if nothing is installed apart from SQL Server on a Windows machine, is too little memory provided to OS. To function properly, on a system with antivirus installed, OS must be given 4 GB at least. I leave 2GB for OS straight away and 1.5 G for AV.

The Target Server Memory is sometimes 6GB and then drops back to Total Server Memory (approx 5.3GB, never reaches 6GB).

Target server memory signifies how much memory is required by SQL Server to function properly in the ideal case. Target server memory is trying to be 6 GB because you have set the max server memory value to 6 GB. It's trying to consume all the memory it is allowed to.

Total server memory is what SQL Server is actually able to consume right now. This is committed memory and backed by physical RAM. This is 5.5 GB max in your case.

SQL Server is trying to grow its memory consumption but after reaching 5.3 or 5.5 GB, the OS is asking SQL Server to not grow its memory consumption further and might be actually flagging low memory notification. This is happening because OS might be facing low memory as already said above. SQLOS responds if Windows OS faces memory pressure by asking its caches its trim down their consumption. You can Query the Ring Buffer to check if there was low memory notification signaled. I must add DMV sys.dm_os_ring_buffer is undocumented but safe.

I see that pages are dropped from the cache - but there is still 700MB of memory left. If nothing needed the memory, how would you explain the fact that pages are removed from cache? I would expect that SQL Server only removes pages when it needs memory.

If you are looking for free memory, I would not suggest you to look at the DMV sys.dm_os_buffer_descriptors. The OS counter Available Mbytes will tell you the amount of physical memory, in bytes, available to processes running on the computer. I suggest you to also see What is a deterministic method for evaluating a sensible buffer pool size? and also read Does SQL Server need more RAM to find out how much RAM SQL Server needs and if SQL Server is facing memory pressure. From what you mentioned, if you are sure pages are getting removed from buffer pool then yes SQL Server feels that pages have to be moved because it needs space to accommodate new pages. I am not sure how you calculated the 700 MB free.

One other thing, please don't look at Task Manager for SQL Server memory consumption. It does not always give you the correct value especially when the SQL Server service account has the lock pages in memory privilege. In your case, even if SQL Server has max server memory of 6 GB, the OS being given just 2 GB, which is forcing SQL Server to not grow its consumption because 2 GB is low for SQL Server. Is there anything apart from SQL Server running on the system?

If you want to calculate SQL Server memory consumption please use:

select
(physical_memory_in_use_kb/1024) Physical_Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 ) Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 ) Total_Memory_in_MB,--RAM+ Pagefile
process_physical_memory_low,
process_virtual_memory_low
from sys.dm_os_process_memory

What I don't understand is why Total_Memory_in_MB isn't equal to 6144 (max memory).

The column Total_Memory_in_MB signifies total memory used by SQL Server (RAM+page file). The RAM is actually physical memory used or committed memory. Some part of SQL Server process is also paged to disk and that constitutes as as virtual memory or page file and so if you are going to see TOTAL memory consumed by SQL Server it would be sum of physical memory and the Page file.

While the column Physical_Memory_usedby_Sqlserver_MB is just the physical memory (memory backed by physical RAM or committed memory) used. This is the reason why both are different. If you see the real column first one is Physical memory used and other one is Virtual memory committed.

If you want to see paged memory that would be the difference between Total_Memory_in_MB and Physical_Memory_usedby_Sqlserver_MB.

NOTE: Total memory used would be greater than physical memory used.

Answer 2

SQL Server uses many more Caches other than the Buffer Cache although that is by far and away the biggest (an obvious example is the plan cache). You can take a closer look at memory through DBCC MEMORYSTATUS and a variety of DMVs. Target memory and total memory refer specifically to the Buffer Pool/ Cache.

Excerpt from Christian Bolton's seminal Professional SQL Server 2008 Internals and Troubleshooting:

• MSSQL$<instance >:Memory Manager\Total Server Memory (KB):
  This indicates the current size of the buffer pool.
• MSSQL$<instance >:Memory Manager\Target Server Memory (KB):
  This indicates the ideal size for the buffer pool. Total and Target should be almost the same on a server with no memory pressure that has been running for a while. If Total is significantly less than Target, then it’s likely that SQL Server cannot grow the buffer pool due to memory pressure, in which case you can investigate further.

Answer 3

Sorry for necromancing, but in order to keep this page up to date, adding my 2 cents:

On Linux the "classic" max memory setting configured via SSMS is not enough. There's an has an extra setting called memory.memorylimitmb

https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver15#memorylimit

This setting controls how much memory is visible to the SQL Server at all. This is what SQL Server thinks is how much memory is installed on the machine.