Performance, Recovery, SQL Server

Delayed Durability in SQL Server 2014 – Part 3 – When should I use it?

In Part 1 of this series we introduced the concept of transaction Durability and its importance in modern relational databases. We also saw that for the first time in SQL Server 2014 Microsoft are going to allow us to control or at least influence transaction durability through its new Delayed Durability feature. In Part 2 we saw that taking advantage of the Delayed Durability feature may offer write-heavy workloads a substantial performance improvement.

In this final post I’m going to offer some brief thoughts on the questions you should ask when considering use of Delayed Durability.

Is my workload suitable?

This is the single most important question we need to answer when considering using Delayed Durability. All other factors are academic if you answer “no” to this question.

So when might a workload be suitable? The short answer to this is that your workload is suitable if you can tolerate data loss. So perhaps the question here should be, when might I be able to tolerate data loss?

When data can be reconstituted from other sources (easily)

It is not uncommon to have data being transferred between systems in a way that is somewhat repeatable. This might be some sort of import into a reporting system or overnight download of transactions. I’ve highlighted the word easily above because just because data can be reconstituted theoretically does not mean it can be done in practice. You don’t want to be figuring out the distance between theory and practice whilst you are trying to recover from some sort of data loss event!

That said, if the mechanism for reconstituting the data is relatively simple , rapid and well understood you may well be able to cope with data loss.

So when else might you be able to tolerate data loss?

When losing data in one system does not automatically imply corruption in another

When considering whether you can deal with data loss you need to think very carefully about how your system interacts with other related systems. Many modern IT systems involve multiple co-dependent databases.

If you consider a web application that is supported by 3 separate databases, is it acceptable for a transaction to go missing in one of them, but not the other two? It’s certainly possible, but you’re going to want to see the small print on that one. The devil is going to be in the detail.

Is Delayed Durability really the only option I have?

It takes a lot to saturate a well tuned database running on decent hardware. In fact, I have only ever come across 2 systems that were being clogged up by write traffic.  It is much more common to see systems struggling with read performance. In the first case the issue was a single poorly structured stored procedure causing system wide havoc. In the second case the workload was actually quite modest – it was just being run against vastly underpowered hardware (think 4GB of RAM for a 130GB database with data, logs and OS all on the C drive!).

The point is – good database design and appropriately specified hardware can easily get you into the realms of tens of thousands of transactions per second. Because Delayed Durability implies data loss it should probably be well down your list of things to look into if you need to squeeze some more performance out of a system.

Bottom Line

Enabling Delayed Durability is a little bit like disabling the air bags in your car. For 99% of us that would be a pretty stupid and thankfully unnecessary thing to do. For the remaining 1% of systems that really need the performance we need to think carefully about how we would deal with the implied data loss in practice. We would also want to make sure that we can’t meet our performance requirements through design changes or more appropriate hardware – after all, why settle for data loss if we don’t need to?

The addition of Delayed Durability in SQL Server 2014 is a welcome one. It brings SQL Server more into line with both Oracle and MySQL in terms of giving the DBA/Developer the flexibility to control how strictly SQL Server enforces the Durability aspect of ACID compliance.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s