This bulletin answers a few frequently asked questions about SQL Server’s usage of memory.

Buffer Pool (BPool)
Consists of several fragmented regions (up to 32) of address space used by SQL Server. This area is dynamic (the size can change) and is used by SQL Server for many internal processes, such as storing compiled and execution plans, creating indexes, and allocating for cursors.

MemToLeave
Consists of a single contiguous region of addresses within the SQL Server process space that is set aside at startup. This area is static (the size cannot change) and is left unallocated by SQL Server for the use by components in the SQL Server process space, such as extended procedures, COM/OLE Automation objects, and linked servers. Memory allocations by SQL Server larger than 8 KB are also made from the MemToLeave area.

Reserved
A region of address space that is set aside for future use of a process, and the OS has not used any physical RAM for this allocation.

Committed
A region of address space that is currently being used by a process, and the OS has provided either physical RAM and/or paging file space for this allocation.

Allocation
The act of giving the memory resource to the consumer.

Following are the steps taken by SQL Server during startup (also called "initialization"):

1. Calculate the MemToLeave area and reserve it immediately. The MemToLeave area is calculated as follows:

max worker threads value (default is 255) * Stack Size (default is 512KB) + external needs setting (default is 128MB on SQL 7.0 and 256MB on SQL Server 2000)

For the default values mentioned, the MemToLeave area evaluates to 256 MB on SQL 7.0 and 384 MB on SQL 2000. The -g startup parameter can be used to increase the external needs setting.

2. Check for the minimum of physical RAM or virtual address space supported by the OS and, based on the largest footprint available to SQL Server, reserve and allocate the following (in the order mentioned):

a) BUF array (also called the "buffer head") – A contiguous array to track status information associated with each buffer (8 KB page) in the BPool.
b) BPool – Makes up to 32 allocation requests to the OS to reserve BPool buffers. The -T1604 trace flag can be used at startup to view the allocation requests.
c)"Committed Bitmap array – A contiguous array to track committed and reserved BPool buffers. SQL Server maintains a committed bitmap to track BPool buffers. In the committed bitmap

a) If the bit is on (1) it indicates that the buffer is committed.
b) If the bit is off (0) it indicates that the buffer is reserved but not committed.
c) Initially all bits in the committed bitmap are set to off (0).

3. Set the LazyWriter clock to the 0 position. The first cycle of the LazyWriter will commit any buffers depending upon the memory needed by SQL Server.

4. Release the MemToLeave area that was initially reserved. This ensures that SQL Server always has a contiguous region of address space available in the MemToLeave area.

The LazyWriter process is a periodic process that checks the status of BPool buffers (committed or not committed) in a cycle and evaluates if it needs to increase or decrease the number of committed buffers according to the memory required by SQL Server and memory available to the OS. This process wakes up every 1 second by default and can also be called by other processes to run more frequently. The LazyWriter is also responsible for updating the SQL Server memory counters in the Performance Monitor.

SQL Server grows and shrinks its memory usage dynamically by committing and de-committing buffers from the buffers reserved at startup. The LazyWriter process is responsible for growing and shrinking the BPool. A committed bitmap array is maintained to track the commit or de-commit of buffers.

Growing the BPool
When the LazyWriter wakes up, it checks if SQL Server needs more memory. If so, then the LazyWriter checks the committed bitmap to find a buffer with the bit set to off (0 or not committed) and locates the buffer. The buffer is then committed, and finally the bit in the committed bitmap is flipped to on (1 or committed).

Shrinking the BPool
When the LazyWriter wakes up, it checks if the OS has sufficient memory available to service the memory requests from other applications. If not, then the LazyWriter looks for buffers that can be de-committed. If it finds such a buffer, it is de-committed and the 8 KB page is returned to SQL Server's reserved address space, which can be used in the OS.

SQL Server will not commit the value configured in the min server memory (MB) setting, but it will maintain at least that value once it is reached. As more memory is needed, more buffers are committed, and their bits are flipped to on (1) until the max server memory (MB) setting is reached. SQL Server cannot commit memory exceeding the max server memory (MB) setting.

When SQL Server is configured to use memory dynamically, it polls the system periodically to determine the amount of free RAM available on the system. SQL Server grows or shrinks the BPool to keep 4 MB (on Intel systems) to 10 MB (on Alpha Systems) RAM available on the system. If there is less memory free, SQL Server releases memory to the OS. SQL Server adds memory to the BPool only when its workload requires more memory; a server at rest does not grow its BPool.

SQL Server uses the "max server memory (MB)" setting to ensure that the size of committed buffers (8 KB per committed buffers) does not exceed this limit. It is possible to change this setting while SQL Server is running, as it does not need to adjust the reserved memory and simply increases or decreases the number of buffers that can be committed from the buffers reserved at SQL Server startup. The LazyWriter polls the min and max server memory settings every 60 seconds and then updates the Target Server Memory counter in the Performance Monitor.

This behavior is most likely a user observation of SQL Server's dynamic memory management made while monitoring the Target Server Memory (KB) and Total Server Memory (KB) counters under SQL Server: Memory Manager object in the Performance Monitor:

Target Server Memory (KB) – The total amount of dynamic memory the server can consume. This corresponds to the buffers reserved at SQL Server startup.
Total Server Memory (KB) – The total amount of dynamic memory the server is currently using. This corresponds to the sum of buffers committed in the BPool and OS buffers of type "OS In Use."

When the LazyWriter wakes up, it is designed to shrink the BPool only when the OS does not have sufficient memory available to service the memory requests from other applications. If this is not the case, SQL Server does not de-commit any buffers, thus leaving the committed buffers in memory for fast access and better performance.

On the other hand, if the OS is starved of memory, the buffers should be de-committed, but this may not always be possible, for example, if the buffer is pinned or currently involved in an I/O operation. In such situations, nothing is done, and the LazyWriter will evaluate the status of the buffer in its future cycles. Thus the user may observe a delay before the Total Server Memory (KB) counter drops.

SQL Server needs a region in the virtual address space that it can manage according to its architecture. This is called the BPool area. But it also needs a region in memory for external processes that run along with the SQL Server process. These external processes need not be aware of the memory management architecture of SQL Server, and thus a separate address space called MemToLeave is created for them at SQL Server startup. Since the MemToLeave area is a smaller region compared to the BPool area and it is reserved on SQL Server startup, SQL Server is able to ensure that it will be able to reserve a contiguous region in the address space. Thus SQL Server uses the MemToLeave area for external processes and any memory allocations that are larger than 8 KB. For all memory allocations smaller than 8 KB, SQL Server commits memory from the BPool.

SQL Server 2000 Enterprise Edition introduced support for using Address Windowing Extensions (AWE) to address approximately 4 GB of memory for instances running on Windows 2000 Server, 8 GB of memory for instances running on Windows 2000 Advanced Server, and approximately 64 GB of memory for instances running on Windows 2000 Data Center. Although this allows SQL Server to increase the RAM that can be accessed in the virtual address space available to it, the size of the address space cannot be dynamically managed because there are certain restrictions in mapping AWE addresses, such as

1) You have to de-commit the entire region of address space and cannot de-commit a certain portion like SQL Server does to de-commit individual BPool buffers.
2) You can unmap only data or index pages.

Another restriction is that the memory pool of SQL Server 2000 using AWE cannot be swapped out to the page file. Windows 2000 has to swap out other applications if it needs to use additional physical memory, which may hinder the performance of the other applications. Therefore, you must ensure that there is enough memory outside of the amount used by SQL Server to satisfy the virtual memory needs of other applications running on the computer. You can do so by configuring the max server memory (MB) setting to a value that will leave sufficient memory on the machine for other applications.

To measure the size of the BPool on AWE-enabled SQL Servers, look at the Total Server Memory (KB) counter under the SQL Server: Memory Manager object.
To measure the size of the MemToLeave on AWE-enabled SQL Servers, look at the Private Bytes counter under the Process object.

© 2003 Microsoft