Delayed Durability in SQL Server 2014 – A Hidden Gem.

Till date, a relational database is all about being obedient to ACID rules . But starting SQL Server 2014, this is no longer a Gospel truth(in SQL Server world)! Yes, Microsoft has introduced something called “Delayed Durability” in SQL Server 2014, which is truly an outstanding feature to have in our wallet. Basically, we have flexibility to choose a Database being either ACID or ACIdD(Atomic, consistent, Isolated and durable with some delay). This is one hidden gem which really needs to be marketed besides “In-Memory” for SQL Server 2014 my MSFT IMHO.

Oh, common…there is no such thing as “ACIdD”. I came up with this stupid Abbreviation 😀

When this feature will be useful to you? Does it really matter to you?

Let me ask you this, how many times you’ve seen a wait type “WRITELOG” in your Monitoring tool(s)? Or how many times you’ve seen something is waiting on “COMMIT”? If your answer is “a lot” to either of these, probably you should evaluate this hidden gem.(This may or may not be useful to you, depending on how much data loss is tolerated by your business). You really need to understand what you are doing to your database by turning ON this feature.

How this works?

Delayed Durable transaction(s) Commits are asynchronous and reports to the user(or session) a COMMIT as successful before the log records of the respective transaction are written to disk!!!…Don’t be scared, SQL Server is storing those log records in Log buffer and will be pushing them to T-log on the disk with a delay(Hence, the name delayed durable…They will be durable, but with a delay 🙂 )

What happens if something bad happens before the transactions are really committed(written to the T-log on Physical Disk)?

You will loose those transactions. That’s where “sp_flush_log” comes handy. May be you can schedule to run this against your database(s) every N seconds/minutes depending on the acceptable data loss.

How Much delay?

You have this control…, By default SQL Server will flush the log records from it’s buffer to T-Log on the Disk when the buffer fills or you can issue “sp_flush_log” to flush the log to disk manually.

Note: Transaction durability is guaranteed by SQL Server only after following a flush of the in-memory transaction log to physical disk.

How to Enable this feature?

ALTER DATABASE  DB_Name SET DELAYED_DURABILITY = {Disabled|Allowed|Forced}

Delayed Durability = Disabled:

This is the default, your database will behave normally as it is till SQL 2012. Nothing Fancy…

Delayed Durability = Allowed:

With this setting turned on, each transaction durability is decided at the transaction level by issuing {DELAYED DURABILITY = ON|OFF}. So not all the transactions within your database are subject to delayed durable in this case.

Delayed Durability = Forced:

With this setting turned on, all the transactions are delayed durable within the scope of your database.

Or from GUI(SSMS), just go to your database properties and you can see options as shown below.

Very Nifty right…?

Note: This feature works on any normal user database, No need to be an In Memory Database.

So, now it’s your choice, whether to make use of this feature or not. But this will come to use in a very great manner if your T-Log disks are slow and/or your Writes are being committed with long waits and/or you see WRITELOG waits and/or your users are screaming “Man…., these DML queries are taking for ever to complete” and you know it’s because of your poor T-Log performance. So…there’s a trade off btw Speed and Data being truly available to you if disaster strikes. I bet, we all are supporting lot of databases which are really “Internal Only” type of deal and has no true impact on business with few hours worth of data loss(data staying in other sources internally within company which can be pulled easily if needed) which are hitting T-Logs crazily and transactions just waiting on “COMMIT” to complete. These type of databases are perfect candidates for enabling this awesome feature “Delayed Durability”…

I think this was not available in 2014 CTP1(I don’t have CTP1 to confirm now, I had to Uninstall 2014 CTP1 and Install CTP2 from scratch as Side by Side Installation of 2014 CTP1 and CTP2 is unsupported), So…download SQL Server 2014 CTP2 today and explore this awesome feature and make your self comfortable with this.

Fore More Information :

http://msdn.microsoft.com/en-us/library/dn449490(v=sql.120).aspx

Bottom Line : SQL Server Rocks 🙂