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

• 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