Compartilhar via

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

  • Artigo

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 https://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

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

Comments