Why is SQL Server memory showing in task manager

Question

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

Answer 1

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:

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

Answer 2

Quoting from the Microsoft Knowledge Base article, "How to adjust memory usage by using configuration options in SQL Server":

When you start Microsoft SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and the Performance Monitor may show that the physical memory that is available on the computer steadily decreases until the available memory is between 4 MB and 10 MB.

This behavior alone does not indicate a memory leak. This behavior is typical and is an intended behavior of the SQL Server buffer pool.

By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache), depending on the physical memory load that the operating system reports. As long as sufficient memory (between 4 MB and 10 MB) is available to prevent paging, the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and obtain several megabytes of memory each second. This allows for SQL Server to quickly adjust to memory allocation changes.