SQL Server Memory Architecture Sumit Sarabhai Microsoft Corp

SQL Server Memory Architecture Sumit Sarabhai Microsoft Corp.

C: />whoami 9+ years in MSFT SQL v. Next Complex Problems Product Group Engagement Speaker in UG Meets, SQL Talks and SSGAS conference Expert in SQL Engine Currently learning Azure viz. HDInsight, SQL Azure, No. SQL and BI Customer Obsession

Agenda • • • Virtual Memory Virtual Address Space SQL Server OS Architecture Changes in SQL 2012 Architecture Some Troubleshooting – if time permits!

Memory Allocations Virtual Memory Virtual memory is best used for managing large arrays or collections of objects or structures of varying sizes. It is the primary mechanism by which SQL Server allocates memory. Heap A heap is a memory region consisting of one or more pages of reserved space that can be suballocated into smaller pieces by the heap manager. Heaps are most useful for allocating large numbers of similarly sized, relatively small objects and structures

Difference between 32 bit and 64 bit

32 (x 86) vs 16 EB PAE Address Windowing Extensions 64 Gb AWE 4 Gb 64 (x 64) Kernel 1 -2 Gb Kernel 8 TB …and (alot) of room to grow user (…someday) Kernel 2 Gb Wo. W 2 TB Physical Memory Limit User 4 Gb User 2 -3 Gb User 2 Gb null increase userva (/3 g. B) BCDEdit. exe User 2 Gb-8 TB null

Trivia What is committed memory? What is Working set? What is private bytes? What is Total Server Memory and Target Server Memory?

SQL Server 32 bit Bpool and MTL = (Stack size * max worker threads) + Additional space //(By default 256 MB and can be controlled by -g) Stack size =512 KB per thread for 32 Bit SQL Server MTL= (256 *512 KB) + 256 MB =384 MB BPool = Min (Physical memory, (User address space – MTL)) – BUF structures

Start Your 32 bit BPool Engines 16 Gb RAM BPool does not commit its target memory at startup If AWE enabled, 16 Gb RAM with /3 Gb Wo. W gives you all 4 Gb Bpool can allocate here Kernel 2 Gb 4 Gb Address Space Limit Capped at 50% of VAS Stack size * “max worker threads” + Mem. To. Leave User Mode Address space Bpool Committed Memory Bpool Reserves Bpool Committed ~2 Gb-<MTL> Memory Bpool Committed Memory -g startup parameter Default of 256 Mb 256 <=4 procs Add 8 for every proc after 4 Kernel 1 Gb Mem. To. Leave A bigger User Mode Address Mem. To. Leave space Bpool Reserves Bpool Committed ~3 Gb-<MTL> Memory Bpool Committed Memory

64 bit SQL Server calculates the size of RAM during the startup and reserve it , minimum of (reserved space, “Max server memory”) is used as Bpool. Remaining all is Non BPOOL Memory Models: Conventional Locked Large

Memory Models Virtual. Alloc API AWE API • Typically used to allocate large chunks of memory • You manage it • With NUMA, local memory is attempted first Use a heap if you want OS to manage it • Created to extend memory for 32 bit but works on 64 bit • Not part of your working set (not pageable by OS or “locked”) • Requires “Locked Pages in Memory” privilege • NUMA API specific support • Use Virtual. Alloc with MEM_LARGE_PAGES • Typically in 2 Mb or greater sizes • Allocation is slowwwwww Large Pages • Requires “Locked Pages in Memory” privilege 11

Memory Fundamentals Memory Node Memory Allocators Memory Clerks Memory Objects

Here Come the Clerks Similar to cache store without hash tables We don’t use SOS cache or store framework Track Usage by Component Interface for allocators System to Respond to Pressure Caching Infrastructure Brokerage System for Large Users We have hash tables and clocks for pressure Generic Cache Store OBJECTSTORE Simple cache of like equal objects User Store Pool Buffer Pool (BPool) Proc Cache Token Perm Locks CLR Optimizer System Rowset XEvent Metadata SNI Network Packets

