Was thinking about to write this blog for quite some time.
|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 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.
Lazy writer is on the server to check when lazy writer occurs use
SQL Server Buffer Manager Lazy writes/sec