Share via

Talking Points around Memory Manager in SQL Server 2005

  • 08/30/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

  • Anonymous
    August 30, 2005
    Dear Slavo,
    Was this talk internal or on channel9 or webcast.
    Any idea we can see this ?
    Regards,
    Ian

  • Anonymous
    August 30, 2005
    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?

  • Anonymous
    August 30, 2005
    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.

  • Anonymous
    August 31, 2005
    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!

  • Anonymous
    November 13, 2005
    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?

  • Anonymous
    January 10, 2006
    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

  • Anonymous
    January 10, 2006
    Oops! I missplled your name in my previous comment. Sorry!

  • Anonymous
    July 12, 2006
    La stratégie :
    SQL Server 2005 Update from Paul Flessner (6/04/2006)
    Les versions et combien ca coute ...

  • Anonymous
    June 01, 2009
    PingBack from http://uniformstores.info/story.php?id=14206

  • Anonymous
    June 17, 2009
    PingBack from http://pooltoysite.info/story.php?id=6874