SQL Server Memory Model I need memory I understand NUMA I’m a heap I provide pages Memory Node Memory Object Brought to you by SQLOS I know Windows APIs Memory Clerk Memory Allocators Single, Multi, Large Generic Cache Store User Store Memory Pool Page Virtual Windows API Shared Memory

How do I Really Get Memory? Memory Node I cache memory I know Window s APIs Memory Object Plan Cache Memory Allocators Optimizer I use and release Backup Buffers Memory Clerk But I also need to respond to external pressure Database Page Cache Because I’m good at managing a cache of 8 Kb pages Single Page Allocator Buffer Pool Why am I both a clerk and an allocator? Multi Page Allocator Virtual Allocator Most SQL memory comes from the Buffer Pool Virtual. Alloc and AWE APIs

SQLOS 2012 Memory Management SQLOS doesn’t know anything about the Buffer Pool and database pages. Plan Cache Memory Object Memory Node Memory Allocators Optimizer Backup Buffers Database Page Cache Memory Clerk Buffer Pool Memory Clerk There is no single vs multi-page concept 16 Workspaces Fragment Manager Virtual Allocator Block Allocator Virtual. Alloc and AWE APIs

What about the 64 bit BPool? BPool does not commit its target memory at startup Large Pages Exception Differences from 32 bit • No Mem. To. Leave issues • No initial big reservation of VAS • When using AWE, map as you commit when growing • visible always equals target But it may look that way after startup due to initial growth The AWE/”Lock Pages” mystery • Memory allocated with AWE not part of working set so cannot be trimmed • If Lock Pages in Memory/EE SKU, then most BPool commits use AWE • Private Bytes does not show memory allocated with AWE APIs • Not all SQL memory is locked (thread stacks, Multi-page and Virtual allocators Any effects of this design? • Poorly designed DLLs may get more memory because not VAS limited • VAS errors could be now virtual memory error or slow perf due to paging 17

Remain Calm…There is a Memory Pressure Alert External Pressure sys. dm_os_proccess_memory Use Windows Memory Notification APIs Low Physical Memory Low VAS Resource Monitor Task We also recognize working set trim We can’t reserve 4 Mb sys. dm_os_ring_buffers User and Object Stores Cache Stores Buffer Pool CLR Clerk CPU Node Clerk External Clock hand moves, up to store to remove entry External Clock hand moves, entries may be removed Recalc target, We could shrink BPool Garbage Collection End idle worker threads sys. dm_os_memory_cache_clock_hands 18

Internal Pressure: Keeping Ourselves Trim Buffer Pool Target Change Lazy. Writes Near fixed limit sys. dm_os_memory_brokers We won’t go below ‘min server memory’ Procedure Cache Stores User Stores Internal Clock hand moves, up to store to remove entry ‘max server memory’ change Memory Brokers Optimizer Memory Query Execution Memory MEMORYBROKER_F OR_CACHE MEMORYBROKER_F OR_STEAL MEMORYBROKER_FO R_RESERVE Move internal clock hand which may result in removal of proc cache objects Gateways modified. Compiles may wait Thresholds lowered. Sorts/hashes may wait Cache Stores Internal Clock hand moves, entries may be removed sys. dm_os_memory_cache_clock_hands 19

Knowledge Check • • Just because private bytes increases doesn’t mean we are leaking SQL Server is leaking memory. SQL is Culprit AWE is not used on 64 bit SQL Server never allocates > ‘max server memory’ PAE required for SQL 32 bit to use AWE on 64 bit Windows SQL Server allocates all of its memory at startup ‘Lock pages’ guarantees nothing for SQL is trimmed My application will run faster when I go from 32 to 64 bit No one truly understands all of this stuff FALSE Only Large Pages FALSE This may be true

Thank you for attending Tech Unite