What is the role of eager writer?

Question

1. What is the role of eager writer? How this process works internally in SQL server?
2. What is the major difference between eager writer and lazy writer/checkpoint? My understanding here is, that both eager and lazy writer are doing the same job - hardening dirty pages to disk. Is my understanding correct or am I missing any thing over here?
3. I've read at some blog that, "there is no chance for roll forward operation in bulk logged recovery model as the trasactions are minimally logged". If we take the case where a log backup consists of minimally logged transactions, how could this log restore can become successful without any roll forward operation?

I really confused with above questions. Please help me in learning more about eager writer.

Thank you.

Answer 1

1. The information about Eager writer process is available in BOL documents

   Eager writing

   The eager write process writes dirty data pages associated with minimally logged operations such as bulk insert and select into. 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.

   Dirty pages: All pages that have changed in memory since they were read from disk or since the last checkpoint are dirty pages. When pages needs to be read or written onto they are brought from disk into memory.

   The eager writing process do not wait for the I/O operation to complete. They always use asynchronous (or overlapped) I/O and continue with other work, checking for I/O success later. This allows SQL Server to maximize both CPU and I/O resources for the appropriate tasks.

2. My understanding here is, that both eager and lazy writer are doing the same job - hardening dirty pages to disk. Is my understanding correct or am I missing any thing over here?

   Yes they both write dirty pages but they are not same. About checkpoint I suggest you read What is checkpoint and what gets logged. Checkpoint writes all dirty pages to disk while eager writer process writes dirty data pages associated with minimally operations such as bulk insert and select into to disk. Checkpoint also periodically scans the buffer pool.

3. there is no chance for roll forward operation in bulk logged recovery model as the trasactions are minimally logged.

   What you are saying is correct but I would quote, Kalen Delaney who defined minimally logged operations as:

   ones that log only enough information to roll back the transaction, without supporting point-in-time recovery

   What happens in BL recovery model is only enough information is logged to rollback the transaction no information is logged to roll forward the transaction this is clearly shown by Gail Shaw in this Article.

   If we take the case where a log backup consists of minimally logged transactions, how could this log restore can become successful without any roll forward operation?

   First you must note that in every restore it is not necessary that you need to have a roll forward operation. The roll forward comes into picture when transaction changes page which has been read by backup operation and if that transaction commits while backup is going on then when such backup is restored a roll forward of transaction is required and that is done by reading transaction log.

   In case of minimally logged operation the eager writer process guarantees that changes to extent/pages are hardened on disk before the transaction is complete. So the transaction log has information ONLY about the committed changes of the bulk logged operation and so if required it can rollback(it would just go and de-allocate the extents or pages allocated). Since the changes are already written to disk there is no need for roll forward.