Difference B/w Checkpoint & LazyWriter
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 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:
- 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
LazyWriter:
- Lazy writer is on the server to check when lazy writer occurs use SQL Server Buffer Manager Lazy writes/sec
Source:
Best regards,
Suresh Yepuri