Talking Points around Memory Manager in SQL Server 2005

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



Comments (10)

  1. Ian dunross says:

    Dear Slavo,

    Was this talk internal or on channel9 or webcast.

    Any idea we can see this ?



  2. hkimura says:

    Hi, Slava.

    I’m so interested in the new memory management architecture in SQL Server 2005, and have a question about it.

    I have thought that the new version of SQL Server might have or be scheming to have a new policy for Cache Replacement, like ARC of IBM.

    Is there any change from 2000 about it, or will be?

    I think, SQL Server 2000 uses CLOCK which is basically LRU, so it needs some tuning like DBCC PINGTABLE at a very large database, doesn’t it?

  3. Dis4ea says:

    I love indepth information about SQL Server 😉

    Keep up the good work!

    Like Ian I’d love to see or read this little talk too.

  4. slavao says:

    Thanks for the comments guys!

    A1. The presentation was internal. There is no webcast. If it were I would definetly point you to it.

    A2. We did change LRU policy for database pages, SQL Server 2005 uses LRU-2 fully described here

    I would love to hear more questions/comments!

  5. ishiba says:

    Hi, Slava.

    I found this blog with Keyword ‘NUMA’. It is a good lesson for me.I’d like to know the difference of NUMA between SQL 2000 and SQL 2005. I learned extended NUMA & Soft NUMA support in this article, how does SQL 2000 support for NUMA?

  6. Linchi Shea says:


    On this 16-processor x64 machine running SQL2005 x64 on Win2K3 x64 Datacenter, I’ve run into a behavior that you may be able to explain. My database size (~8.5GB) is smaller than the amount of physical memory given to the SQL2005 instance (~10GB), and my owrkloads are read-only (essentially the Order-Status and Stock-Level transactions of tpc-c).

    When I set the numproc boot option to any value other than 4, I can easily get the database cached in memory after an initial rampup, and there is no disk I/O observed thereafter. However, when I set numproc to 4, I keep seeing disk reads, and SQLServer:Buffer ManagerTotal pages doesn’t stop at ~1047647 (8GB) and continues to climb to the Target Pages of ~ 3456000 (27GB).

    Note that when numproc is set to 4, only one node is used as per SQLServer:Buffer Node. And the physical memory on that node (which is one of the four servers that make up the 16-processor machine) is 8GB, slightly smaller than the database size. I suspect that SQL2005 for some reason decides to not use the physical memory on the other nodes, and has chosen, instead, to continue to fetch pages from disk, even though the OS sees 32GB and the SQL instance is given 27GB.

    To test that theory, I scale the database down to about 4GB, and repeat the test with the same workloads. Sure enough, no more disk I/Os are observed during the steady state.

    I’d appreciate it very much if you could help explain this behavior.


  7. Linchi Shea says:

    Oops! I missplled your name in my previous comment. Sorry!

  8. La stratégie :

    SQL Server 2005 Update from Paul Flessner (6/04/2006)

    Les versions et combien ca coute …