SQL Server 2014 – Delayed Durability

Pop quiz: What does ACID stand for?

That should be a pretty easy question for database professionals:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

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.

( NAME = N'Durability', FILENAME = N'C:\SQL2014\Data\Durability.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
( NAME = N'Durability_log', FILENAME = N'C:\SQL2014\Logs\Durability_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

USE [Durability]
id INT IDENTITY (1,1),
currtime DATETIME)

INSERT INTO t1 (currtime) SELECT getdate()

Delayed Durability disabled

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.

 USE [master]

At this point we can use delayed durability, but only if we specifically tell SQL Server to do so. We do that with a COMMIT option.

INSERT INTO t1 (currtime) SELECT getdate()

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

Delayed Durability allowed

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.

Delayed Durability log flush

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.

Also recommended:

Leave a Reply to Colleen M. Morrow Cancel reply

Your email address will not be published.

4 thoughts on “SQL Server 2014 – Delayed Durability

  • ACID tolerates no delays

    Thank you for the article – by and large, you made it very clear that delayed durability means you’re risking data loss, though I would be interested to see just how bad it could be and how CHECKDB might end up interpreting things after a sudden crash, as well as some metrics on actual performance differences.

    However, while you correctly stated that “Durability guarantees that, when a transaction is committed, it stays committed” I must take serious issue with the statement “So, with delayed durability, ACID really becomes ACI…D” – it does not. Delayed durability breaks ACID compliance completely, because the guarantee is no longer there – all the NoSQL products that eventually write to disk after reporting a successful transaction have the same situation, and they’re not ACID compliant either.

    With delayed durability allowed, the database now merely holds the traits of atomicity, consistency, and isolation – just ACI, nothing else, since they must be guaranteed in order to count.