Let’s have a quick summary to see the difference between the CHECKPOINT and the Lazy Writer process.



SQL Server uses CHECKPOINT to flush dirty pages in the disk to keep database recovery as per the defined threshold. check

SQL Server uses the Lazy Writer process to flush the dirty and clean pages for keeping free space in the buffer cache to accommodate new pages

CHECKPOINT generates the transaction log records and follows the write-ahead logging process

A lazy writer does not make any entry in the transaction log

CHECKPOINT flushes only the dirty pages

It flushes both dirty and clean pages

We can manually execute the CHECKPOINT or control the CHECKPOINT behavior by setting the appropriate parameters

DBA cannot control on the Lazy Writer process

CHECKPOINT is also dependent on the recovery model

Lazy Writer does not have any relationship with the database recovery model

We can monitor the CHECKPOINT event by using the undocumented system function as well as the trace flags. Starting from SQL Server 2012, it also logs an entry in the error log for the long CHECKPOINT

We cannot monitor the Lazy Writer process

We can check CHECKPOINT LSN in the database boot page

We cannot check the Lazy writer process

No comments:

Post a Comment

Popular Posts