Difference B/w Checkpoint & LazyWriter

Lazy Writer
1. Flush dirty pages to Disk
1. Flush dirty pages to disk.
2. Flush only Data pages to disk
2. Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)
3. Default, Occurs approximately every 1 minute
3. Occurs depending upon memory pressure and resource availability
4. Can be managed with sp_confige -recovery interval option
4. It is lazy,  Sql server manages by its own.
5. Does not check the memory pressure
5. Monitor the memory pressure and try maintain the available free memory.
6. Crash recovery process will be fast to read log as data file is updated.
6. No role in recovery
7. Occurs for any DDL statement
7. Occurs per requirement
8. Occurs before Backup/Detach command
8. Occurs per requirement
 9. Depends upon the configuration setting, we can control.
9. Works on Least recent used pages and removed unused plans first, no user control.
10. For simple recovery it flush the T.log file after 70% full.
10. No effect on recovery model.
11. Can manually /Forcefully run command  “Checkpoint”
11.No command for Lazy Writer
12. Very Less performance impact
12. No performance impact

  • Checkpoint occurs on database level.
  • To find when the checkpoint occur use undocumented function
 Select  * from ::fn_dblog(null,null)  WHERE [Operation] like ‘%CKPT’
or sql server 2000

Select top 10  [Operation],[checkpoint begin],[checkpoint end] from ::fn_dblog(null,null)  WHERE [Operation] in(‘LOP_BEGIN_CKPT’, ‘LOP_END_CKPT’,'LOP_XACT_CKPT’)

Also enabling trace flag will provide information on error log when checkpoint started at what database.

DBCC TRACEON(3502, -1)

Checkpoint impact the performance (very low IO) for heavy system, so we can even disable automatic checkpoint —-Never do this, using trace flag 3505

  • Lazy writer is on the server  to check when lazy writer occurs use SQL Server Buffer Manager Lazy writes/sec

Best regards,
Suresh Yepuri

Do you want to get Latest job updates promptly? If so, Enter Your Email ID in the box to Subscribe:

Web Analytics Clicky