Stop using Task Manager to check SQL’s memory usage!

There’s two fairly common questions I see on the forums around SQL Server’s memory usage. Either the question asks why SQL’s using too much memory, or why it’s using too little.

Too much memory isn’t usually a real problem, it’s often due to max server memory being left at its default of 2048TB, along with a lack of understanding of how SQL uses memory.

Too little memory used is also often not a real problem, rather it’s usually from using the wrong tools to check SQL Server’s memory usage.

Let’s start by looking at an example.

This particular server has 16 GB of memory, and SQL Server’s max server memory is set to 10GB. Since the last restart of the instance, I’ve run SELECT * FROM .. against every table in a 30GB database. That should have warmed the cache up nicely.

MemoryTaskManager

Err, what? SQL Server’s not even using 100MB? I’ve just read 30GB of data and SQL Server’s not even using 1% of it’s allowed memory!!!

Or is it?

TotalServerMemory

A look at perfmon shows a completely different picture. Total and target server memory are both at 10GB. So why is Task Manager showing such a low figure?

LockedPagesInErrorLog

The service account that SQL’s running under has been granted the Lock Pages in Memory permission. This means that SQL’s not using the normal Windows memory routines to allocate memory.

Normally, SQL Server and other Windows applications allocate memory using the VirtualAlloc API call. This allocates virtual memory which is pageable. When SQL Server has been given the Lock Pages in Memory permission, it doesn’t use VirtualAlloc to allocate memory. Instead it uses the API call AllocateUserPhysicalPages. Memory allocated with this API call is not pageable, it has to remain in physical memory.

Task Manager’s memory counters (the Working Set ones) only show memory that’s been allocated using VirtualAlloc. Hence, when SQL Server has the Lock Pages in Memory permission and is allocating most of its memory using AllocateUserPhysicalPages, its memory usage in Task Manager will appear to be extraordinarily low. As far as I recall, in that case it’s only the non-buffer memory which is still allocated with VirtualAlloc, and that’s just things like the thread stacks, the CLR memory, backup buffers, and similar. It’s not the buffer pool. The buffer pool gets allocated with AllocateUserPhysicalPages.

If I remove the Lock Pages in Memory and re-run the test, Task Manager shows completely different values

MemoryTaskManager_NoLockedPages

Now the buffer pool is being allocated with VirtualAlloc and so Task Manager shows the full 10GB of memory usage.

In summary, Task Manager can show a completely incorrect value for SQL Server’s memory usage if the SQL service account has the Lock Pages in Memory permission. This can lead to a lot of wasted time if it is concluded that Task Manager is correct and SQL Server is using little memory.

Rather leave Task Manager alone and use perfmon and the DMVs to check what SQL Server’s memory allocation actually is. They’ll both be correct whether Lock Pages are being used or not.

6 Comments

  1. Pingback: (SFTW) SQL Server Links 22/01/16 - John Sansom

  2. Andy Hogg

    Very well explained post with clear examples to demonstrate, thank you.

    Reply
  3. Chuck Rummel

    This inspires me in a different way. My company’s operators often try alerting people that “SQL is taking too much memory, please fix” (even though it’s a dedicated box, so it *should* be using all that memory. But they use task manager to gather evidence. So I’ll be asking my dba colleagues on other teams to make sure they have LPIM set (and SQL’s max server memory to an appropriate, non-default value, to allow the OS some breathing room) in the hopes the operators can focus their time elsewhere.

    Reply
    1. Gail (Post author)

      Wouldn’t it be easier to educate the operators that ‘taking all the memory’ is normal behaviour?

      Reply
  4. ZB

    Thanks a lot for the explanation! It helped me to understand why using Windows Task Manager to check SQL Server memory usage is very wrong.

    Is that only SQL Server use “AllocateUserPhysicalPages”, or have other windows application also work in this way?

    Reply
    1. Gail (Post author)

      Any Windows application that has the necessary permissions and is coded to use physical memory allocations rather than virtualalloc. I don’t offhand know of any, other than SQL Server, that do.

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *