Colleen Morrow is a database professional living in Cleveland, OH who has been working with database systems since 1996. For more than 12 years, she was a Database Administrator at a large law firm where she developed an appreciation for auditing, automation, and performance tuning. Since that time she has worked with clients in the healthcare, manufacturing, software, and distribution/freight delivery industries. Colleen is currently a Senior Consultant at Fortified Data.

It’s that last property, durability, that I want to talk about today. Durability guarantees that, when a transaction is committed, it stays committed. The changes made by that transaction are permanent, even in the event of a power failure. Now, for years, relational databases have stuck like glue to the ACID rules. SQL Server achieved durability by logging any transaction to the transaction log on disk before it was considered committed. That is, until now.

Delayed Durability

SQL Server 2014 CTP2 introduces a concept called delayed durability. With delayed durability, transactions are logged to the transaction log buffers in memory and control is returned to the application. The log buffers are hardened to disk later. How much later? According to BOL, the buffer will be flushed to disk when any of the following events take place:

sp_flush_log is executed

The in-memory transaction log buffer fills up.

A fully durable transaction in the same database makes a change in the database and commits.

And even though BOL doesn’t specifically mention it, the buffer will also be flushed in the event of an orderly shutdown. So, with delayed durability, ACID really becomes ACI…D.

Delayed Durability in action

Using delayed durability starts at the database level. In SQL 2014 CTP2, we have a new database-level property, DELAYED_DURABILITY. This property can be set to one of three values:

DISABLED – This is the default setting. All transactions are fully durable on commit

ALLOWED – Durability is set at the transaction level, but by default transactions are fully durable unless specified otherwise

FORCED – Delayed durability is applied to all transactions, even if the transaction specifies otherwise

Let’s start by creating a traditional, fully durable database in SQL 2014. We’ll then create a table and insert a record. We’ll also start up Perfmon and monitor Transactions/sec and Log Bytes Flushed/sec.

Looking at Perfmon above, we see that the transaction and the log flush to disk happened simultaneously. But what happens if we use delayed durability? We’ll start by allowing it at the database level.

Now when we look at Perfmon, we see a blip for the transaction, but no corresponding log flush.

To harden that log buffer to disk, we can either run another transaction without delayed durability, or simply use the sp_flush_log stored procedure.

EXEC sp_flush_log

Now we finally see the log bytes being flushed to disk.

When to use delayed durability

First of all, don’t use this if you can’t tolerate the possibility of any data loss. If your database crashes, any transactions that haven’t been hardened to disk will be lost. That said, if you’re seeing a log of transaction log write waits, delayed durability can help alleviate that bottleneck by batching up writes. Also, because delayed durability reduces commit time, locks are released faster, meaning less blocking and higher throughput.

Further reading

For more information on delayed durability in SQL Server 2014 CTP2, check out Books Online.