Basics of SQL Server Memory Architecture
Do you have a question in SQL Server or stuck in SQL Server issue? Click here to join our facebook group and post your questions to SQL Server experts
Enter your email address to subscribe to this blog and receive notifications of new posts by email.
1. 32-Bit SQL Server memory architecture
In the Win32 memory architecture, each process has a 4 GB virtual address space. By default, 2 GB of that address space is accessible from user mode(Application like SQL Server) and the remaining 2 GB is accessible from kernel mode
So in 32 Bit windows architecture 2 GB of memory is maximum available for SQL Server.
Note: When /3GB switch is enabled user mode address space becomes 3GB and kernel mode becomes 1 GB. When 32-Bit SQL Server is running on 64-Bit windows (WOW64) It gets 4GB of user address space .It can also leverage AWE on WOW64 mode and can use more than 4GB.
SQL Server "User address space" is broken into two regions: MemToLeave and Buffer Pool
Size of MemToLeave (MTL) and Buffer Pool (BPool) is determined by SQL Server during start
up as below.
MTL (Memory to Leave)= (Stack size * max worker threads) + Additional space (By default 256 MB and can be controlled by -g).
Stack size =512 KB per thread for 32 Bit SQL Server
I.e = (256 *512 KB) + 256MB =384MB
Additional space to load Dll’s= 256 MB from SQLServer2000. This space is used to store
1. COM objects
2. Extended stored procedure
3. Memory allocated by linked servers (loaded in process ) or other Dll’s loaded in SQL Server proce
4. Memory allocated by SQL Server memory manger if the allocation size in greater than 8K and need’s contiguous memory (Multiple_pages_kb).
Note: Additional space to load Dll’s can be modified using -g startup parameter.
on any machine with less than 4 processors the Maximum worker Thread’s is
always 256 by default (unless we change the value using SP_configure)
SQL Server Buffer Pool is minimum of “Physical RAM “ or “user mode memory(2GB or 3GB) – MTL- BUF structures”
BPool = Minimum (Physical memory, User address space – MTL) – BUF structures