SQL Server 2014 – Delayed Transaction Durability

SQL Server 2014 has some great features, with my personal favorite being In-Memory OLTP (Hekaton).  However, in CTP2, a new feature was brought to light which has not received as much attention – Delayed Transaction Durability.  A common misperception is that this feature is only available when using In-Memory OLTP.  In reality, this is just another feature that can be put to use to reduce IO contention.  In this case, the targeted IO contention is transaction log writes.  Additionally, this feature may be helpful when your application is experiencing waits due to locks.  Since delaying log durability results in faster commit times, this effectively results in releasing locks faster.  The downside, is that you may lose data if the log is not flushed to disk for any reason (you can manually execute a flush if needed).

Deploying Delayed Durability

Step 1 – The Database

The database first needs to be configured for delayed durability.  This can be done by turning it on for the entire database, disabling for the entire database, or leaving it configurable at a transaction level.  This is done with an ALTER DATABASE statement.

ALTER DATABASE [DATABASE NAME] SET DELAYED_DURABILITY = {Disabled | Allowed | Forced}

These settings are fairly self-explanatory, but…

    1. Disabled – this means it can’t be used
    2. Forced – every transaction falls under delayed durability (if it can, exceptions are include things like distributed transactions which will always be durable)
    3. Allowed – this means that each transaction can control whether it is fully durable or delayed durable

Step 2:  The Transaction

If the database allows transaction durability to be configured (delayed_durability = allowed on the database), we have 2 options.  The first is to configure this in a natively compiled stored procedure (In-Memory OLTP), and the second is in a standard TSQL commit.

In-Memory OLTP (the books online example, I will dig into this further in a subsequent blog post)

CREATE PROCEDURE <procedureName> …

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS BEGIN ATOMIC WITH

(

DELAYED_DURABILITY = ON,

TRANSACTION ISOLATION LEVEL = SNAPSHOT,

LANGUAGE = N’English’

)

END

The piece that most people seem to be missing right now is that you can do this now without using a natively compiled stored procedure or in-memory table.  For any transaction (in a database that is marked delayed_durability = allowed), a commit statement can control the durability of the transaction.

BEGIN TRANSACTION

COMMIT WITH (DELAYED_DURABILITY = ON)

There are a few caveats to this.  For example, a distributed transaction will override this setting and proceed with delayed_durability = off.  For more on this, check out books online.

Summary

  1. Easy to deploy and use
  2. Use only if you can tolerate some data loss
  3. Use if you are commonly waiting on transaction log writes
  4. Use if you experience heavy contention and locks

My experience working with SQL Server 2014 on IO optimization to this point has been fantastic.  Between In-Memory OLTP and delayed durability, many of the typical IO bottleknecks can be alleviated.  Of course, there are trade-offs, but each project can now be dissected and analyzed as to whether these new features can be used to optimize the workload.  Whether it is a web application, ETL process, etc, I have already pinpointed a number of processes and applications that are prime candidates for these features.  Give them a try, I am sure you will find them useful as well!