SQL Dirty Pages

Lazy writing , eager writing and checkpoint use asynchronous IO in writing pages to disk.  The purpose of  asynchronous IO is to release resources and  for other tasks to progress.

i.e releases the thread to allow further threads.Usually this takes less than 10 ms – but under circumstances delays can occur.

 When this occurs , you’ll see an error message IO Requests taking longer than 15 seconds to complete

IO  writes in SQL Server are broken down into physical and logical. A Physical IO writes to disk. A Logical IO writes to the buffer cache – a basic SQL Server memory region

When a page is modified in the buffer cache and isn’t moved immediately to disk it’s marked as dirty.

Each time a change occurs on the dirty page a transaction log record is written. A dirty page is not moved from the buffer until the transaction log record is written to disk. The method used is write-ahead logging

The write-ahead logging method ensure the transaction log is written to disk  and is fundamental to the Recovery Manager.

How is the dirty page written to disk?

Eager writing   - Nonlogged bcp,  SELECT INTO, WRITETEXT,UPDATETEXT,BULK INSERT are examples of non-logged operations. To speed up the tasks , eager writing manages  page creation and page writing in parallel. The requestor does not need to wait for all the page creation to occur prior to  writing pages

Lazy writing is a process to move pages containing changes from the buffer onto disk. This clears the buffers for us by other pages.

Checkpoint  writes all dirty pages to disk. SQL Server periodically commits a CHECKPOINT to ensure all dirty pages are flushed to disk.

Explicitly issuing a CHECKPOINT will force a checkpoint

Examples of events causing a CHECKPOINT

a)       net stop mssqlserver

b)       SHUTDOWN

c)       ALTER DATABASE adding a file

If a CHECKPOINT fails , SQL Server must negotiate with the Recovery manager to restore to an earlier checkpoint