Yesterday I gave a talk around SQL Server 2005's memory manager. The talk was rather chalk talk. It included memory manager changes in SQL 2005 as well as changes post CTP15. For the talk I prepared several slides - basically talking points that I thought might be interesting to everyone.  If you were following my blog you will notice that slides below closely outline my previous posts.  Please ask questions if you have any :-)!

Talking Points for Memory Manager In SQL Server 2005


      Quick Intro

      Noticeable Changes from SQL 2000

      Memory Nodes

      Memory Clerks

      Memory Caches & Pools

      Memory Broker

      NUMA support



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


Note: Caches are Memory Clerks


Cache Store

      Generic cache mechanism with the storage



      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


      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


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