Based on this article from sql-serverperformance.com, I created this graphical representation the Lazywriter and how it interacts with the Memory Pool.
The Lazy writer serves two purposes:
- Ensure that a specified number of buffers are free in the Buffer Pool so they can be allocated for use by the server.
- Monitor the usage of committed memory by the Buffer Pool and adjust it as necessary so that enough physical memory remains free to prevent Windows from paging.
The Lazy writer can adjust the number of buffers in the buffer pool if dynamic memory management is enabled. SQL Server estimates the number of necessary Buffer Pool buffers based on system activity and based on the number of stalls. A stall occurs when a request for memory has to wait on a free buffer page.
To monitor the Lazy Writer use this perf counter: SQLServer:Buffer Manager\Lazy writes/sec. This counter represents the number of buffers written by buffer manager’s lazy writer. This counter tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Usually this should not be a high value, say more than 20 per second or so. Of course you will need to compare it with your established baseline. Ideally, it should be close to zero. If it is zero, then the buffer cache is big enough and SQL Server doesn’t have to free up dirty pages. If this value is high, then you may be experiencing a memory bottleneck.