patternsqlMinor
Why is SQL Server memory showing in task manager
Viewed 0 times
whyshowingsqlmanagermemoryservertask
Problem
I have worked with SQL Server for a while now, and I understand that SQL memory is inaccurately reported by the task manager.
However I have recently been doing some testing on SQL Server that someone else has setup.
I am expecting to see
Is there a setting in SQL Server that is causing the task manager to report this inaccurately? Or is there something going wrong in the background of my SQL Server that is causing it to balloon?
However I have recently been doing some testing on SQL Server that someone else has setup.
I am expecting to see
sqlservr.exe running at about 40mb memory. However, Task manager is displaying that over 4Gb of memory is being used.Is there a setting in SQL Server that is causing the task manager to report this inaccurately? Or is there something going wrong in the background of my SQL Server that is causing it to balloon?
Solution
The number reported by Task Manager is not inaccurate exactly; you just have to understand what the number is telling you.
If you expand the Task Manager column so you can see the whole title, you'll see it says: Memory (Private Working Set). The working set does not include any memory that is being accessed via Address Windowing Extensions (AWE).
AWE is a technology API that allows a 32-bit process to access more memory than its 4GB virtual address space, by mapping and remapping a small 'window' of virtual address space onto a larger physical memory space.
AWE is how 32-bit SQL Server is able to access more than 4GB of memory, and the same mechanism is used in 64-bit SQL Server to implement the 'locked pages' feature.
So, Task Manager will show only a relatively small amount of memory in use for:
In your case, with Task Manager reporting over 4GB in use, it seems likely the SQL Server you were looking at was a 64-bit installation that is not using locked pages. Instead of using Task Manager, use the Performance Monitor SQL Server instance counter Total Server Memory, or query the sys.dm_os_process_memory DMV in SQL Server 2008 and later.
Reference and further reading: Fun with Locked Pages, AWE, Task Manager, and the Working Set by Microsoft Customer Service and Support (CSS) SQL Support.
If you expand the Task Manager column so you can see the whole title, you'll see it says: Memory (Private Working Set). The working set does not include any memory that is being accessed via Address Windowing Extensions (AWE).
AWE is a technology API that allows a 32-bit process to access more memory than its 4GB virtual address space, by mapping and remapping a small 'window' of virtual address space onto a larger physical memory space.
AWE is how 32-bit SQL Server is able to access more than 4GB of memory, and the same mechanism is used in 64-bit SQL Server to implement the 'locked pages' feature.
So, Task Manager will show only a relatively small amount of memory in use for:
- 32-bit SQL Server using AWE to access memory; or
- 64-bit SQL Server using locked pages
In your case, with Task Manager reporting over 4GB in use, it seems likely the SQL Server you were looking at was a 64-bit installation that is not using locked pages. Instead of using Task Manager, use the Performance Monitor SQL Server instance counter Total Server Memory, or query the sys.dm_os_process_memory DMV in SQL Server 2008 and later.
Reference and further reading: Fun with Locked Pages, AWE, Task Manager, and the Working Set by Microsoft Customer Service and Support (CSS) SQL Support.
Context
StackExchange Database Administrators Q#35418, answer score: 8
Revisions (0)
No revisions yet.