SQLUNINTERRUPTED

With SQL Server 2012 there has been considerable changes in the way Memory is allocated and utilized. In Part 1 of the SQL Server Memory series I would talk about memory configurations on windows Servers and then discuss how memory works for SQL Server 2000.

All windows Application (32/64 bit) are designed to work with the Virtual Memory concept in Windows. Each process is provided with a fixed size virtual memory, regardless of the amount of RAM on the machine. The VM limits (a.k.a the maximum addressable memory limit) for an application is as follows

1. 32 bit Applications: 4 GB
2. 64 Bit Applications: 16 TB

Each application running on Windows has to comply with the Virtual memory Limits as imposed by the architecture.

This Virtual Memory(hence forth referred as Virtual Address space) is divided into 2 regions “User Mode” and “Kernel Mode”. An Application works with only the user mode. In other words they can only play around with the User Mode VAS, and do not have any control on the Kernel Mode.

On a 32 bit(x86) environment the User Mode is 2GB (8 TB in 64 bit environment) and can be increased to 3GB (reducing the kernel mode to 1 GB) using the /3GB switch in the Boot.ini file. With windows 2003 servers a new boot.ini switch was available which allows the user mode to be configured to a value between 2 GB and 3 GB, depending on the user requirements.

On a 64 bit environment, the VAS is practically limitless (8 TB) and hence the application can potentially access all the memory on the Machine.

Physical Memory allocations and usage is directly controlled by some components which runs in the Kernel Mode (Windows Kernel Code). A User Application has no control on the Physical RAM. In order for the Application to access Physical Memory, it provides the windows Memory Management Unit(MMU) with a Virtual address (4 Byte – 32 bit address in the VAS) which is then translated (address translation) by the MMU into a physical address. Windows then reads the content at the physical address and returns it back to the application.

An Application running with default configurations (without AWE in case of 32 bit applications) cannot access more memory than allowed by the size of the User Mode VAS.

SQL Server like all other windows application follows this Virtual Memory concept. Hence a 32 bit SQL Server by default cannot access more than 2 GB of RAM.

SQL Server divides its User Mode VAS into two section. The Buffer Pool and Memory to Leave (MTL region). The MTL is a free memory region inside the SQL VAS, and is used for the following

• Large allocations from SQL Server: These are allocations that request more than 8 KB.
• CLR allocations: These allocations include the SQL CLR heaps and its global allocations that are created during CLR initialization.  (SQL 2005 Onwards)
• Memory allocations for thread stacks in the SQL Server process.
• Memory allocation requests made directly to Windows: These include Windows heap usage and direct virtual allocations made by modules that are loaded into the SQL Server process. Examples of such memory allocation requests include allocations from extended stored procedure DLLs, objects that are created by using Automation procedures (sp_OA calls), and allocations from linked server providers.

The Buffer Pool is a pool of 8K buffers (memory pages) which are used by SQL Server for its operations. Any allocation in SQL Server which requires 8 KB or less memory comes from BPool. SQL Server Data Cache is created in the BPool.

The high level steps performed by SQL (32 bit) during startup is are follows

1. Calculate the amount of RAM on the machine, this sets the upper limit of the SQL Buffer Pool (aka BPool).
2. Reserves the MTL as a single block of memory. In SQL 7/SQL 2000, this value is set to 256 MB by default can be configured using the –g startup parameter. Along with the 256 MB, SQL also reserves space for the thread stack. In SQL 7/SQL 2000, the max number of worker threads (controlled by the Max Worker Threads Configuration option) is set to 256 threads by default. Each thread requires 512 KB of memory. Which Means

      Size of MTL = 256 + (.5 MB * 256) = 384 MB 

3. Once the MTL is reserved, SQL tries to reserve the Buffer Pool. The size of the SQL Buffer Pool is calculated as per the below equation

   Size of BPool = Min ( Min(SizeOfRAM, MaxServerMemorySetting), (VAS-MTL-BufArray-Overhead Memory) )

   Where

   SizeOfRAM - Amount of physical memory

   MaxServerMemorySetting - value Max Server memory configuration option

   VAS - Size of User Mode VAS

   MTL - Size of MTL

   BufArray - An array of Buf structures requires to manage the BPool. Approximately 9MB/GB of RAM

   Overhead Memory: Memory used in loading the DLL's and other executable.

4. If BPool reservation fails for some reason, SQL would not start.
5. Once BPool is reserved, SQL Marks the MTL area as free. This is to ensure that external components can access this memory area.

On a 32 bit environment, the VAS is only 2 GB and thus forms the limiting factor while calculating the size of the buffer Pool. adding more RAM on a 32 bit environment does not help SQL because as the RAM increases, so would the size of the BufArray (~9MB/GB of RAM), which in turn would reduce the size of the BPool. For example on a machine with 64 GB of RAM and Max Server memory set to 50 GB,  the BufArray would be approximately about 576 MB. Which means the BPool Calculation would be

BPool = Min ( Min (64 GB, 50 GB) ,  (2048 MB – 576 MB – 384 MB – ~10 MB)) = 1100 MB.

As the RAM on the machine increases the BPool would continue to become smaller and can cause a lot of performance issues.

For the 64 bit environment the following changes are applicable.

1. Each thread requires 2 MB for the thread stack.
2. The size of the BPool would always be limited by the amount of RAM or the max server memory setting.
3. The VAS is 8TB and not 2GB as in the case of a 32 bit SQL.
4. If Max Server Memory is not set, SQL can consume almost entire memory on the machine.

As mentioned, a 32 bit SQL running on a 32 bit Windows Server by default can only use 2 GB of memory. In order to allow SQL to use more than 2 GB of memory, you would need to use one of the following techniques

1. Use /3GB setting or /USERVA switch. DO NOT use this setting on a machine with more than 16 GB of RAM.
2. Use AWE with SQL Server.

In the next post, I would be talking about how memory operations works in the BPool and MTL.

References

1. How to configure SQL Server to use more than 2 GB of physical memory
2. Address Windowing Extensions
3. Managing AWE Memory