Difference between Checkpoint and LazyWriter

Was thinking about to write this blog for quite some time.

CheckPoint	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 tlog 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:

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

Please correct/update me if you have any comment on this.

 http://msdn.microsoft.com/en-us/library/ms188748.aspx

http://support.microsoft.com/kb/815436

LazyWriter:

Lazy writer is on the server  to check when lazy writer occurs use

SQL Server Buffer Manager Lazy writes/sec