SQL Server 2014 is having a lot of useful feature and some are hidden as well. I was able to learn about “delayed durability” feature while preparing my session at GIDS. Since the session is over now, it’s right time to write about this feature. First, let’s understand durability.

In general, every transaction in relational database should follow ACID properties. Atomicity, Consistency, Isolation and Durability.

In SQL Server, durability is maintained by writing log record of modification to transaction log “synchronously”. If write to the disk is slow (or huge data to be written) the total time of transaction would become longer and we would see WRITELOG wait for the SPID. Once log record is hardened to transaction log, client would get commit confirmation.

SQL Server 2014 gives DBA and Application team to achieve durability with a delay. Delayed durability may give you some performance gain at the cost of durability factor of a transaction. It is achieved by writing the log record asynchronously. Log record is still generated and kept in memory area called “Log Cache”. Only difference is that it’s written to disk with the delay. So, even if application has received confirmation from SQL about the commit, it’s not actually hardened to disk. This is what is called as “delayed” durability.  If there is a unexpected failure of SQL before the data is written to disk, the data is essentially lost.

I must emphasize the fact that if your application can’t afford any data loss, don’t use this feature. (I don’t have option to put red flashing light otherwise I would have done that to stress on the data loss factor). Does this mean that you have no control about the flush of the log records from log cache to disk? No, product team has thought of this and provided a extended stored procedure called sp_flush_log. I would talk more about this later.

Now since you know the fundamentals of delayed durability, let’s see how to implement it. This can be controlled at various level. We can set delayed durability at Database Level, Transaction Level and in-memory stored procedure.

Database Level

We can change the setting using SSMS as below

Same options are provided in T-SQL as well.

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = DISABLED

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = ALLOWED

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = FORCED

If we set the value to disabled at database level, it would take highest priority. This means that transaction level setting would not be honored.

Transaction Commit Level

During commit of a transaction we can specify delayed durability option as ON or OFF

COMMIT TRAN [ transaction name | @transaction name variable ] [ WITH ( DELAYED_DURABILITY = { OFF | ON })]

Natively Compiled Stored Procedure Level

To know more about natively compiled procedure, I would like you to read here. While creating natively compiled procedure, we can specify delayed durability as below.

CREATE PROCEDURE SQLServerHelp
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH 
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
    …
)
END

Here is the quick chart which explains effect of settings done at database level and transaction level.

In next part, we would see delayed durability in action.