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.

 CREATE DATABASE [Durability]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'Durability', FILENAME = N'C:\SQL2014\Data\Durability.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Durability_log', FILENAME = N'C:\SQL2014\Logs\Durability_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Durability] SET DELAYED_DURABILITY = DISABLED
GO
USE [Durability]
GO
CREATE TABLE t1 (
id INT IDENTITY (1,1),
currtime DATETIME)
GO
BEGIN TRAN
INSERT INTO t1 (currtime) SELECT getdate()
COMMIT 

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]
GO
ALTER DATABASE [Durability] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO 

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.

 BEGIN TRAN
INSERT INTO t1 (currtime) SELECT getdate()
COMMIT WITH (DELAYED_DURABILITY=ON) 

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.