Chapter 4. Delayed Durability

In the previous chapter, we looked at memory-optimized tables, what you need to do to create them, and how they can help improve performance. In this chapter, we will have a look at another new feature in SQL Server 2014 that can also improve database performance, a feature called Delayed Durability. This makes use of an in-memory transaction log feature, which delays writing transaction log entries to disk.

Database theory suggests that relational database management systems and their transactions should be atomic, consistent, independent, and durable; often referred to as ACID. SQL Server makes transactions durable by writing transactions to its transaction log. If you suffer a bottleneck after writing a transaction to the log, your system can suffer from poor performance. SQL Server 2014 introduces the concept of Delayed Durability, which offers you (the database administrator) some control on how SQL Server writes transactions to the transaction log. However...

Understanding the full transaction durability

Fully durable transactions mean that SQL Server will wait for a commit to be reported and the transaction to be written to the transaction log before returning control to the client. While it is waiting for the commit to be reported, SQL Server will block other transactions and sessions from accessing the resources it is updating.

Fully durable transactions will harden or write the transaction to the transaction log before returning control to the client. This is the default behavior in SQL Server. You should use this setting if you cannot tolerate data loss or poor performance in your system, which is not caused by write latency on the transaction log.

A fully durable transaction guarantees that once a commit succeeds, data changes made by the transaction are visible to other transactions in the system and durability is guaranteed on commit. The transaction is written to the log before control is returned to the client, which means that in the...

Understanding the delayed transaction durability

Delayed transaction durability can reduce the waiting time caused by I/O throughput on the database transaction log. With Delayed Durability the transaction log records are stored in memory and written to the disk in batches, which in turn means shorter I/O wait times. The process is sometimes referred to as a lazy commit.

Delayed transaction durability uses a process of asynchronous log writes to disk. The transaction log records are stored in memory and are written to the log (hardened) when the buffer is full or a buffer flushing event happens. This process can reduce latency or wait time in the system along with reducing contention and the possibility of blocking in the system.

With Delayed Durability, the commit process does not wait for the transaction to be written to the transaction log (or hardened) before returning control to the client. Also, concurrent transactions are less likely to compete for log I/O. Instead, the transaction...

Full Durability versus Delayed Durability

There are some factors to consider when deciding on whether to use Full or Delayed Durability. These include:

• Data loss: If you can accept some data loss and the performance of the system is more important than the recoverability of certain transactions, then Delayed Durability could be an option for you.

• Bottleneck on transaction log writes: After careful analysis of your system wait stats, you concluded that you are suffering from performance issues. This could be due to latency when writing to the transaction log, and you can accept some data loss in the event of a system crash or restart. In which case, Delayed Durability could be an option to solve this performance issue.

• A high contention workload: If your wait stat analysis shows that your performance slowness is caused by the delay in locks being released, then Delayed Durability could be an option. It helps remove the contention as it reduces the commit time and as such locks will be released...

Using delayed transaction durability

You, as the database administrator, can control Delayed Durability at the database level and can set it using an ALTER database statement as follows:

USE [master]
GO
ALTER DATABASE [DB1] SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT
GO

This code changes the database option for Delayed Durability on the DB1 database from Disabled to Allowed.

You can also set this using Management Studio. Right-click on the database whose Delayed Durability settings need to be changed, and choose Properties, and then choose the Options page. You will see a list of options; scroll down until you come to the Delayed Durability option. There you will find a drop-down list with three options: Disabled, Allowed, and Forced. Choose the new value for the option and click on OK as seen in the following screenshot:

There are three options available to the DBA when setting the option for Delayed Durability:

• Disabled: This is the default setting. All transactions will be fully durable...

Delayed Durability and transaction log flush

Delayed Durability means that it is possible that there will be transactions that have been reported as committed, and yet aren't written or hardened to the database transaction log. You know this and should be prepared to accept the potential data loss should the system crash or failover. But what if you need to take the system down for some reason? You wouldn't want to lose data as a result of planned outage. So, if your database is using Delayed Durability, you need some way of manually flushing the log to the disk. With SQL Server 2014, there are two ways to do this:

• You can execute the system-stored procedure called sp_flush_log. This will flush all committed transactions that are using the Delayed Durability option to the disk. To force a full flush of the in-memory log to the disk, execute the command exec sp_flush_log.

You can also execute a fully durable transaction that makes changes to the same database. This will also force a flush of...

Delayed Durability and potential data loss

In this section, you will discover how it is possible for you to lose data contained in a committed transaction using Delayed Durability.

The following script will create a database called DB1 on a SQL Server 2014 instance:

--Create database DB1
CREATE DATABASE [DB1]
GO

Then you will create a table in the DB1 database called t1. It will be a simple table with two columns called ID and Name:

USE DB1
GO
CREATE TABLE t1 (
  ID int,
  Name Varchar(20))

The following script inserts two rows into the table t1:

USE [DB1]
GO

INSERT INTO [dbo].[t1]
           ([ID]
           ,[Name])
     VALUES
           (1,'Seth'),
           (2,'Jake')
GO

The following script will change the database level Delayed Durability option to Forced. Therefore, all transactions will use Delayed Durability:

USE [master]
GO
ALTER DATABASE [DB1] SET DELAYED_DURABILITY = FORCED WITH NO_WAIT
GO

We will then set up a performance monitor and configure it to monitor log flushes. I am going...

Delayed Durability and other SQL Server components

How does Delayed Durability integrate with the components of SQL Server? We will have a brief discussion here on how Delayed Durability will work with some other components of SQL Server.

Let's start with the most important: crash recovery. You, as the DBA, have no control over crash recovery. Crash recovery is the process that brings the database into a consistent state after the system has crashed for whatever reason. It is possible that changed data pages have not been written or hardened to the data file. In previous versions of SQL Server, they would have been written and hardened to the log-file as part of the commit process. With Delayed Durability in SQL Server 2014, its possible, using asynchronous commit, that transactions have not been hardened to the transaction log on disk and as such these transactions will be lost even though a commit was issued. The database will still be in a consistent state but potentially data will be...

Summary

In this chapter, we have looked at Delayed Durability and how it can improve performance. It is important that when making use of this new feature in SQL Server 2014, you consider the potential data loss. Delayed Durability is something you should consider using if you have an I/O bottleneck or other related performance issues that Delayed Durability fixes. Always consider the potential for data loss and if that risk is acceptable to you. If the small amount of data loss that comes with Delayed Durability is not a problem for you, then this can be a great way of improving the performance of your databases. If, however, data loss, however small, is not acceptable to you, then this feature is not for you, regardless of the performance enhancement you can get from using it. In the next chapter, we will move on to the new high availability features and enhancements in AlwaysOn Availability Groups.