sqlskills-logo-2015-white.png

Upgrading SQL Server–Delayed Durability

One of the interesting new features that was added in SQL Server 2014 was delayed durability. This feature lets you trade some transaction durability for increased log write performance. This is possible because delayed durable transaction commits are asynchronous and report a commit as successful before the log records for the transaction are flushed and actually written to the disk.

The price you pay for this possible performance boost is the possibility of some data loss, as explained in more detail here by Paul Randal.

The main reason you might want to try this feature is if your workload and infrastructure actually has performance bottlenecks writing data to your transaction log file (which is not that common). This will be characterized by high WRITELOG and PAGEIOLATCH_xx wait types.

The first thing I would check and correct if necessary would be the virtual log file (VLF) count for the database. Having an extremely high VLF count can hurt write performance to the log file. Setting your auto growth increment to an appropriately large size will reduce the rate of growth of your VLFs.

Other things I would consider trying before I used delayed durability would be to make sure I understood the I/O performance characteristics on the LUN where my transaction log file was located, using a synthetic storage benchmark tool such as Microsoft DiskSpd. If necessary, I would explore whether it is possible to do anything to improve the write performance of than LUN.

If I had multiple databases that all had their transaction log files on the same disk, I would want to make sure the LUN where they were located had very good random write performance. It makes a lot of difference whether you have a small number of magnetic drives using a parity-based RAID level (such as RAID 5), or high performance flash-based storage.

Parity-based RAID levels have an unavoidable write penalty, since they have to write the parity information after they write the actual striped data. Hardware RAID controllers with fast multi-core processors and a large hardware cache, dedicated to writes can help mask this problem.

Keep in mind that not all flash storage is created equal. There are major performance differences between traditional SATA AHCI drives and newer PCIe NVMe drives, and also between read-optimized and write-optimized devices. Another important factor is the capacity of the flash device you are using. Larger capacity devices usually have much better write performance than smaller capacity devices from the same manufacturer and product line.

Aaron Bertand wrote a great post about delayed durability, including some good performance benchmarks here. Personally, I have seen major performance gains with delayed durability when running in an Azure VM that has relatively poor storage performance. You cannot use delayed durability if you are using transactional replication, change tracking, or change data capture (CDC).

 

Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.

Here is a link to the complete series about upgrading SQL Server.

Leave a Reply

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

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.