Talking Points for Memory Manager In SQL Server 2005

Agenda

•      Quick Intro

•      Noticeable Changes from SQL 2000

•      Memory Nodes

•      Memory Clerks

•      Memory Caches & Pools

•      Memory Broker

•      NUMA support

•      Q&A

Quick Intro

•      VAS – Virtual Address Space

•      Physical Memory & Swap File

•      AWE mechanism & Locked Pages in memory on 64 bit

•      Memory Pressure

–   Internal (Virtual & Physical)

–   External (Virtual & Physical)

•      SMP – Symmetric Multi Processing

•      NUMA – Non Uniform Memory Access

Noticeable Changes from SQL 2000

•      Responds to VAS pressure

–    Thread pool truncation

–    BP’s region truncation when AWE mechanism is enabled

•      Supports dynamic memory management when using AWE mechanism

•      Supports locked pages on 64 bit platform

•      Implements Resource Monitor & Common caching framework

•      Contains extended NUMA & Soft NUMA support

•      Implements Memory Broker

•      Lots of new memory DMVs

Memory Nodes

•      A Memory Node is a software abstraction representing the memory attached to either to all CPUs in SMP configuration or to a single NUMA node

•      Supports several different memory allocators

–   Single Page Allocator - Buffer Pool Memory

–   Multiple Page Allocator - Memory outside of Buffer Pool

–   Reserved Page Allocator – DAC memory

•      Currently is not exposed through either DMVS or

dbcc memorystatus

Memory Clerks

•      Reflects memory usage for specific component

•      Receives notifications about memory state changes and responds to pressure

–  CLR’s GC is hooked into this mechanism

•      Utilize Memory Node allocators for memory allocation

•      Sys.dm_os_memory_clerks & dbcc memorystatus

Memory Object

•      Memory Objects are heaps

•      Used by components to allocate/free memory dynamically

•      Plenty of memory objects in the system

•      Sys.dm_os_memory_objects dumps all memory objects

•      Sys.dm_os_memory_allocations dumps all allocations out of memory objects. (Supported with trace flag 3654 only)

Resource Monitor

•      Mechanism to respond to memory pressure

–  Implemented as a regular task

–  Occupies its own hidden scheduler

–  Invokes GC in CLR

•      Select * from Sys.dm_os_ring_buffers where ring_buffer_type = “RING_BUFFER_RESOURCE_MONITOR”

Memory Caches

•      Buffer Pool

–    Data page cache (Not covered here)

•      Cache Store

–    Generic cache framework

–    Examples: Procedure Cache & System Rowset Cache

•      User Store

–    Generic cache framework

–    Examples:  Schema Manager, Security & Metadata Caches

•      Sys.dm_os_memory_cache_counters

Note: Caches are Memory Clerks

Cache Store

•      Generic cache mechanism with the storage

–   Sys.dm_os_memory_cache_hash_tables

–   Sys.dm_os_memory_cache_entries

•      Implements size control by utilizing LRU (clock) algorithm

–   External Clock hand controls memory consumed by all caches

–   Internal Clock hand controls memory consumed by a given cache

–   Sys.dm_os_memory_cache_clock_hands

•      User Store is exactly the same as Cache Store but doesn’t have storage

Memory Pools

•      Object Store

–  Generic pool framework

–  Responds to memory pressure

–  Example: SNI’ Network Packet Cache

•      Object Store

–  Sys.dm_os_memory_pools

Note: Pools are Memory Clerks

Memory Broker

•      Mechanism to dynamically broker memory across large memory components such as Query Execution, Query Optimization & Caches

•      dbcc memory status & Sys.dm_os_memory_ring_buffers where ring_buffer_type = “RING_BUFFER_MEMORY_BROKER”

NUMA support

•      Automatic configuration according to hardware layout

•      Application can be bound to a specific node

•      Memory Nodes

–   Collection of Scheduler (CPU) Nodes

–   Memory  locality

•      Scheduler Nodes

–   Collection of Schedulers

–   Scheduling locality

–   I/O port for network requests – preemptive I/O

–   Resource & Scheduler Monitors

NUMA support cont

•      Soft NUMA

–   Soft Node configuration for large SMPs

–   Soft Node configuration for NUMA

•    Soft Nodes can’t span multiple hardware nodes

•      Buffer Pool is NUMA aware

–   Lazy Writer per NUMA node

–   Partitioned free lists

–   Perfmon, SQLServer.BufferNode

•    Database pages

•    Foreign pages

•    Stolen pages

•    Target pages

•    TotalPages