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


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


 

Comments (10)

  1. Ian dunross says:

    Dear Slavo,

    Was this talk internal or on channel9 or webcast.

    Any idea we can see this ?

    Regards,

    Ian

  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 http://www.cs.cmu.edu/~christos/courses/721-resources/p297-o_neil.pdf

    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:

    Salva;

    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.

    Linchi

  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 …

Skip to main content