Improve Your SQL Server Transaction Log Performance Using Delayed Durability

What is delayed durability in SQL Server?

Delayed durability is a dangerous feature introduced in SQL Server 2014.

Also known as lazy commit, It is a storage optimization that returns a successful commit before transaction logs are saved to a drive.

In some cases, the feature can make a significant performance difference when correctly enabled – in other scenarios, it can cause data loss.

Should I enable it?

Although this can improve performance, the risk of data loss is higher because the transaction logs are saved asynchronously, only when the logs are flushed to a drive.

For a database in simple recovery mode, there is a minimal risk. You have already decided that some data loss is acceptable.

If you are running full recovery, you are more concerned about data loss. But how much is acceptable? For example, a job that flushes the log can potentially minimize that risk. The risk will keep existing, but it can be minimized for the right workload.

Note: Be careful If you are using a form of HA. Even on a graceful shutdown or planned cluster failover, data loss can happen.

How can I enable it?

When using the ALTER DATABASE statement to enable at the Database level, you have three different options with the SET DELAYED_DURABILITY:

1. Disabled

• Default setting, similar to full transaction durability.

2. Allowed

• Allow each transaction to decide the delayed durability.
• All the transactions’ durability will be based on the transaction level settings.
• This option just enabled the capability of the database to work with transactions that have delayed durability.

When you allow it, you can specify the transaction level durability on the commit statement like the example below:

COMMIT TRANSACTION TransactionName
WITH (DELAYED_DURABILITY = ON)

3. Forced

• Forces each transaction to follow this feature.

You can run the statement below to force your transactions to follow delayed durability (replace the [DatabaseName] to your database):

USE [master] GO

ALTER DATABASE [DatabaseName]

SET DELAYED_DURABILITY = FORCED GO

You can also change these values using SSMS

1. Expand Databases, and right-click one.
2. Go to the Options page and change the Delayed Durability option.

More information

• Microsoft – Control Transaction Durability
• Reduce SQL Server WRITELOG waits using Delayed Durability, Chandresh Patel – MSSQLTips
• Delayed Durability in SQL Server 2014, Aaron Bertrand – SQLPerformance