What Is Eager Writing in SQL Server?

Here we will understand its behavior and improvement in SQL 2014

Eager writing is a long back implemented idea in SQL server to prevent flooding of the buffer pool with pages that are newly created from minimally logged activities, and physically written to disk. 

• This helps reducing pressure on lazy writer and checkpoint as well as widening the I/O activity window.
• This allow SQL server for better performance and parallel usage of the hardware.
• I/O the weakest part of any system in performance and can be skipped for operations involving tempdb in minimally logged operations.

         The eager write process writes dirty data pages associated with Non Logged / Minimally logged operations. This process allows creating and writing new pages to take place in parallel. That is, the calling operation does not have to wait until the entire operation finishes before writing the pages to disk.

It uses asynchronous IO in writing pages to disk.  The purpose of asynchronous IO is to release resources and for other tasks to progress.

In this process during physical write, buffer manager searches for adjacent dirty pages that can be included in a single gather-write operation. Adjacent pages have consecutive page IDs and are from the same file (disk) irrespective of their order in memory.

Buffer manager search continues both forward and backward in memory until one of the following events occurs:

• A clean page is found.
• 32 pages have been found.
• A dirty page is found whose log sequence number (LSN) has not yet been flushed in the log.
• A page is found that cannot be immediately latched.

As per Bob Dorr: In SQL server 2014 there is a change in behavior of TEMPDB minimally logged operations (Data stored in tempdb is consider as volatile).

                  In SQL Server 2014, eager write doesn’t force dirty pages physical write as quickly as previous versions.   This allows dirty pages to spend their life time in RAM, used by SQL and flush them to free list without ever a physical write to disk as long memory is available. This will significantly increase the performance up to 300% as compared to previous versions