Difference between Checkpoint and LazyWriter

C: Flush dirty pages to Disk

L: Flush dirty pages to disk.

C: Flush only Data pages to disk

L: Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)

C: Default, Occurs approximately every 1 minute

L: Occurs depending upon memory pressure and resource availability

C: Can be managed with sp_confige -recovery interval option

L:It is lazy, Sql server manages by its own.

C: Does not check the memory pressure

L:Monitor the memory pressure and try maintain the available free memory.

C: crash recovery process will be fast to read log as data file is updated.

L: No role in recovery

C: Occurs for any DDL statement

L: Occurs per requirement

C: Occurs before Backup/Detach command

L: Occurs per requirement

C: Depends upon the configuration setting, we can control.

L: Works on Least recent used pages and removed unused plans first, no user control.

C: for simple recovery it flush the tlog file after 70% full.

L: No effect on recovery model.

C: can manually /Forcefully run command “Checkpoint”

L: No command for Lazy Writer

C: Very Less performance impact

L: No performance impact

Checkpoint can be run manullay using command "Checkpoint":