Talking Points around Memory Manager in SQL Server 2005
Article
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”