Friday, September 13, 2013

SQL Server Memory Management Part II

SQL Server 32 bit memory architecture

We already discussed that in Win32 bit architecture 4GB address space is equally divided into user mode space and kernel mode space and by adding /3GB switch user space can be increased by 3GB and kernel space will become 1 GB. In that case for 32bit SQL Server 2 (or) 3 GB is the maximum memory available.

SQL Server address space is divided into two different regions

1) Buffer Pool

2) MemToLeave (Memory to leave)

Buffer pool -- The buffer pool area supports all memory requests upto 8KB size, As SQL Server page is 8 KB that means all data and index page allocation requests are supported from buffer pool region.

MemToLeave -- All requests that are greater than 8 KB are supported from this region. This includes memory used for COM objects, CLR Code, Extended stored procedures etc.,

The size of MemToLeave and Buffer pool was decided by SQL Server, On SQL Server startup it looks at the physical memory RAM in the machine and decides how much of VAS (Virtual address space) is needs to reserve for its usage ie., for MemToLeave and then allocation Buffer Pool regions.

MemToLeave = (Stacksize * MaxWorkerThreads) + DefaultReservationSize

(256 * 512 KB) + 256 MB = 384 MB by default

(Stack Size = 512 Kb in 32 bit system, 256 are default MaxWorkerThreads)

After completion of assigning MemoryToLeave then it reserves memory to Buffer Pool area, this was decided by considering other factors like AWE is enabled or not, If AWE is not enabled then the size was reserved by remaining memory user address space (2GB) – MemToLeave (384 by default)

Buffer Pool = Minimum (Physical memory, UserAddressSpace(VAS) – MemToLeave) – MemToLeaveSize

Buffer Pool = Minimum(4 GB, 2GB) – 384 MB

Buffer Pool = (2GB – 384) = 1664 MB

In MemToLeave portion SQL Server Multi Page Allocation (MPA) will be done that means if the page size is greater than 8KB this will be processed in this area and Extended procedures, linked servers providers, COM objects and any other DLL’s that are loaded into this process space.

Note – There is NO MemToLeave for 64 bit version of SQL Server Engine.

AWE -- Address Windowing Extension

It helps SQL Server to make use of large amount of physical memory, In SQL Server 32 bit machine if the physical memory is 8 GB the SQL Server will make use of only 2GB user mode address space, if you enable AWE then SQL Server can make use of more than 2GB address space and this only applies to 32 bit systems.

NOTE - To enable AWE we must run SQL Server using a domain account and that has to be added to Lock Pages in Memory option.


No comments: