November 03, 2008

SQL Server 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.

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.

DBCC MEMORYSTATUS
The DBCC MEMORYSTATUS command provides a snapshot of the current memory status of SQL Server. The output from this command is useful in troubleshooting issues that relate to the memory consumption of SQL Server or to specific out-of-memory errors

No comments: