The work of scanning the buffer pool, writing
dirty pages, and populating the free buffer list is primarily performed by the
individual workers after they have scheduled an asynchronous read and before
the read is completed.
The worker gets the address of a section of the buffer pool containing 64 buffers from a central data structure in the SQL Server Database Engine. Once the read has been initiated, the worker checks to see whether the free list is too small. (Note that this process has consumed one or more pages of the list for its own read.) If so, the worker searches for buffers to free up, examining all 64 buffers, regardless of how many it actually finds to free up in that group of 64. If a write must be performed for a dirty buffer in the scanned section, the write is also scheduled.
Each instance of SQL Server also has a thread
called lazywriter for each NUMA node (and every instance has at
least one) that scans through the buffer cache associated with that node. The
lazywriter thread sleeps for a specific interval of time, and when it wakes up,
it examines the size of the free buffer list. If the list is below a certain
threshold, which depends on the total size of the buffer pool, the lazywriter
thread scans the buffer pool to repopulate the free list. As buffers are added
to the free list, they are also written to disk if they are dirty.
When SQL Server uses memory dynamically, it must
constantly be aware of the amount of free memory. The lazywriter for each node
queries the system periodically to determine the amount of free physical memory
available. The lazywriter expands or shrinks the data cache to keep the
operating system’s free physical memory at 5 MB (plus or minus 200 KB) to
prevent paging. If the operating system has less than 5 MB free, 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.
SQL Server also releases
memory to the operating system if it detects that too much paging is taking place. You can tell when SQL
Server increases or decreases its total memory use by using one of SQL Server’s
tracing mechanisms to monitor Server Memory Change events (in the Server Event
category). An event is generated whenever memory in SQL Server increases or
decreases by 1 MB or 5 percent of the maximum server memory, whichever is
greater. You can look at the value of the data element, called Event Sub Class, to see whether the change was an increase or a
decrease. An Event Sub Class value of 1 means a memory increase; a value of 2
means a memory decrease.
