Sql-server – Virtualized SQL Server Memory Oddity

memorysql serversql-server-2008-r2vmwarewindows-server

I've not seen this before, though perhaps it is common. I am looking at a virtualized (VMWare 5.5) SQL Server (2008R2 on Windows 2008 R2). What I am seeing is that in Task Manager*, sqlserver.exe is using about 163 MB of RAM, if I use procexp, the same service shows just under 500 MB of RAM used.

The VM has 32GB of RAM and Task Manager shows 31.7 GB of that RAM being used. The VMWare Perfmon counter does not seem to be indicating any ballooning (perhaps I read something the wrong way).

Thoughts/guidance appreciated. I am trying to right size a new SQL VM. At this point I have not yet obtained access to vSphere or the vCenter database.

* Using Task Manager because I was looking at overall memory utilization, not just SQL Server. I was warned by a performance touchy admin to keep my treading very very light.

Best Answer

What I am seeing is that in Task Manager, sqlserver.exe is using about 163 Mb of RAM, if I use procexp, the same service shows just under 500 MB of RAM used.

You should never look at task manager to check SQL Server memory utilization. It would never show you correct result. You should use a DMV sys.dm_os_process_memory, if you are using SQL Server 2008 and above, to check SQL Server memory utilization.

select 
(physical_memory_in_use_kb/1024) as PhyMemory_usedby_Sqlserver_MB, 
(locked_page_allocations_kb/1024 ) as Locked_pages_used_Sqlserver_MB, 
(virtual_address_space_committed_kb/1024 ) as Total_MemoryUsed_in_MB, 
process_physical_memory_low, 
process_virtual_memory_low 
from sys. dm_os_process_memory

Note:

PhyMemory_usedby_Sqlserver_MB is the committed memory and is backed by physical RAM.

Total_MemoryUsed_in_MB is the total memory used by SQL Server (RAM and page file)

You are making a mistake, which probably many 'newbies' make when referring to memory used by SQL Server. It would never show you correct memory utilization in task manager if Locked pages in memory privilege(LPIM) is there for the account running SQL Server service. This is because the task manager only shows Process Private Bytes, the memory allocated via Virtual_alloc function which is pageable. Now if the SQL Server service account has LPIM, most part of the memory allocation would be done by AWE API. The memory allocated via AWE API is NOT pageable and thus does not shows up in task manager, which, in the end shows you different (incorrect) values.

Further reading:

Fun with Locked Pages, AWE, Task Manager, and the Working Set… (CSS SQL Server Engineers)

Related Question