Condividi tramite

Memory Manager surface area changes in SQL Server 2012

  • Articolo

There were various changes to memory related DMVs, DBCC memory status, and Perfmon counters in SQL Server 2012 as part of the redesign of the Memory Manager component of SQLOS. The Memory Manager redesign resulted in being able to more accurately size and govern all SQL Server memory consumption, making page allocations more efficient, and better handling and tracking of NUMA node memory allocations. This post aims to document these surface area changes in one place to serve as a quick reference.

Some of these changes were to standardize memory counters as "kb" rather than bytes or pages, continuing a process begun earlier, but now more relevant since the single and multi-page allocators were replaced by an any-size page allocator, so counting pages allocated would no longer give a meaningful view of memory use. This consolidation of allocators also resulted consolidated counters for page allocation.

Other changes reflect the central role of the redesigned Memory Manager in being the interface to the OS, providing page allocations for the other components. A modular summary of the architectural changes is shown below:

 

 

DMV Changes

sys.dm_os_memory_nodes

The consolidation of the single and multi-page allocators is reflected in the pages_kb column in sys.dm_os_memory_nodes. The new foreign_committed_kb counter is a result of the improved NUMA management and reporting in SQL 2012 and identifies memory on a noe that belongs to a remote node, which can be useful if you're troubleshooting slower memory access. We will always try and use local memory first, at least until the target memory is reached.

sys.dm_os_memory_nodes

SQL Server 2012

SQL Server 2008 R2

pages_kb

single_pages_kb +  multi_pages_kb

foreign_committed_kb

-

 

sys.dm_os_sys_info

The main memory manager changes here are to standardize memory counters.

sys.dm_os_sys_info

SQL Server 2012

SQL Server 2008 R2

physical_memory_kb

physical_memory_in_bytes

virtual_memory_kb

virtual_memory_in_bytes

committed_kb

bpool_committed

committed_target_kb

bpool_commit_target

visible_target_kb

bpool_visible

 

sys.dm_os_memory_cache_counters

 

sys.dm_os_memory_cache_counters

SQL Server 2012

SQL Server 2008 R2

pages_kb

single_pages_kb +  multi_pages_kb

pages_in_use_kb

single_pages_in_use_kb  + multi_pages_in_use_kb

sys.dm_os_memory_cache_entries

sys.dm_os_memory_cache_entries

SQL Server 2012

SQL Server 2008 R2

pages_kb

pages_allocated_count

sys.dm_os_memory_clerks

sys.dm_os_memory_clerks

SQL Server 2012

SQL Server 2008 R2

pages_kb

single_pages_kb +  multi_pages_kb

page_size_in_bytes

page_size_bytes

sys.dm_os_memory_objects

 

sys.dm_os_memory_objects

SQL Server 2012

SQL Server 2008 R2

pages_in_bytes

pages_allocated_count

max_pages_in_bytes

max_pages_allocated_count

 

DBCC Memory Status changes

 Many of the DBCC memory counters that were previously under Buffer Pool have now been consolidated under Memory Manager to reflect the revised architecture. There is also some additional information to reflect OS errors and NUMA awareness. NUMA growth phase shows memory growth before the target is reached. The “Pages In Use” counter shows the actual number of pages in use, including the stolen pages, database pages, and the reserved memory. The Away Committed counter indicates how much memory from this node is committed from other nodes.

SQL Server 2012

SQL Server 2008 R2

Memory Manager \  Large Pages Allocated

-

Memory Manager \  Emergency Memory

Memory Manager \  Reserved Memory

Memory Manager \ Emergency  Memory In Use

Memory Manager \  Reserved Memory In Use

Memory Manager \  Target Committed (KB)

Buffer Pool \  Target (Pages)

Memory Manager \  Current Committed (KB)

Buffer Pool \  Committed (Pages)

Memory Manager \  Pages Allocated (KB)

Buffer Pool \  Stolen (Pages) + Buffer Pool \ Database (Pages)

