Search code examples
memorysql-server-2016windows-server-2016

Server memory usage reporting incorrect?

I have a Windows 2016 Server (128GB Ram), running SQL Server 2016, and I'm seeing some unusual figures on the memory usage reporting.

I'm used to seeing SQL Server using a lot of memory (we have this one limited to 96GB) but I'm seeing this being reported strangely in Task Manager. I am seeing 75% memory usage but only 755.5MB against SQL Server. While there are other services running in the background, the total does not come anywhere near 75% of 128GB.

Task Manager

The 75% is the being reflected in Performance manager as I'd expect, with 96% being committed to SQL.

enter image description here

Internally in sql server, I am getting similar figures from internal reporting.

SELECT  (physical_memory_in_use_kb / 1024)          Phy_Memory_usedby_Sqlserver_MB
,       (locked_page_allocations_kb / 1024)         Locked_pages_used_Sqlserver_MB
,       (virtual_address_space_committed_kb / 1024) Total_Memory_UsedBySQLServer_MB
,       process_physical_memory_low
,       process_virtual_memory_low
  FROM  sys.dm_os_process_memory;

enter image description here

I've been asked to investigate why the task manager is showing such a low usage when we would expect it to be so much higher. If there is a general misunderstanding on my part here, then please let me know. If there are any further tests I can perform to help track this down then I'm happy to do so.

Many thanks in advance.

Solution

  • Taskmanager only shows processes that allocate memory via VirtualAlloc. When Lock Pages is set in SQL Server, AllocateUserPhysicalPages is used for allocating and this does not show up in TaskManager. So you cannot rely on TaskManager for SQL memory usage.