Delayed Transaction Durability is a new SQL Server 2014 feature that was not included in SQl Server 2014 CTP1 but discreetly published in CTP2. It is a very interesting functionality that helps reducing the IO contention for transaction log writes.

In other databases engine, you will find equivalents such as:

  • Oracle: ‘COMMIT WRITE BATCH WAIT|NOWAIT’ (link here)
  • MySQL: ‘group_commit’ since version 4.x (link here)
  • PostgreSQL: ‘group_commit’ introduce in version 9.2 (link here)

What is it for?

It is very simple: ‘Delayed Transaction Durability’ helps reducing the IO contention for transaction log writes. Transaction commits are asynchronous. The most important advantage is to release faster locks, but you have to consider that data can be lost (;-))…

Full or Delayed Transaction Durability

What kind of Transaction Durability should you choose – full or delayed? This is not an easy topic:

Transaction durability Explanation Advantage Disadvantage
Full transactions are written in the transaction log to disk before returning control to the client No data loss IO contention during log writes
Delayed transactions are written in the transaction log to disk after returning control to the client reduces IO contention Data loss possible

The best way to apply Transaction Durability maybe consists in mixing both types…
This feature is not available for In-Memory OLTP transaction but only for regular transaction.

How to use it?

This option is available in 3 control levels:

  • Database
  • Atomic block in Natively Compiled Procedure
  • COMMIT command option in T-SQL

Database level control

You can set this option with SSMS GUI or directly by script.
GUI: Right-click on database > properties > options -:

Delayed-durability.png

Script:

ALTER DATABASESET DELAYED_DURABILITY = {DISABLED (default) | ALLOWED | FORCED}

To insure backward compatibility, this option is set on DISABLED by default.

Three setting options are available:

  • DISABLED: feature cannot be used
  • ALLOWED: each transaction controls its durability (default is OFF for Atomic block in Natively Compiled Procedure and COMMIT command option in T-SQL)
  • FORCED: every transaction is under delayed durability (might be dangerous)

Atomic block in Natively Compiled Procedure level control

In the creation of the stored procedure, it is just the option DELAYED_DURABILITY with ON or OFF like this sample:

CREATE PROCEDUREWITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERASBEGIN ATOMICWITH(    DELAYED_DURABILITY = ON,    TRANSACTION ISOLATION LEVEL = SNAPSHOT, ….) END

COMMIT command option in T-SQL level control

In the same way as for the Atomic Block in Natively Compiled Procedure, the COMMIT command has the option to set DELAYED_DURABILITY ON or OFF.

Conclusion

Delayed Transaction Durability is a new feature in SQL Server 2014, but given the feedback from my colleagues concerning other database systems (thanks a lot!), I would advise to use it with caution.
Data loss is a risk for many applications and the data consistency is not granted, but in most case, you should have no problem.
A good example is a (very, very good) application inserting a lot of data and for each INSERT line a COMMIT is done…
Don’t smile, it’s a common use case!

It is very practical in this case to have this option available in order to insure a fast workload.
I hope this will help you find the right answers and encourage you evaluating SQL Server 2014 CPT2 in order to resolve IO contention problems. SQL Server 2014 CTP2 is downloadable here.
For more information on Delayed Transaction Durability, look up the msdn website here.