Lazy Writes Performance Issues

Question

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?

Answer 1

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