Lazywriter:
ü Flush dirty pages to disk.
ü Check for available memory and removed Buffer pool.
ü Occurs depending upon memory pressure and resource availability
ü Name says Lazy so it’s lazy, Sql server manages by its own.
ü Monitor the memory pressure and try maintaining the available free memory.
ü No role in recovery
ü It occurs per requirement
ü Works on Least recent used pages and removed unused plans first, no user control.
ü No effect on recovery model.
ü No command for Lazy Writer
ü No performance impact
Checkpoint:
ü Flush only Data pages to disk
ü Default, Occurs for every 1 minute
ü Can be managed with sp_ configure recovery interval option
ü Does not check the memory pressure
ü Crash recovery process will be fast to read log as data file is updated.
ü Occurs for any DDL statement and before Backup/Detach command
ü Depends upon the configuration setting, we can control.
ü For simple recovery it flushes the t-log file after 70% full.
ü Can manually /Forcefully run command “Checkpoint”
ü Very Less performance impact