Memory Manager \  Pages Reserved (KB)

Buffer Pool \  Reserved (Pages)

Memory Manager \  Pages Free (KB)

Buffer Pool \ Free  (Pages)

Memory Manager \  Pages In Use

-

Memory Manager \  Page Alloc Potential (KB)

Buffer Pool \  Stolen Potential (Pages)

Memory Manager \  NUMA Growth Phase

-

Memory Manager \  Last OOM Factor

Buffer Pool \ Last  OOM Factor

Memory Manager \  Last OS Error

-

SQL Server 2012

SQL Server 2008 R2

Memory Manager \  Large Pages Allocated

-

Memory Manager \  Emergency Memory

Memory Manager \  Reserved Memory

Memory Manager \  Emergency Memory In Use

Memory Manager \  Reserved Memory In Use

Memory Manager \  Target Committed (KB)

Buffer Pool \  Target (Pages)

Memory Manager \ Current  Committed (KB)

Buffer Pool \  Committed (Pages)

Memory Manager \  Pages Allocated (KB)

Buffer Pool \  Stolen (Pages) + Buffer Pool \ Database (Pages)

Memory Manager \  Pages Reserved (KB)

Buffer Pool \  Reserved (Pages)

Memory Manager \  Pages Free (KB)

Buffer Pool \ Free  (Pages)

Memory Manager \  Pages In Use

-

Memory Manager \  Page Alloc Potential (KB)

Buffer Pool \  Stolen Potential (Pages)

Memory Manager \  NUMA Growth Phase

-

Memory Manager \  Last OOM Factor

Buffer Pool \ Last  OOM Factor

Memory Manager \  Last OS Error

-

 

SQL Server 2012

SQL Server 2008 R2

Memory node Id = n  \ Pages Allocated

Memory node Id = n  \ MultiPage Allocator + Memory node Id = n \ SinglePage Allocator

Memory node Id = n  \ Target Committed

-

Memory node Id = n  \ Current Committed

-

Memory node Id = n  \ Foreign Committed

-

Memory node Id = n  \ Away Committed

-

Memory Clerks \  Pages Allocated

Memory Clerks \  SinglePage Allocator + Memory Clerks \ MultiPage Allocator

 

Perfmon counters

 The Perfmon counter changes follow the same consolidation of counters under the Memory Manager object, and the standardization of counters to KB. Several deprecated AWE counters are now removed.

SQL Server 2012

SQL Server 2008 R2

Object - SQLServer:Memory  Manager

Object - SQLServer:Buffer  Manager

Database Cache Memory (KB)

Database pages

Free Memory (KB)

Free pages

Reserved Server Memory (KB)

Reserved pages

Stolen Server Memory (KB)

Stolen pages

Target  Server Memory  (KB)

Target  pages

Total  Server Memory (KB)

Total  pages

-

AWE  lookup maps / sec

-

AWE stolen maps / sec

-

AWE  write maps /  sec

-

AWE  unmap  calls / sec

-

AWE  unmap  pages / sec

 

SQL Server 2012

SQL Server 2008 R2

Object -  SQLServer:Memory Node

Object -  SQLServer:Buffer Node

Database Node  Memory (KB)

Database pages

Free Node Memory  (KB)

Free pages

Foreign Node Memory  (KB)

Foreign pages

Stolen Node Memory  (KB)

Stolen pages

Target Node Memory  (KB)

Target pages

Total Node Memory  (KB)

Total pages

 

I'll follow up this article with some additional surface area changes for configuration and new memory manager messages that appear in the errorlog. 

The goal of these changes is to make the new Memory Manager activity easier and more intuitive to track, and to clean up some of the values that no longer make sense with the any size page allocator and deprecation of AWE. Anything you used to use but can't find any more? Let me know.

Comments

  • Anonymous
    October 30, 2012
    good!

  • Anonymous
    November 24, 2012
    very useful information. Thx

  • Anonymous
    September 25, 2013
    Useful information