How It Works: DBCC MemoryStatus Locked Pages Allocated and SinglePageAllocator Values

[mssql-support]

First published on MSDN on May 15, 2009

I have recently had several questions related to the dbcc memorystatus and associated memory DMV display values for locked pages and single page allocations.   After reviewing several outputs and the code I will attempt to summarize my findings.

SQL 2008/2005 have memory related DMV’s to report the memory distribution:

• sys.dm_os_sys_info
• sys.dm_os_sys_memory
• sys.dm_os_process_memory
• >sys.dm_os_nodes
• sys.dm_os_memory_nodes
• sys.dm_os_memory_clerks

Summary

The system I was working on: 500GB RAM, 8 socket, quad core system
Issue: DBCC memorystatus is showing all locked pages and reservations on the same node.

What is a Clerk?

A clerk is an accountant of memory.  SQL Server contains many caches, pools and other memory objects.  Each of these is wrapped by a clerk so the various cache, pool and object allocation counts are tracked.  For complete details refer to http://blogs.msdn.com/slavao .

More Information
This is a reporting issue and not incorrectly assigned pages.    The buffer pool has a single clerk to track BPool activity so all locked pages and reserved values are accounted for using a single clerk.   The NUMA node where the BPool class is initialized will own the clerk and it appears that all the allocations took place on a single node.

The output below appears to show that all the allocations are taking place on node id = 1.  The SinglePage Allocator also shows the same value for all nodes.  There is a single (SPA) single page allocator that works with the buffer pool so this value is reported the same across all nodes as well.

Memory node Id = 1                       KB

---------------------------------------- -----------

VM Reserved                              552418724

VM Committed                             2945656

Locked Pages Allocated                   514134528

MultiPage Allocator                      212288

SinglePage Allocator                     1076168

(5 row(s) affected)

Memory node Id = 2                       KB

---------------------------------------- -----------

VM Reserved                              38016

VM Committed                             37904

Locked Pages Allocated                   0

MultiPage Allocator                      3592

SinglePage Allocator                     1076168

Memory node Id = 64                      KB

---------------------------------------- -----------

VM Reserved                              2560

VM Committed                             2504

Locked Pages Allocated                   0

MultiPage Allocator                      2416

SinglePage Allocator                     1076168      ~1.2GB

MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB

---------------------------------------- -----------

VM Reserved                              537411584

VM Committed                             524288

Locked Pages Allocated                   514134528

SM Reserved                              0

SM Committed                             0

SinglePage Allocator                     0

MultiPage Allocator                      5528

Buffer Pool                              Value

---------------------------------------- -----------

Committed                                62284568     ~490GB

Target                                   62284568

Database                                 62147426     ~485GB

Dirty                                    90372

In IO                                    10

Latched                                  38

Free                                     2591

Stolen                                   134551       ~1.2GB

Reserved                                 3107

Visible                                  62284568

Stolen Potential                         60662694

Limiting Factor                          17

Last OOM Factor                          0

Page Life Expectancy                     4970

There is nothing in the memory status to show the real breakdown of the BPool (hashed) pages and such for indicating the foreign aspect.   This comes from the performance counters.  The following query will show the Target Pages, Total pages and Foreign pages for each node.  (Taken from my single NUMA system.)

select * from sys.dm_os_performance_counters

where object_name like '%Buffer Node%'

1         MSSQL$SQL2008:Buffer Node        Free pages        698

2         MSSQL$SQL2008:Buffer Node        Total pages       9924

3         MSSQL$SQL2008:Buffer Node        Foreign pages     0

4         MSSQL$SQL2008:Buffer Node        Database pages    6027

5         MSSQL$SQL2008:Buffer Node        Stolen pages      3199

6          MSSQL$SQL2008:Buffer Node       Target pages      298327

Memory node Id = 0                       KB

---------------------------------------- -----------

VM Reserved                              4256712

VM Committed                             44480

Locked Pages Allocated                   81920

MultiPage Allocator                      25248

SinglePage Allocator                     25680

MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB

---------------------------------------- -----------