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.
Examples of events causing a CHECKPOINT
a) net stop mssqlserver
c) ALTER DATABASE adding a file
If a CHECKPOINT fails , SQL Server must negotiate with the Recovery manager to restore to an earlier checkpoint