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

About these ads
This entry was posted in Internal and tagged , , . Bookmark the permalink.

5 Responses to Difference between Checkpoint and LazyWriter

  1. Raj says:

    Super Stuff .. thanks

  2. SOUMEN MALLICK says:

    really awesome …

  3. balu says:

    exceelent

  4. and says:

    Greetings from Carolina! I’m bored at work so I decided to browse your website on my iphone during lunch break.
    I enjoy the knowledge you provide here and can’t
    wait to take a look when I get home. I’m shocked
    at how fast your blog loaded on my phone .. I’m not even using WIFI, just 3G ..

    Anyhow, fantastic blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s