SQL Server Memory ArchitectureSumit SarabhaiMicrosoft Corp.

C:/>whoami

9+ years in MSFT

SQL vNext

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, NoSQL 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.

HeapA 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 (x86) vs 64 (x64)

null

64Gb

User 2Gb

Kernel 2Gb

User 2-3Gb

Kernel 1-2Gb4Gb

PAE

increaseuserva (/3gB)

AWE

null

16EB

User 2Gb-8TB

Kernel 8TB …and (alot)

of room to grow user (…

someday)

2TB Physical Memory

Limit

BCDEdit.exe

User 4GbWoW

Address Windowing Extensions

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

//(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) + 256MB =384MB

MTL = (Stack size * max worker threads) + Additional space

BPool = Min (Physical memory, (User address space – MTL)) – BUF structures

Start Your 32bit BPool Engines

16Gb RAM

User Mode Address space

Bpool Reserves~2Gb-<MTL>

MemToLeave

Kernel2Gb

4Gb Address Space Limit

Bpool Committed Memory

If AWE enabled, Bpool can allocate here

Bpool Committed Memory

Bpool Committed Memory

16Gb RAM with /3Gb

User Mode Address space

Bpool Reserves~3Gb-<MTL>

MemToLeave

Kernel1Gb

Bpool Committed Memory

Bpool Committed Memory

Stack size * “max worker threads”

+-g startup parameter

256 <=4 procs

Add 8 for every proc

after 4

Default of 256Mb

Capped at 50% of

VAS

A biggerMemToLeave

WoW gives you

all 4Gb

BPool does not commit its

target memory at startup

64 bit SQL ServerSQL 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:ConventionalLockedLarge

11

Memory Models

VirtualAlloc API

• Typically used to allocate large chunks of memory• You manage it• With NUMA, local memory is attempted first

AWE API

• Created to extend memory for 32bit but works on 64bit• Not part of your working set (not pageable by OS or “locked”)• Requires “Locked Pages in Memory” privilege• NUMA API specific support

Large Page

s

• Use VirtualAlloc with MEM_LARGE_PAGES• Typically in 2Mb or greater sizes• Allocation is slowwwwww• Requires “Locked Pages in Memory” privilege

Use a heap if you want OS to manage it

Memory Fundamentals

Memory Node

Memory Allocators

Memory Clerks

Memory Objects

Here Come the Clerks

Track Usage by Component

Interface for allocators

System to Respond to

Pressure

Caching Infrastructure

Brokerage System for Large Users

Generic

BufferPool (BPool)

CLR

Optimizer

XEvent

Cache Store

Proc Cache

System Rowset

User Store

TokenPerm

Metadata

Pool

Locks

SNI Network Packets

OBJECTSTORE Simple cache of

like equal objects

We don’t use SOS cache or

store framework

We have hash tables and clocks

for pressure

Similar to cache store without hash

tables

SQL Server Memory Model

Memory Clerk

Memory Object

Memory Node

Memory Allocators

Cache Store

User Store

Memory PoolGeneric

I’m a heap

I understand NUMA

Shared MemoryVirtualPage

I know Windows

APIs

I provide pages

I need memory

Single, Multi, Large

Brought to you

by SQLOS

Windows API

How do I Really Get Memory?

BufferPool

Memory Node

Memory Allocators

Single Page Allocator

I know Window

s APIs

Memory Object

Memory Clerk

MultiPage

Allocator

VirtualAllocator

VirtualAlloc andAWE APIs

BackupBuffers

Plan Cache

Database Page Cache

Why am I both a clerk and an

allocator?

Because I’m good at managing a cache of

8Kb pages

Most SQL memory

comes from the

Buffer Pool

But I also need to respond to

external pressure

Optimizer

I cache memory

I use and release

SQLOS 2012 Memory Management

16

SQLOS doesn’t know anything about the Buffer Pool and database pages.

BufferPool Memory

Clerk

Memory Node

Memory Allocators

Memory Object

Memory Clerk

VirtualAlloc andAWE APIs

BackupBuffers

Plan Cache

Database Page Cache

Optimizer

Workspaces

Virtual Allocator

Fragment Manager

Block Allocator

There is no single vs multi-page concept

17

What about the 64bit BPool?

• No MemToLeave issues• No initial big reservation of VAS• When using AWE, map as you commit when growing• visible always equals target

Differences from 32bit

• 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

The AWE/”Lock Pages” mystery

• 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

Any effects of this design?

BPool does not commit its target

memory at startup

But it may look that way after startup due to

initial growth

Large Pages

Exception

18

Remain Calm…There is a Memory Pressure Alert

ResourceMonitor

Task

Low PhysicalMemory

User and Object Stores Cache Stores Buffer Pool CLR Clerk

Recalc target, We could shrink

BPool

GarbageCollection

External Clock hand moves,

entries may be removed

External Clock hand moves, up

to store to remove entry

CPU Node Clerk

End idle worker threads

Low VASWe can’t reserve

4Mb

Use Windows Memory

Notification APIs

sys.dm_os_ring_buffersWe also

recognize working set

trim

External Pressuresys.dm_os_proccess_memory

sys.dm_os_memory_cache_clock_hands

19

Internal Pressure: Keeping Ourselves Trim

Memory Brokers

User Stores Cache Stores

Internal Clock hand moves,

entries may be removed

Internal Clock hand moves, up

to store to remove entry

Buffer Pool Target Change

‘max server memory’ change

Near fixed limit

ProcedureCache Stores

Optimizer Memory

Query Execution Memory

MEMORYBROKER_FOR_CACHE

Move internal clock hand which may result in removal of proc cache objects

MEMORYBROKER_FOR_STEAL

Gateways modified. Compiles may wait

MEMORYBROKER_FOR_RESERVE

Thresholds lowered. Sorts/hashes may wait

sys.dm_os_memory_cache_clock_hands

sys.dm_os_memory_brokers

LazyWrites

We won’t go below

‘min server memory’

Knowledge Check• SQL Server is leaking memory. SQL is Culprit• AWE is not used on 64bit• SQL Server never allocates > ‘max server memory’• PAE required for SQL 32bit to use AWE on 64bit 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 64bit• No one truly understands all of this stuff

Just because private bytes increases doesn’t mean we are

leaking

FALSE

FALSE

FALSE

Only Large Pages

FALSE

FALSE

This may be true

Thank you for attending Tech Unite