Checkpoints and the Lazy writer


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

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


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 –

Have a nice day



Tags: ,

About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.