By

Susantha Bathige

MINDING SQL SERVER MEMORY

AGENDA• Introduction

• 32 bit vs 64 bit

• Terminology

• Memory support in Windows

• SQL Server Memory

PHYSICAL MEMORY• RAM (system memory, main memory, primary storage)

• DIMM’s

• Measured in MB or GB

• Faster access (5-12GB/sec)

• Volatile

• Expensive

• Windows Server 2008 R2 Datacenter and Enterprise Editions can now support systems with 2TB of RAM

VIRTUAL ADDRESS SPACE (VAS)• Mechanism to share the limited physical memory among the processes

• Set of addresses that the processor uses to access anything on its bus

• Some are reserved for

• Video RAM

• HW buffers, etc.

• 32-bit w/o PAE, the address bus is 32 bits (4GB)

• 32-bit Intel server processors with PAE, the address bus was 36 bits (64GB)

• Limit address bus to 44 bits on 64-bit (16TB)

VAS

Source : Professional SQL Server® 2008 Internals and Troubleshooting

• Address space is limited to 4GB

• /3GB switch increases the user mode VAS to 3GB

• /PAE Switch

• Intel technology

• Increases address bus to 36bits

• Up to 64GB or 128GB of RAM

• Theoretical limit is 18,446,744,073,709,551,616 Bytes

• 16 exabytes =

16,777,216 petabytes (16 million PB)

17,179,869,184 terabytes (17 billion TB)

17,592,186,044,416 gigabytes (17 trillion GB)• Practical limitation – 44 bits address bus (16TB)

• Enough address space for the foreseeable future

• Logically it's split into an 8TB range for user mode and 8TB for kernel mode

VIRTUAL MEMORY MANAGER (VMM)• Joins physical memory and virtual address space together

Source : Professional SQL Server® 2008 Internals and Troubleshooting

TERMINOLOGY• Processes

• essentially container that hosts the execution of an executable image file

• process has a virtual address space that holds the process’s private and shared data

• Threads

• a process includes one or more threads that actually execute the code in the process

• PTE

• OS mechanism of mapping VAS to physical memory page

• Page file

• a disk file that the computer uses to increase the amount of physical storage for virtual memory

TERMINOLOGY• Reserved memory/MemToLeave

• applications reservations to create a large block of VM. only in the startup process in 32-bit SQL Server

• Current commit charge

• amount of committed virtual memory for all the active processes. RAM size is the max limit

• Page fault

• writes the data from page file into memory

• Soft page faults

• occur when a new page of memory is required (VirtualAlloc)

• Hard page faults

• occurs when SQL Server tries to access a page of its memory that has been paged out to the page file

TERMINOLOGY…• Private bytes

• bytes allocated exclusively for a specific process

• Committed memory / Working Set

• the shared and private bytes allocated to a process backed by RAM. reflects the physical memory usage of a process

• Paged pool

• windows can write the data it stores to the paging file

• Non-paged pool

• the kernel and device drivers use non-paged pool to store data that might be accessed when the system can’t handle page faults

SQL SERVER MEMORY• Three levels (bottom to top)

• Memory nodes

• Memory clerks, cache stores

• Memory objects

• Only memory clerks can access memory

• Create new memory clerks if want to allocate more memory

MEMORY NODES• DMVs

• sys.dm_os_memory_nodes

• More memory nodes if NUMA enabled

• Each memory node has its own memory clerks and caches

• Total usage is calculated using the sum of all the nodes

MEMORY CLERKS, CACHES, AND THE BUFFER POOL

• Clerks

• DMV - sys.dm_os_memory_clerks

• MEMORYCLERK_SQLGENERAL

• MEMORYCLERK_SQLQUERYPLAN

• Troubleshooting much easier

• Cache

• object store, cache store, user store

• DMV - sys.dm_os_memory_cache_counters

• Buffer pool

• manage data cache

• DMV - sys.dm_os_buffer_descriptors

PLAN CACHE• Cache all the execution plans

• DMV - sys.dm_exec_cached_plans

• Look for the procedure cache option in DBCC MEMORYSTATUS

PLAN CACHE IN A PROD SERVER• What is Adhoc?

• Use of DBCC FREESYSTEMCACHE('SQL Plans')

QUERY MEMORY/WORKSPACE MEMORY

• Used to temporarily store results during hash and sort operations

• Allocated out of the buffer pool

• DMV – sys.dm_exec_query_memory_grants• Perfmon counters

• Granted Workspace Memory (KB)

• Maximum Workspace Memory (KB)

• Memory Grants Pending

• Memory Grants Outstanding

• RESOURCE_SEMAPHORE wait type

• Demo

MIN AND MAX SERVER MEMORY

• Control only buffer pool size

• Min

• min physical memory try to keep committed

• when the SQL Server service starts, it does not acquire all the memory configured in Min

• Max

• when the SQL Server service starts, it does not acquire all the memory configured in Max

• a "ceiling" for the buffer pool

• Can be used “sp_configure” to set

AWE ENABLED OPTION

• In SQL Server 2008 R2

• SQL Server process needs to have the "Lock Pages in Memory" Advanced User Right in Windows

• Only data pages can be stored in AWE mapped memory

AWE ENABLED OPTION• Deprecated in SQL Server 2008 R2

• SQL Server 2012, this configuration option and the feature that uses this configuration option are removed from the product (32 bit and 64 bit)

Source : http://support.microsoft.com/default.aspx?scid=kb;EN-US;2644592

LPIM• AWE APIs to allocate and map physical memory to process address space

• OS never paged out AWE API allocated memory

• "Lock Pages In Memory“ right need to be granted

• Simplified in SQL Server 2012 (32 bit and 64 bit)

• Need to revisit the “max/min server memory”

• Not be part of the working set

• Overall system performance is a concern due to memory starvation

LPIM RIGHT AND SQL SERVER

Source : http://support.microsoft.com/kb/2659143

IS LPIM ENABLED?• use sys.dm_os_memory_nodes, sys.dm_os_nodes dmvs

• Memory Manager section of DBCC MEMORYSTATUS• Current SQL Server Error log

Using

locked pages

in the memory manager

WINDOWS MAX MEMORY?• Why 2TB maximum limitation on Windows?

• Windows Server 2008 Datacenter support maximum

• Not h/w limitation

• Testing limitation

•

CHANGES IN SQL SERVER 2012• memory allocation is controlled by the max server memory (MB) and min server memory

(MB) configuration options.

Source : http://support.microsoft.com/default.aspx?scid=kb;EN-US;2663912

CHANGES IN SQL SERVER 2012• Changes to "memory_to_reserve“

Source : http://support.microsoft.com/default.aspx?scid=kb;EN-US;2663912

PERFMON COUNTERS• Process: Page Faults / sec

• Memory/Pool Paged Bytes

• Memory/Pool Nonpaged Bytes

• Memory\Free System Page Table

REFERENCES• http://support.microsoft.com/default.aspx?scid=kb;EN-US;2644592

• http://support.microsoft.com/kb/2659143

• http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx

• http://sqlblog.com/blogs/sqlos_team/archive/2012/06/20/sql-server-2012-memory-manager-kb-articles.aspx

• http://msdn.microsoft.com/en-us/library/aa175282(v=sql.80).aspx

• Book: Professional SQL Server® 2008 Internals and Troubleshooting

• Whitepaper - "Advances in Memory Management in Windows"

Q & A