Tuesday, August 2, 2011

Checkpoint Vs LazyWriter

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":