Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Checkpoints and the Lazy writer

Hi,

Both the lazy writer process and a checkpoint both push in-memory pages out to disk, however that’s where the similarity ends.

The reason that I’m blogging about that is because I can completely understand how people can confuse these two events – however they are totally different. This post was prompted by the fact that I wanted to be clear in my own mind about the difference so that I could clearly explain this to anybody in the future.

A checkpoint is responsible for pushing dirty pages in the buffer pool out to disk at a specified interval.  A dirty page is simply a page that SQL SERVER has changed in memory, but has not yet been written to disk.  The pages need to be written to disk regularly in order to ensure that when SQL SERVER restarts, that crash recovery will not take so long.

A checkpoint can be issued manually, but is most lightly just issued by SQL SERVER as part of it’s general house keeping. The timing of the checkpoints being issues is determined by the setting ‘recovery interval (min)’ of sp_configure more at this link  http://msdn.microsoft.com/en-us/library/ms190770(v=SQL.100).aspx

This setting can also be configured in SSMS

Essentially the ‘recovery interval (min)’ setting lays out the amount of time it will take for each database to recovery.

On the other hand the lazy writer will push dirty pages to disk for an entirely different reason, because it needs to free up memory in the buffer pool. This happens when SQL SERVER comes under memory pressure. As far as I am aware this is controlled by an internal process and there is no setting for it – please leave me a comment if this is incorrect.

Note that both of these mechanisms only push dirty pages to disk.

If you would like to push all pages to disk and clear the buffer pool for that database first issue a checkpoint – ensuring dirty pages are pushed to disk and thus “cleaned” – and then issue a DBCC DROPCLEANBUFFERS

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

If you would like to see how many pages are in the buffer pool for your database, then just query the sys.dm_os_buffer_descriptors DMV

I hope that this has been helpful, writing it down has certainly helped clear it up in my mind.

Here a link to another post that it might be useful reading to – http://sqlinthewild.co.za/index.php/2009/06/26/the-lazy-writer-and-the-checkpoint/

Have a nice day

cheers

Martin.

Comments

Posted by robert matthew cook on 2 May 2011

thanks for the article martin.  there is a wiki page on sqlserverpedia: sqlserverpedia.com/.../Memory_-_Lazy_Writer_and_Checkpoint

the first read of "Note that both of these mechanisms only push dirty pages to disk" was somewhat confusing.

the lazy writer will also release pages that are not dirty to free up memory in the buffer pool but since they are unchanged there is no reason for them to be "pushed to disk"

Posted by Martin Catherall on 4 May 2011

Hi sqlmashup,

Thanks for the link. Perhaps I'll try to word things a little clearer next time.

Thanks again.

Posted by Vinodhk24/7 on 8 August 2012

short and simple yet superb article

Posted by smtzac on 20 April 2014

Thank you Martin for explaining so simply. I had hard time to understand Check Point and Lazy writer. Keep it up in simple English.  

Leave a Comment

Please register or log in to leave a comment.