A week ago I wrote an article about Delayed Durability feature in SQL Server 2014. I was quite excited about it as the idea of relaxing ACID properties was something new to me. Potential performance gain was also very tempting. A thought immediately came to my mind that it would be beneficial to execute a performance test to check how much we can gain by using this feature.

This article describes what performance test I executed and its result. The whole test is documented on a YouTube video. Enjoy!

Of course, no matter whether you have already read my previous article on dba-presents.com about the Delayed Durability feature or not, I have to say it once again –

Delayed Durability is not for every system. It can cause data loss in some scenarios.

I don’t want my bank to have a mess in their databases and lose money, MY MONEY. But if you work with a system which is one of those rare cases where the Delayed Durability can be applied, stay calm, relax and enjoy this performance test.

Performance test scenario

I have a database on SQL Server 2014 and a table in it, called test. Here are two scripts that I am going to run for the test.

Fully durable transaction:

begin tran;
insert into dbo.test (id, name) values (next value for seq_test, 'test test test, absolute test');
insert into dbo.test (id, name) values (next value for seq_test, 'another sample name');
insert into dbo.test (id, name) values (next value for seq_test, 'third not so long text');
commit;

Delayed durable transaction:

begin tran;
insert into dbo.test (id, name) values (next value for seq_test, 'test test test, absolute test');
insert into dbo.test (id, name) values (next value for seq_test, 'another sample name');
insert into dbo.test (id, name) values (next value for seq_test, 'third not so long text');
commit with (DELAYED_DURABILITY = ON);

As you can see it inserts three rows into the table. All three statements are wrapped in a transaction.

The test will be performed with Apache JMeter. If you do not know this tool, don’t worry, this knowledge is not necessary to understand what is going on. It is just a tool to execute the SQL script and measure response times.

The test plan consists of one thread group which executes the script described a moment ago.

The script is run by 5 threads in parallel. Which means it simulates 5 users sitting in front of their computers and executing the script as fast as they can. The test ends when all 5 of them executes it 50 000 times. Summary Report will show us some statistics during and after the test.

If you have read my article on dba-presents.com, you should know that in fully durable transactions commit statement triggers Transaction Log flush operation. It is not a very resource consuming task but if there are a huge number of commits, it can become a bottleneck. The Delayed Durability feature makes Transaction Log flush detached from the commit statement. It executes, let’s say “aside”. It does not have to be just after the commit, it can be a second later when other commits have already been done. Then the log flush will be done once for many commits not one by one.

Knowing that, I want to also measure number of log flushes per second to see if there are any differences between tests with the Delayed Durability feature disabled and enabled. I will use Windows Performance Monitor for this.

I guess, there is nothing left and I can start.

Execution

The first test is executing the script with the feature disabled.

I am starting Windows Performance Monitor and jMeter.

When the test is done, Summary Report shows statistics. Throughput column shows a number of script executions per second - 3771.8/sec.

Now it is a turn for a test with the Delayed Durability feature enabled. To enable it, I am just adding a proper option to the commit statement. There are other possibilities also which you can check on my other article about Delayed Durability.

I am adding with (DELAYED_DURABILITY = ON) to the commit statement in the script and the test can be started.

When the second test is complete, both results can be analyzed together.

Test result

jMeter Summary Report:

You can see the first test – the one with the feature disabled, was noticable slower than the second one with the feature enabled. It is a huge gain in performance.

Windows Performance Monitor report:

Number of Transaction Log flushes per second is significantly lower in the second test. It is important to notice, the second test finished faster with smaller number of log flushes. It makes absolute sense with the Delayed Durability feature description – it does not trigger log flush after each commit so one log flush can cover multiple commit operations.

My outcome from the tests is simple – the Delayed Durability feature can significantly improve performance. I know my test is very artificial because it consisted of insert statements only but it proves the feature makes sense.

Before I finish, I need to tell it once again – this feature enabled can cause data loss so most of the systems cannot take advantage of it. If you think your system can, consider the risk again.