Monday, 15 July 2013

What is the difference between Checkpoint and Lazywriter?



Simplified version:

Checkpoint

1. Objective of checkpoint is to keep recovery time minimum.
2. This is done by scaning dirty pages in buffer cache and writes them to disk.
3. Checkpoint never puts buffers to free list.
4. Scope of Checkpoint is in database level.
5. Checkpoint can be called manually (CHECKPOINT cmd) or automatically
at regular interval (recovery interval property).

Lazywriter

1. Lazywriter is a background thread.
2. Objective of Lazywriter is to handle memory pressure.
3. Each SQL Instance has one/more Lazywriter threads (one per NUMA node).
4. Lazywriter thread is invoked at regular interval.
5. If free buffer count is less, Lazywriter scans 64 buffers and writes dirty pages to disk
and creates free buffers list.
6. Free buffers are released to OS when there is less available memory or if it detects
huge paging.

No comments:

Post a Comment