Improve your SQL Server transaction log performance using Delayed Durability

What is the Delayed Durability in SQL Server?

This 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 – in some scenarios, it can cause data loss.

By the way, this check is a part of our SQL Server Health Check service.

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, so you are more concerned about data loss. But how much is acceptable? A job that flushes the log can potentially minimize that risk, for example. 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, the data loss can happen.

How can I enabled it?

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

Disabled

Default setting, similar to full transaction durability.

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 allowed, you can specify the transaction level durability on the commit statement like the example below:

  1. COMMIT TRANSACTION TransactionName WITH (DELAYED_DURABILITY = ON)

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):

  1. USE [master]
  2. GO
  3. ALTER DATABASE [DatabaseName] SET DELAYED_DURABILITY = FORCED
  4. 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.
Figure 1 – Delayed durability – Database Level.

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.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *