Difference Between CheckPoint and LazyWriter

Checkpoint:

* Flush dirty pages to Disk
* By default Occurs approximately every 1 minute
* Does not check the memory pressure
* Occurs for any DDL statement
* Occurs before Backup/Detach command
* Can be managed with sp_confige -recovery interval option
* Checkpoint occurs on database level.

* To find when the checkpoint occur use undocumented function

select  * from ::fn_dblog(null,null)  WHERE [Operation] like ‘%CKPT’

In 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

* Simple recovery it flush the tlog file after 70% full.


LazyWriter:

* Lazy writer is on the server

* To check when lazy writer occurs use

SQL Server Buffer Manager Lazy writes/sec

* Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)

* Sql server manages by its own.