Published on November 29th, 2014 | by Warner Chaves0
SQL Server 2014 New Features: Delayed Durability
Out of the many new features of SQL Server 2014 there are some that have flown “under-the-radar” and haven’t received as much attention as other big ones (In-Memory OLTP, Updateable Columnstore Indexes). Delayed Durability is one of these underrated features, available in all editions and for all types of tables (Classic and In-Memory).
What does it do?
Delayed Durability (DD to shorten) is basically asynchronous committing. What this means is that SQL Server is NOT going to wait until the log records of a given transaction are hardened on permanent storage, instead it’s going to return from the COMMIT instruction as soon as the records are placed in the log buffer in memory.
What are the advantages?
Since DD doesn’t make SQL wait for permanent storage it can increase throughput by removing WRITELOG waits. Also, when used, SQL will accumulate delayed durable records in memory until the log buffer is full or until a fully durable COMMIT comes in. This means that in general, it will be doing less IO to the log compared to the same workload with DD turned OFF.
When should you use it?
Now, the moment I mentioned that DD was “asynchronous commit” some red flags should have gone off in your head. Indeed, with DD there is a possibility that you could have data loss. This is because there are moments in time where a server could crash while some delayed durable records were in memory but not yet hardened to disk. My recommendation to our clients is this one: think of what data you would want to have MOST of it, but would be fine if you lost SOME of it.
I’ve found that many clients do have data that fits this description and are able to get the performance boost from using DD. For example, logging tables (logging not auditing!), sensor data, ETL pipeline tables, etc.
How to enable it?
Enabling DD is fairly easy, step #1 is to enable it on the database level:
USE [master] GO ALTER DATABASE [HR] SET DELAYED_DURABILITY = ALLOWED; GO
Note that there actually three values: DISABLED, ALLOWED and FORCED:
DISABLED: no delayed durable transactions can happen in this database.
ALLOWED: delayed durable transactions can happen but only if explicitly requested in the code.
FORCED: the database will make all transactions delayed durable (not recommended unless you really know what you’re doing!).
The different behavior of each value when doing different types of transactions is fully described on Microsoft’s official documentation.
Once it’s enabled you can take advantage of it by simply mentioning it on the COMMIT instruction. See the example below.
Here’s a performance comparison I ran of two different sessions, running batches of 100 record inserts until 10 million records are inserted.
This is the fully durable SQL:
USE HR BEGIN TRANSACTION INSERT INTO ImportedData SELECT TOP 100 EmployeeId,Month,Year,TotalHours FROM HoursTracked; COMMIT TRANSACTION; GO 100000
This is the delayed durable SQL:
BEGIN TRANSACTION INSERT INTO ImportedData SELECT TOP 100 EmployeeId,Month,Year,TotalHours FROM HoursTracked; COMMIT TRANSACTION WITH(DELAYED_DURABILITY=ON); GO 100000
Here are the results of the two runs:
As you can see, the process using Delayed Durability took about 24 seconds less than the one that was fully durable. Again, the trade-off we’re making is the risk of losing data if SQL were to crash while some delayed durable data was still in memory and not hardened.
When the DBA is aware of the risk and takes into consideration the points that we have mentioned in this post, Delayed Durability can be a new powerful tool for the SQL 2014 toolbox.