Jaa

Find Non-Buffer Pool Memory (MemToLeave) in "Private Bytes"

  • Artikkeli

Yesterday I had a discussion on how to determine the non-buffer pool memory usage (formerly called MemToLeave/MTL) for a SQL Server 2005 64-bit instance.

The two counters I pay attention to for this specific scenario are "Process: sqlservr: Private Bytes" and "SQL Server: Buffer Manager: Total pages." 

The "Total pages" counter includes the number of database, free, and stolen pages in the SQL Server buffer pool.

The "Private Bytes" counter interpretation is a little trickier - depending on whether your SQL Server is using AWE on 32-bit or "locked pages in memory" for 64-bit.  The following table details the various interpretations of "Private Bytes" depending on the configuration of your SQL Server instance:

Private bytes interpretation 32-bit, no AWE 32-bit AWE enabled 64-bit "locked pages" NOT enabled 64-bit with "locked pages" enabled
Includes Buffer pool memory

X

 

X

 
Includes Non-buffer pool memory (MemToLeave)

X

X

X

X

 

So in this table you can see that there are two scenarios where buffer pool memory is not included in the value of "Private Bytes."  The first scenario is when AWE is enabled for a 32-bit instance.  The second scenario is when 64-bit SQL is running with "locked pages" enabled.  The latter scenario, although 64-bit, is still using AWE OS APIs under the cover.  (See Slavo's blog entry on this topic.)  Under these two scenarios, "Private Bytes" only represents non-buffer pool memory. Otherwise, "Private Bytes" equals the value of both buffer pool and non-buffer pool memory.

For the case when Buffer Pool and MTL memory are lumped together, you can multiply "Total pages" by 8 to get the total KB, convert the "Private Bytes" to KB from bytes, and then subtract "Total pages" from "Private Bytes" to get the non-buffer pool (MTL) value.

Why care about non-buffer pool memory?  Because SQL Server allocates memory above-and-beyond the buffer pool - including memory for threads, DLLs, EXEs, and common processes like database backups.  If you suspect non-buffer pool memory pressure on the server node hosting your SQL Server instance, this technique will help you appropriately measure SQL Server's true footprint.

Comments