SQL 2014 Delayed Durability - Testing Tran Log Flushes

Recently, I had been reading about delayed durability in SQL 2014 and I came away with the impression that delayed durability makes the database unreliable.

Main reason for this impression is the fact that few blogs and books online mention that delayed durable transactions are written to log file on disk under these conditions

1. A durable transaction is committed
2. 60KB log block in memory is full
3. Someone runs sp_log_flush

I got the impression that if I run one insert statement of say 2 KB and nothing after that, SQL will keep waiting for one of the above events before flushing log to disk.

From all the testing I did and discussions I had with SQL experts, I think it is important to add two more conditions to the list when delayed durable transaction will be written to log file on disk

1.      Automatic periodic log flush by SQL, which happens multiple times per second. It is so quick that I had trouble reproducing data loss until I ran statement in a while loop continuously. Each statement inserted an entry in a column of char(1) in a table that had that one column.

     --Create test table
     CREATE TABLE Names (Name char(1))
     --Run while loop to insert a value in table
     WHILE 1=1
     BEGIN

        INSERT INTO Names
        VALUES ('A')
        WAITFOR DELAY '00:00:00.100' --Change value from 100 to 5 for next test 

     END

Extended Events screen shot when insert statement ran every 100 milliseconds. See the time difference between subsequent log flush events highlighted. Difference between first and second flush is 123 ms(892-769). Third log flush happens after 57 ms(949-892) for second flush.

  

Extended Events screen shot when insert statement ran every 5 milliseconds. Notice the time difference between log flushes. Second log flush happens 9 ms(452-443) after first flush. Third log flush happens 8 ms(460-452) after second flush. Notice that a few times, log flush happened after two transactions were committed. If you run this test with no delay between transactions, you will see several transactions committing between log flushes.

 

 

2.      Checkpoint or Lazy writer runs since both these operations invoke log flush for data pages they send to disk

 

If you run Extended Events to track Begin Transaction, End Transaction, and Log flush while running an insert/update/delete statement, you will see that

1. In case of fully durable transaction, sequence is Begin Tran, Log Flush, and End Tran.
2. In case of delayed durable transaction, sequence is Begin Tran, End Tran, and Log Flush. 

In the screen shot below, DelayedDurableDB(database id 14) has delayed durability FORCED and FullyDurableDB has delayed durability disabled. I ran the same insert statement in both databases. Notice the difference of sequence of events between FullyDurableDB and DelayedDurableDB.

 

 

For small delayed durable transactions, data loss would be minimal depending on speed of IO subsystem and load at that time. Think of it like asynchronous mirroring(Asynchronous log flush). Commit is sent to client/application before log is written to disk but, log is written soon after. It just reduces the transaction time by the amount of time take to flush log to disk.

For larger transactions that may happen for minutes, hours, or days, if the last commit statement log entry is not flushed to disk in time and server crashes, the whole transaction will be rolled back after crash recovery.

 
Delayed durability is more beneficial for small transactions than larger transactions. It won’t save much time for large transactions anyway so, it is better to run them in fully durable mode. It is milliseconds added up in lot of small transactions that really make an impact on performance.

Conclusion: Delayed durability is exactly what is says but the delay is really short.