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