Check point

Check point writes out, to disk, all data pages that have changed since the last check point. The goal of checkpoint is to reduce the amount of time Sql Server takes to perform rollforward and rollback operations. As part of this, it makes sure all the dirty pages are written to disk, so when Sql Server restarts after a catastrophe or something else, the amount of work Sql Server needs to do to bring the database current is minimized.

During normal operations, all the data changes are written directly to the data pages in memory. These changes need to be written to the disk (MDF & LDF) files. On a busy database server, if CHECKPOINT does not happen for longer periods of time, there will be many dirty pages in memory. If such a database experiences unexpected restarts (or other catastrophe’s), all the dirty pages in memory are lost (not really…Sql is smarter). CHECKPOINT minimizes this in two ways.

1. By regularly writing pages to disk, all the data changes are safe.
2. The dirty pages that are still in memory during such catastrophe (and are lost) need to be re-played using entries in transactional log file (LDF).
   1. Rollforward helps replay the transactions that are committed, but not written to disk yet.
   2. Rollback helps remove transactions that were written to disk but were not committed.
3. For large & busy databases there will be many such transactions, resulting it longer duration to bring the database back to operational after a disaster. CHECKPOINT helps minimize this by frequently writing pages to disk.

One can manually issue CHECKPOINT, but running the command like this:

• Checkpoint, writes any dirty data to disk irrespective of the transaction commit status. When a transaction modifies a bunch of pages it results in dirty pages. Let’s say the transaction is still running and has not committed yet. But if the CHECKPOINT occurs before the transaction commit, those dirty pages are still written to disk by CHECKPOINT.
• Usually this runs every 1 minute or so, but is configurable. One of the settings in sys.configurations table (“recovery interval (min)”) affects the frequency of CHECKPOINT.

Lazywriter

Lazywriter also flushes dirty  pages to disk. Sql Server constantly monitors memory usage to assess resource contention (or availability); It’s job is to make sure that there is a certain amount of free space available at all times. As part of this process, when it notices any such resource contention, it triggers LazyWriter to free up some pages in memory by writing out dirty pages to disk. It employs Least Recently Used (LRU) algorithm to decide which pages are to be flushed to the disk.

• There is a dedicated LazyWriter thread for each NUMA node.
• If LazyWriter is always active, it could indicate memory bottleneck.

Log Flush

Log Flush also writes pages to disk. The difference here is that it writes pages from Log Cache into the Transactional log file (LDF). Once a transaction completes, LogFlush writes those pages (from Log Cache) to LDF file on disk.

Each and every transaction that results in data page changes, also incurs some Log Cache changes. At the end of each transaction (commit), these changes from Log Cache are flushed down to the physical file (LDF). So, in essence, the number of log flushes depend on number of transactions.

• Please note that SELECT statements do not result in any data page changes, so there are no changes to Log Cache, so no Log Flushes to Transactional Log file.

Hope this helps,
_Sqltimes