Pop quiz: What does ACID stand for?
That should be a pretty easy question for database professionals:
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.
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.
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.
For more information on delayed durability in SQL Server 2014 CTP2, check out Books Online.