Or “What writes what when?”
It’s important to understand how and when SQL makes changes in memory and how and when those changes are written to the data file. It has a big impact on memory use and IO patterns.
Data modification queries (insert, update, delete) always make changes to data pages in memory. In fact, queries in general only operate on in-memory data pages. When a query makes a change to an in-memory page, that page is marked as ‘dirty’, indicating that there are changes on that page that have to be written to disk before the page can be discarded from memory. That writing is the job of two system processes, the lazy writer and the checkpoint.
Lazy Writer
The job of the lazy writer is to find dirty pages in the buffer pool and write them out to disk and drop those pages from cache. It does this to keep a certain amount of free pages available within the buffer pool for data that may be requested by other queries. The pages that it writes out are ‘older’ pages, ones that haven’t been used for a while.
If there’s lots of available buffers, the lazy writer will be doing relatively little work and the number of pages written to disk will be quite low. If the lazy writer’s consistently writing lots of data, it may indicate that there’s a memory bottleneck.
Checkpoint
The checkpoint process also writes dirty pages to disk, but it has no interest in keeping available buffers or memory pressure. The job of the checkpoint is to keep the potential time needed to recover the database to a small value.
Recovery is the process that the database must go through when it starts up to ensure transactional consistency. In part of the recovery process SQL uses the transaction log to work out what transactions had committed but may not have had their changes written to disk at the time of the shut down and it replays those transactions. That replay (roll forward) can be extensive on larger, busy databases. To reduce the amount of work required of this roll forward, the checkpoint process runs at regular intervals and writes all dirty data pages to disk and writes an entry into the log indicating that all changes before that point are now on disk.
One important thing to note here is that all dirty data pages are written to disk by the checkpoint, not just pages corresponding to committed transactions. Of course, this means that changes may be written to disk and later rolled back, either explicitly or because the transaction did not complete. This is not a concern. When the rollback happens, the data pages will again be modified in memory and later written to disk. If the SQL service restarts after the pages were written to disk but before the transaction commits, another part of the recovery process will undo those data changes.
The frequency at which the checkpoint process runs is controlled by a server setting: “recovery interval (min)”. This setting specifies how long a recovery should take. The higher this is set to, the less often checkpoint runs. The flip side is that the higher the recovery interval is, the more work checkpoint has to do each time it runs, hence if checkpoint is causing IO contention it may be better to reduce the recovery interval, rather than increasing it
Thanks Gail for this article. What I understand abt the difference between the two is lazy writer works more towards memory management where as checkpoint works more towards recoverability of the database. Is my understanding correct? Also is there a control over lazy writer just as we have for checkpoint?
Pretty much yes. No control that I know over the lazy writer.
Hi Gail,
I forget… Is the lazy writer responsible for ghost records cleanup as well, or is it another process?
Ghost records are cleaned up by the ghost cleanup task, not the lazy writer.
For info on that, see http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Ghost-cleanup-in-depth.aspx.
Any changes to Data Pages are first logged on log cache & then written to Ttansaction Log on disk. during this time if server crash where transaction written to log disk but not Data disk then during recovery it will roll forward but will bring those in to memory first then to data disk, is that right
During recovery SQL will replay the transactions and the changes will be made to the data cache. At the end of recovery, SQL runs a checkpoint, forcing all dirty pages (the ones it just dirtied as party of recovery) to disk
Pingback: Checkpoints and the Lazy writer
Hi Gail,
Great Article, crisp and to the point.
I’ve one doubt regarding Recovery Interval setting, you said if checkpoint is causing IO Contention it is better to reduce the recovery interval time but if i go through MSDN it says vice-versa.
According to MSDN if we reduce the recovery interval setting SQL will try to complete the recovery process within specified time thus employ much more resources than default. here is the link…
http://msdn.microsoft.com/en-us/library/ms188748.aspx
It Says: “The performance impact of using checkpoint_duration depends on the number of dirty pages, the activity on the system, and the actual duration specified. For example, if the checkpoint would normally complete in 120 seconds, specifying a checkpoint_duration of 45 seconds causes SQL Server to devote more resources to the checkpoint than would be assigned by default. In contrast, specifying a checkpoint_duration of 180 seconds causes SQL Server to assign fewer resources than would be assigned by default. In general, a short checkpoint_duration will increase the resources devoted to the checkpoint, while a long checkpoint_duration will reduce the resources devoted to the checkpoint. ”
Please review it and let me know which one is true.
Thanks,
Sarab
That’s talking about checkpoint_duration, not recovery_interval. Two completely different settings.
Checkpoint_duration is how long the checkpoint can run for. Recovery_interval affects how often it runs.
Using checkpoint duration to force the checkpoint to run in less time will increase IO, because it’s forcing the X amount of writes to be done in a shorter time than SQL would be default.
Using recovery interval to make the checkpoint run more often will reduce IO, because it has less to do each time it runs.
Oops…
Now i got it. Thanks for explaining 🙂
Sarab
When lazy writer flush the dirty pages into memory that information does not maintain anywhere as checkpoint information is maintained. so while doing the recovery how those already written transactions by lazywritter are handled?
Same way that ones written by checkpoint are – the log records for those changes goes to disk first, hence they’re in the transaction log, hence recovery can see if it needs to redo or undo them.
Hi Gail,
I have a query about Lazy Writer, i understood that if lazy writer consistently writing lots of data it indicates that there is a memory bottleneck. Am facing the same issue now, can u help how to sort out this issue.. there is 32 GB RAM.
Yes, that is one of the signs of memory pressure.
So, can u guide me how to solve this problem.
Here i ran sp_who2 active command there are more than one result which is executing Lazy Writer in Background process which is consuming lot of memory. I set max memory to 26 GB AWE Enabled and now the PF Usage status is 27.7 GB using.
Correction please,
I set max server memory to 30 GB and the Last Batch for the Lazy Writer Process showing is on 30/01/2012.
If you’re got problems or questions about configs please post then on one of the SQL forums.
Hi Gail,
i posted here http://www.sqlservercentral.com/Forums/Topic568551-146-1.aspx . But no replies.
Thanks Gail,
How lazy Writer understand which buffer is old ? How can someone check whether there is a lot of Lazy Write happening?
There’s information in each buffer page’s BUF structure about it’s age.
Check perfmon, the Lazy Writes/sec counter.