Delayed Durability in SQL Server 2014

                                                                                                                                       
Delayed Durability in SQL Server 2014

Firstly what is Delayed Durability?

We all aware of ACID property, the last letter in it is "D"[Durability] which brought this topic.

In SQL Server, changes to data are written to the log first. This is called write ahead logging (WAL).
Control isn't returned to the application until the log record has been written to disk (a process referred to as "hardening").
Delayed durability allows you to return control back to the application before the log is hardened.
This can speed up transactions if you have issues with log performance. Nothing is free, though, and here you sacrifice recoverability.
Should the database go down before the log is committed to disk, then you lose those transactions forever.

History behind Transaction Commit:

Whenever we commit the transaction(SQL Server is auto commit by default), log buffer data are the one which is first flushed into the disk
even before the original data[present in data buffer] into the physical disk. On completion of log flush into disk, all locks associated with the transaction will be released.
The transaction’s locks cannot be dropped until the log flush completes. So whenever log buffer entries made into the physical log files, transaction attains the final property of Durability.

Normal Transaction vs Delayed Durability Transaction 

Under normal circumstances, when a transaction commits, the commit doesn’t complete until the log block for the transaction has been flushed to disk.
Whereas in the case of delayed durability transactions are considered to be complete, even before log flush occurs.
Hence other transactions can acquire locks held by current transaction.

Scenario:

Think of a workload such as, all the other transactions are waiting for the one that is committing, as they all need the same locks, so Transactions/sec is tied to Log Flushes/sec in this case.

With delayed durability, the transaction commit proceeds without the log block flush occurring – hence the act of making the transaction durable is delayed.
Under delayed durability, log blocks are only flushed to disk when they reach their maximum size of 60KB.
This means that transactions commit a lot faster, hold their locks for less time, and so Transactions/sec increases greatly (for this workload).
You can also see that the Log Flushes/sec decreased greatly as well, as previously it was flushing lots of tiny log blocks and then changed to only flush maximum-sized log blocks.

Advantages/Benefits: 

By enabling the delayed durability, no of transactions per sec will be greatly improved.
Since other transactions doesn't need to wait for the current transaction till it is being logged.

Disadvantages: 

Your transactions aren’t durable when they commit. If the system crashes we will end up losing the transactions(though they are committed)
which is in the log buffer.

Key Notes:

Delayed Durability can be enabled at database level, COMMIT level, or ATOMIC block level in Natively Compiled Stored Procedures.
For more details please refer: https://msdn.microsoft.com/en-us/library/dn449490.aspx