During one of my classes I was asked how SQL Server reserves memory during its startup. I thought I would take a few minutes to describe how memory is reserved and configuration during the initialization of SQL Server.
The first item that is reserved is the MemToLeave memory area. This special contiguous memory area is determined with the following formula. Max worker threads * stack size (512kb) + external memory (MemToLeave)
The default number of worker threads in SQL Server 2005 and 2008 is dynamic so the values depend upon the number of schedulers you have configured for SQL Server.
The formula for the number of worker threads on a 32-bit system is:
· If 4 or less schedulers then you have 256 worker threads
· If you have more than 4 schedulers then the number of worker threads is ((number of schedulers – 4)*8) + 256.
o So 8 schedulers would be ((8-4)*8)+256 = 288
On a 64-bit system the formula is:
· If 4 or less schedulers then you have 512 worker threads
· If you have more than 4 schedulers then the number of worker threads is ((number of schedulers – 4)*16) + 512.
o So 8 schedulers would be ((8-4)*16)+512 = 576
Getting back to the memory reserved on startup, we have the memory for the worker threads on a 32-bit 8 scheduler system as (288*512kb) = 147,456 KB(144MB). For a 64-bit 8 scheduler system: (576*512kb) = 294,912 KB (288MB)
Add this amount to the default MemToLeave area: 256MB and you will get:
· 32-Bit 8 scheduler = 256MB + 144MB = 400MB
· 32-Bit 8 scheduler = 256MB + 144MB = 544M
If you are used to SQL Server 2000, it has the defaults of 384MB (32-bit) and 512MB (64-bit) because of the static nature of the number of worker threads.
Note: These figures utilize the default settings for MemToLeave. If you use the –g startup parameter, your figures should be different. It also uses the default values of worker threads. If you have changed the number of worker threads through sp_configure, your number will be different.
After determining the MemToLeave area and worker thread memory, SQL Server then determines the buffer pool. In order to do this, SQL Server checks for the largest of the two following: Physical RAM, Virtual Address space supported by the OS, and the largest amount of memory available to SQL Server (determined by the max server memory setting). After doing this, SQL Server will reserve and allocate the following memory areas (in order):
1. Buf Array: Tracks status information associated with each buffer
2. BPool: Up to 32 allocation requests to the OS to reserve BPool buffers
3. Committed Bitmap array: Tracks committed and reserved BPool buffers
After reserving and allocating the BPool memory area, SQL Server will then release the MemToLeave area it reserved in the first step in order to have this region of address space available for objects which utilizes MemToLeave memory.
I hope this helps those who have had questions on how memory is determined during the startup of SQL Server. This means that if you change the number of worker threads and add new worker threads, the memory first reserved will grow or if you use the –g startup parameter, the area will also grow. The larger this initial reserved area, the smaller the size of the buffer pool.
