{"id":175,"date":"2009-06-26T22:01:12","date_gmt":"2009-06-26T20:01:12","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=175"},"modified":"2009-06-26T22:01:12","modified_gmt":"2009-06-26T20:01:12","slug":"the-lazy-writer-and-the-checkpoint","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2009\/06\/26\/the-lazy-writer-and-the-checkpoint\/","title":{"rendered":"The Lazy Writer and the Checkpoint"},"content":{"rendered":"

Or “What writes what when?<\/em>”<\/p>\n

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.<\/p>\n

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.<\/p>\n

Lazy Writer<\/strong><\/p>\n

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.<\/p>\n

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.<\/p>\n

Checkpoint<\/strong><\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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<\/p>\n","protected":false},"excerpt":{"rendered":"

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…. (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,16],"tags":[],"class_list":["post-175","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-2P","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=175"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/175\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}