SQL Server Performance Tuning

Improve Your SQL Server Transaction Log Performance Using Delayed Durability

Mark Varnas
No comments

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

Article by
Mark Varnas
Founder | CEO | SQL Veteran
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.

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials