My SQL Server sometimes reports a high amount of Lazy Writes.
e.g. “Lazy Writes per second is 119 Writes/sec”
- What should I be checking to find out why this is high?
- What are potential causes?
The lazy writer process is closely related to checkpoints, so I’ll start with that first
Best SQL Server performance is achieved when pages are read from the buffer. To provide enough free space in the buffer, pages are moved from the buffer to disk. These pages are usually moved at a check point, which can be:
- automatic (occurs automatically to meet the recovery interval request)
- indirect (occurs automatically to meet the database target recovery time)
- manual (occurs when the CHECKPOINT command is executed)
- internal (occurs along with some server-level operations, such as backup creation)
At a checkpoint, all dirty pages are flushed to disk and the page in the buffer cache is marked for overwriting
“For performance reasons, the Database Engine performs modifications
to database pages in memory—in the buffer cache—and does not write
these pages to disk after every change. Rather, the Database Engine
periodically issues a checkpoint on each database. A checkpoint writes
the current in-memory modified pages (known as dirty pages) and
transaction log information from memory to disk and, also, records
information about the transaction log.”
Database Checkpoints (SQL Server)
The lazy writer process periodically checks the available free space in the buffer cache between two checkpoints. If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache
If SQL Server needs more memory and the buffer cache size is below the value set as the Maximum server memory parameter for the SQL Server instance, the lazy writer will take more memory
If SQL Server is under memory pressure, the lazy writer will be busy trying to free enough internal memory pages and will be flushing the pages extensively. The intensive lazy writer activity affects other resources by causing additional physical disk I/O activity and using more CPU resources
To be sure that the server is under memory pressure, check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure. Check the Free List Stalls/sec value as well. If above 2, consider adding memory to the server