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.