23 May,2013 by Jack Vamvas
Task Manager is not showing the correct SQL Server memory usage. I have a SQL Server 2008 running on a Windows 2008 64 bit server.The task manager shows 100k , but I know I’n using 24 GB. What server configurations cause this problem?
Task Manager should not be used to report on SQL Server memory usage . Use the physical_memory_in_use_kb on the sys.dm_os_process_memory. Here’s the strange thing – this query will indicate whether locked pages is enabled. If locked pages is enabled ,Task Manager may not show all the memory used by SQL Server.
SELECT physical_memory_in_use_kb,locked_page_allocations_kb, memory_utilization_percentage, FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);
To check if Lock Pages is enabled read Cannot use Large Page Extensions: lock memory privilege was not granted.
Note: If the SQL Service Account is using a Local Administrator account, the Lock Pages in Memory is inherited as part of the elevated permissions in the OS. By default, Locked Pages in memory is off, but using a Local Administrator Account as a SQL Service Account is not a good idea and open to security breaches.