In the Previous blogs we saw about SQL server logical tree and the various memory caches.
http://sqlandmorewithkruti.blogspot.in/2016/02/memory-fundamentals-for-sql-server-sql.html
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
https://msdn.microsoft.com/en-us/library/ms190219.aspx
Number of CPUs
|
32-bit computer
|
64-bit computer
|
<=
4 processors
|
256
|
512
|
8
processors
|
288
|
576
|
16
processors
|
352
|
704
|
32
processors
|
480
|
960
|
64
processors
|
736
|
1472
|
128
processors
|
4224
|
4480
|
256
processors
|
8320
|
8576
|
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.
SELECT * FROM SYS.dm_os_threads
In SQL server terms a buffer is equivalent to a page in memory of size 8KB. A buffer is a page in memory that’s the same size as a data or index page. The main components comprising the data cache and plan cache. You can view the pages in SQL server buffer as follows
You can modify the query further to view the pages allocated per database.
SELECT CASE
database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS 'Database',page_type,
count(*) as "Page Count",
count(*)*8 AS 'Cached Size (KB)'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id, page_type
ORDER BY 'Cached Size (KB)' desc
Hashing
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.
BUF array
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.
LRU-k Algorithm
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.
Commit Bitmap
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.
Lazy-writer
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.
The lazywriter recommits memory to the buffer pool only when it repopulates the free list; a server at rest does not grow its buffer pool. Lazywriter also ensure that the minimum size of buffer pool is maintained before releasing memory back to operating system. SQL Server uses separate physical structures for a page and its header allows a page to be flushed to disk and to "move" from list to list without anything on the page actually changing. Once a page is written onto the disk and marked as free its contents can be over-written.
CheckPoint
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.
Ideally CheckPoint is issued every minute. However this value is set based on the Recovery Interval setting for a database instance.Trace flag 3502 is an undocumented trace flag that records in the error log when a checkpoint starts and stops.
https://msdn.microsoft.com/en-IN/library/ms188748.aspx
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.
One may view the loaded modules through the below command.
SELECT * FROM SYS.dm_os_loaded_modules
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.
