In this blog we shall see about Buffer Pool and Mem To Leave a little more in detail.
The Buffer Pool
Buffer Pool comprises of a major component for SQL server memory. It comprises of various chunks of memory spread across User Mode of Virtual Address Space. Apart from the sections mentioned above any temporary memory needed by SQL server to perform hash or sort operations for a given query come from buffer pool. If the existing memory is insufficient SQL server may also use tempdb for the same.
Thread Space Calculation
We know that SQL server executes tasks in terms of threads.
Microsoft suggests the below tables for configuring maximum number of worker threads
Number of CPUs
<= 4 processors
StackSize is the space required to create each thread stack. For each thread On x86 (32-bit) its 0.5MB, on x64 (64-bit) its 2MB, and on IA64 its 4MB.
Determining the Maximum Potential for Non-Buffer Pool Usage based on No. of Threads
This option is the most popular, as the aim is to calculate the worst-case scenario for memory requirements other than SQL Server’s buffer pool. You should allow the following:
? 2GB for Windows
? xGB for SQL Server worker threads. You can figure out how many threads your instance will configure using Table shown earlier. Each thread will use 0.5MB on x86, 2MB on x64,
and 4MB on Itanium.
? 1GB for multi-page allocations, linked servers, and other consumers of memory outside the buffer pool. See the “Reserved Memory, or MemToLeave” section for more details and other consumers.
? 1–3GB for other applications that might be running on the system, such as backup programs.
For example, on a server with eight CPU cores and 16GB of RAM running SQL Server 2008 x64 and a third-party backup utility, you would allow the following:
? 2GB for Windows
? 1GB for worker threads (576 × 2MB rounded down)
? 1GB for MPAs, etc.
? 1GB for the backup program
The total is 5GB, and you would configure Max Server Memory to 11GB. This is just a rough estimate how memory for the buffer pool can be calculated.
You can view the existing threads within SQL server with the below query.
You can modify the query further to view the pages allocated per database.
To access these data pages faster SQL server implements a technique called hashing. A hash table is structure in memory pointing to various buffer pages . This is implemented as a linked list with hash tables spanning across multiple hash pages. Given a dbid-fileno-pageno identifier (a combination of the database ID, fi le number, and page number), the hash function converts that key to the hash bucket, any data retrieved by SQL server is from pages within memory. If the page is not available in memory SQL server will fetch the same from the data file to any of the available buffers.When a read is requested, it gets a list of 64 pages in cache and the server checks whether the free buffer list is below a certain threshold.
Bpool needs to maintain information regarding the pages allocated and their types in order to maintain the number of free buffers. It does so using 2 array structures. One stores the pointer to the start of each region in buffer pool. The other stores the count of 8KB pages that are reserved for this region. Each page in the BPool will have a corresponding BUF structure. Each page's BUF structure functions as a type of header for it. Each time a page is referenced, this reference count is incremented. SQL also maintains a free buffer list which contains the count of free pages.
The amount of time that pages stay in cache is determined by a least recently used (LRU) policy. The header of each page (BUF structure) in cache stores details about the last two times it was accessed, and a periodic scan through the cache examines these values. A counter is maintained that is decremented if the page hasn’t been accessed for a while; and when SQL Server needs to free up some cache, the pages with the lowest counter are flushed first.
Pages are first updated in memory only then written onto disk. These include data and log pages. SQL server maintains what is called a Commit Bitmap. As each page in the BPool is committed, its corresponding bit in the commit bitmap is set. The commit bitmap is also set when the reference count decreases as per the LRU-k algorithm. Once a page is dirty and successfully written to disk it is un-hashed and added to the free list. If a page's reference count reaches zero and the page is not dirty, it is simply freed—i.e., moved to the free list without writing anything to disk.
Lazywriter is a thread that runs within SQL server. One for each NUMA node. Its task is to scan the BufferPool to ensure enough number of free pages are available in the free buffer list. It also monitors the server memory. If the OS free physical memory drops below 5MB the lazywriter releases memory to the operating system instead of adding it to the free list. If more than 5 MB of physical memory is free, the lazywriter recommits memory to the buffer pool by adding it to the free list.
Issuing a checkpoint performs similar functionality like lazywriter of scanning the buffer-pool for flushing out any dirty pages. Difference being it cannot move pages to the free list. Checkpoint can be fired manually to run issuing the CHECKPOINT command. Lazywriter thread wakes up on its own during memory pressure. Also all dirty data pages are written to disk by the checkpoint, not just pages corresponding to committed transactions. The job of the checkpoint is to keep the potential time needed to recover the database to a small value.
You can view the all of the Page Life Expentancy, Checkpoint, Lazywriter setting for a given database instance using perfmon as follows
MeM To Leave
As seen in the above diagram MemToLeave is used for Dll's,COM objects, extended stored procs, linked servers, distributed queries,SQL server code.The size of the MemToLeave region can be adjusted using the -g command line parameter. This may be required when more memory is needed for linked server queries or any other objects residing in MemToLeave region. On the other hand reducing this is not advisable but provides more virtual memory space for Buffer Pool region.
Since MultiPage Allocations are a part of MemToLeave region,
Allocations needing contiguous memory blocks higher than 8KB come from this region. It could be procedure cache or data pages as well. However not necessary allocations needing lower than 8KB will always end up in buffer pool. It could also be found in MemToLeave region.
As stated earlier SQL server memory is a vast topic to be covered within a few blogs. But as promised I shall provide the references that may be used to study each of these topics in detail in the next blog.