SQL Server Memory Allocation and Monitoring - Part 1

This article gives an overview of how SQL server memory is allocated during SQL server start up and how can we monitor the memory used.

This article has been written based on the facts explained in the following MSDN pages and Blogs, definition and texts may have been taken from the MSDN/blogs specified below,
http://msdn.microsoft.com/en-us/library/ms187499(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms189334(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms178145(v=sql.105).aspx
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx
http://sqlserverpedia.com/wiki/Memory__Buffer_Cache_and_Procedure_Cache

Let us start with basic components of a SQL Server Memory to understand better how its allocated,

SQL Memory has 2 basic components,

 

1. Memory to Leave(MemToleave)

2. Buffer Pool(Bpool)

 

Memory to Leave is a space used for external sources like COM objects(eg: sp_OACreate), Extended store procedures, linked servers, OLEDB providers etc, MemToLeave is the smaller component and usually of a fixed size in 32 bit architecture 384 MB approximately. It 64 bit system VAS reservation does not happen at the start up, We will see more about VAS allocation further in the topic.

 

Buffer Pool holds the majority of the SQL memory and is divided into multiple section,

• Data Cache/Buffer Cache - Holds the data pages uses in SQL operations.
• Procedure Cache/Query Cache - The name Procedure cache may be misleading but it stores all the execution plans of queries and store procedures.
• Log Cache  - stores data related to Transaction log pages.
• System Level Data Structure - Instance level data
• Connection context - stores connection information
• Stack Space - Windows allocated stack space

Virtual Address Space:-

 

The virtual address space(abbreviated VAS) for a process is the set of virtual memory addresses that it can use. The address space for each process is private and cannot be accessed by other processes unless it is shared.

 

A virtual address does not represent the actual physical location of an object in memory; instead, the system maintains a page table for each process, which is an internal data structure used to translate virtual addresses into their corresponding physical addresses. Each time a thread references an address, the system translates the virtual address to a physical address.

 

Memory Allocation:-

 

SQL server memory allocation has various scenario's based on the server Architecture(32bit or 64bit) and configurations (AWE,/3GB). Let us look at various scenario's

 

1. 32 Bit with No AWE or /3G Option enabled:-

All 32-bit applications(SQL Server) have a maximum of only 2^32 or 4-gigabyte (GB) of process address space. Microsoft Windows operating systems provide SQL Server with access to 2 GB of process address space, specifically known as user mode virtual address space(VAS). All threads owned by an SQL Server share the same user mode virtual address space. The remaining 2 GB are reserved for the operating system (also known as kernel mode address space). As we discussed already SQL Server memory is in turn divided into MemToLeave and Buffer Pool. On SQL Server Start up SQLOS reserves MemToLeave area which is usually a constant around 384 MB or ((MaxWorkerThreads * StackSize) + DefautReservationSize) and rest of the available space in the user mode VAS is taken by the Buffer Pool.

 

2. 32 Bit with /3GB switch enabled:-

All operating system editions starting with Windows 2000 Server, including Windows Server 2003, have a boot.ini switch that can provide applications with access to 3 GB of process address space, limiting the kernel mode address space to 1 GB, so in contrast to the above condition 4GB memory is split as 3 GB user mode VAS and 1 GB Kernel Mode VAS when /3GB swith is enabled. On SQL Server Start up SQLOS reserves MemToLeave area which is usually a constant around 384 MB or ((MaxWorkerThreads * StackSize) + DefautReservationSize) and rest of the available space (3072 MB - 384 MB) in the user mode VAS is taken by the Buffer Pool.

 

3. 32 Bit with AWE option enabled:-

Address Windowing Extensions (AWE) extend the capabilities of 32-bit SQL Server by allowing access to as much physical memory as the operating system supports with a max up to 64GB.  when AWE option is enabled on a SQL Server we have to keep in mind that only SQL server buffer cache(data cache) can utilize memory more than 4GB hence the MemToLeave remains the same around 384 MB or((MaxWorkerThreads * StackSize) + DefautReservationSize) .The maximum size of the buffer pool is the upper limit of the Physical RAM on the SQL Server or the max server memory setting, whichever is smaller. 

 

4. 64 Bit Architecture:-

64 bit applications can support Address space upto 2^64 or some xx Trillion GB which at present scenario much larger than the memory capacity, so the user mode VAS and Kernel mode VAS are capped to 8TB of space each. On start up VAS reservation does not occur in 64 bit system due to the size available. Also AWE Option is ignored in 64 bit Architecture. SQL Server allocates memory dynamically based on the requirement to the upper limit of the Physical RAM on the SQL Server or the max server memory setting, whichever is smaller. 

 

What is Lock Pages in Memory?

 

Under default configuration, all SQL Server's memory allocations are made in the user mode VAS. Any memory allocated in User VAS is pageable, meaning that the Windows OS can force this memory to be paged to disk, in response to memory pressure. periodically paging out a large amount of memory from SQL Server’s working set will have bad effect on SQL Server performance. Adding SQL server service account to Lock Pages in Memory will prevent buffer cache/data cache portion from paging to disk during windows OS low memory notification. In 32 -bit architecture when AWE is enabled we need to Lock Page in memory to exclusively lock the AWE memory from paging. In 64-bit architecture there is a debate still in SQL Server community whether its good to have lock page in memory to exclusively lock SQL server allocated memory. I would advise we can Lock pages in Memory in 64 bit architecture but we need to ensure we give enough memory to OS too by setting  'max server memory' setting to SQL Server.

 

In the next blog we can discuss on how we can monitor the memory allocated and used in SQL Server and how can be detect memory pressure using DMV and Perfmon.