Delayed durability was announced late in the SQL Server 2014 development cycle, but offers something that many SQL Server professionals have wanted for years—the ability to disable transaction logging.
Why turn off the transaction log? It is the main guarantor of transactional consistency in SQL Server and is critical for highly recoverable applications, after all. That answer, in itself, tells you about situations where you might like to turn off the transaction log. That is, you can accelerate performance in a lot of situations where you do NOT need transactional consistency.
Default Transaction Log Behavior Compared to Delayed Durability
Here’s how SQL Server’s transaction log works by default. It uses a write-ahead log (WAL) algorithm, which basically means that transactions must be recorded in the log before they are otherwise committed. (If you’re more familiar with Oracle, note that the SQL Server transaction log combines the functionality of Oracle’s archive and redo logs.) Because all transactional work in a SQL Server database is dependent on the speed and I/O throughput of the transaction log, it’s not uncommon to find instances of SQL Server that perform poorly because of I/O bottlenecks on the file system where the transaction log is located.
(Side note: This is also the reason why transaction logs sometimes grow a great deal on SQL Server, since the transaction log cannot be truncated past the last open and uncommitted transaction was started.)
Now with delayed durability, SQL Server will continue to write transactions as if the log records had been flushed to disk, when in fact they’ve been batched up for a deferred write to be handled in the background. SQL Server then assumes that the log flush will eventually happen and, unless you change the default, will push log records to the transaction log as the default 60KB log buffer chunk fills up. (You can change the log buffer chunk size at the database level, at the individual transaction level, or at the procedure level of In-Memory OLTP compiled procedures.)
There are also some transactions which SQL Server will treat as fully durable, no matter what the setting. Some examples of fully durable transactions on SQL Server 2014 include system transactions, cross-database transactions, and transactions involved with FileTable, Change Tracking, and Change Data Capture features.
When Delayed Durability Makes Sense
There are a number of scenarios where delayed durability makes a lot of sense. A good example might be when you have all-or-nothing ETL processes in which you attempt to load a lot of data or have many steps when loading the data and, if for any reason a step of the ETL process fails necessitating that you’ll completely throw out the entire load and start over.
In fact, any situation where transaction log I/O throughput is slowing you down and where you don’t mind the possibility of data loss is a good candidate for delayed durability. Another example that comes to mind readily is when users have a large number of calculations they need to run to process reports, and the calculations involve a lot of work in tempdb.
You can expect an especially noticeable bump in performance for OLTP workloads which are heavy on small, discreet DML statements. OLTP workloads which have a lot of large transactions might still see a performance benefit, but it will likely be much less noticeable. In either workload, overall WRITELOG wait stats are going to decline, sometimes a lot, sometimes a little. Your mileage may vary, so be sure to test extensively before implementing. Of course, this performance benefit is essentially at the complete loss of transactional durability, meaning if your server crashes you can recover only to your last full database backup.
Performance Benefit
Certain workloads running on SQL Server 2014 will definitely see a performance benefit. You can read an excellent article from SQL Server MVP Aaron Bertrand outlining the performance tradeoffs of delayed durability in a variety of scenarios at http://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014. (Full disclosure: Aaron and I are employed by the same company.) Aaron also provides a great many links to other resources that discuss the inner workings of the SQL Server transaction log, ways to tune it for maximum performance, and ways to monitor the transaction log.
Kevin Kline, a longtime Microsoft SQL Server MVP, is a founder and former president of PASS and the author of SQL in a Nutshell. Kline tweets at @kekline and blogs at http://kevinekline.com.
Follow Kevin Kline on Twitter and Google.