August 10, 2014

Checkpoint and lazywrite in SQL

Checkpoint : Write all the dirty pages into buffer cache. It is not clear from buffer cache is called checkpoint.check point is the internal process which will take dirty blocks from buffer pool to data file on interval based.
Lazywriter: Write all the dirty pages into buffer cache. It is clear from buffer cache is called lazywriter

Checkpoint and lazywrite do the same job of flushing out the (8k size) pages from the memory to disk the only difference is of when they do this task.

A lazy write does its task when there is resource contention and sql engine needs to process more pages and bring them in the memory. The lazy writer in this case gets all the pages which have been residing in the memory for the longest time and flushes them to the disk.

A checkpoint on the other hand does the task for the SQL engine to create a stable point from which SQL can recover in case there is a failure of service during some processing. A checkpoint is not issued due to lack of resources.

Please note that in both cases the pages flushed or written to the disk are still marked dirty. So in case sql service fails after a lazy write or a checkpoint, after starting again the SQL will know which flushed pages were marked dirty and those\ changes would be rolled back.

No comments:

Post a Comment