CHECKPOINT vs LAZY WRITER
CHECKPOINT | LAZY WRITER |
Check point runs in only transaction log file | Lazy writer operates from buffer pool |
Check point is the logged operation and writes to Tlog file | Lazy writer is the non-logged operation and did not write to Tlog file |
Check point can be controlled by user and Sql server engine as well | lazy writer only operate by Sql server engine |
Check point is the background process which triggers every 3 sec | Lazy writer does not have any fixed time line and only occurs when there is memory pressure in the buffer pool |
We can able to track checkpoint into Sql server logs by enabling the trace 3502 | Lazy writer information cannot be able to track into SQL Server logs. |
In memory free pages list not taken care and kept free list | In memory free pages list taken care and kept free list |
We can fire a query to see check point information by using Select * from:: fn_dblog (null, null) WHERE [Operation] like ‘%CKPT’ | We don’t have any query to see lazy writer information |
Command : Checkpoint [time in sec] | No command available |