Let me start this new blog post asking you a question: Which is your general approach when a new version of SQL Server is announced? Well, I normally look at (few) white-papers and overview videos on new features and functionalities, but at the very end, I start looking at the BooksOnLine documentation, items by item, entry by entry, to discover hidden gems in the new version:
Books Online for SQL Server 2014
I made this also this time for SQL Server 2014, and my patience was successful even in this case, as always happened since SQL Server 6.5 many years ago, and finally discovered something new that, at least for OLTP RBMS purists like me, it’s really astonishing, that is “Delayed Transaction Durability”. If you try to make an educated guess, you will probably realize what is it and YES…. I can confirm you that it is the possibility to relax/remove/bypass one of the most ancient rule, principle or dogma, if you like the term, since SQL Server first version has born, that is synchronous commit of transactions.
Just to set a common start point for everyone, when an application commits a transaction on SQL Server, transaction log records containing the data modifications must be hardened on disk (stable media) before SQL Server can return the acknowledgement to the application that the transaction is committed. Synchronous commit to stable media will ensure that, in case of SQL Server crash/failure, every transaction confirmed back to the user/application before the crash, will be “replayed” in the database and then survived to the unexpected event. The key word in the period above is “synchronous”; remember it while reading the remaining part of this blog post.
Ok, now you have a general idea, then let me show you an example of a piece of code, in this case a stored procedure, that will not cause synchronous transaction commit:
Forget for a moment other statements and the fact that this is a Hekaton natively compiled stored procedure: what will happen here is that the log records for the transaction are written to the log buffers in memory, but they may not have been written to disk when the commit reports success to the client. This essentially means two things:
If you are a transactional RDBMS “purist” like me, you will probably consider, at least on a first glance, this feature totally unacceptable due to the data loss possibility: to be honest, after spending 15 years working on the biggest SQL Server OLTP systems in Italy, I changed my point of view, after working for a while on Azure and Big Data hybrid scenarios. The right way to consider this feature is not “one size fits all”, but using “Delayed Transaction Durability” only in appropriate scenarios where:
Ok, now let me go over the implementation details of “Delayed Transaction Durability” (DTD) in SQL Server 2014. First of all, despite the stored procedure I used as an example above, is not limited to Hekaton in-memory engine, a new database wide setting, called “DELAYED_DURABILITY”, has been introduced to governs this behavior:
You can check if DTD is enabled for your database using the query below or the “Options” tab of database property page in SQL Server Management Studio (see below):
Select [name], [database_id], [delayed_durability], [delayed_durability_desc]
Where (delayed_durability_desc = 'ALLOWED' OR delayed_durability_desc = 'FORCED')
The default value is “DISABLED” (fully durable transactions) to ensure compatibility with existing code and applications, “FORCED” is obvious as well since every transaction in the current database will obey and use DTD without any code modification, finally “ALLOWED” is the real Interesting option: with this enabled, you have granular control over which code blocks will be permitted to use this feature. Based on my early experience with some partners using this feature, only a small subset of tables (and code!) will have benefits by using DTD, then itmakes perfect sense to decide where to use it. Essentially, you have two ways to decide where to use DTD:
Create procedure proc_test2 @c1 int, @c2 varchar(100)
With native_compilation, schemabinding, execute as owner
Begin atomic with (transaction isolation level=snapshot, language='english', delayed_durability = on)
Insert into dbo.tblTest1 values (@c1, @c2)
Insert into dbo.tblTest1 values (2, 'Wello world!’)
Commit transaction with (delayed_durability = on)
Now that you have a clear (hopefully) understanding of this new feature and how is implemented in SQL Server 2014, you should ask yourself how is possible to control transaction log flushing to ensure that everything is hardened on disk: the answer is inside a new extended system stored procedure called “sp_flush_log” that will trigger all log buffers to be secured on stable media. Online documentation does not contain any interesting detail, except for this sentence:
If you choose to use delayed transaction durability because of the performance benefits, but you also want to have a guaranteed limit on the amount of data that is lost on server crash or failover, then execute sys.sp_flush_log on a regular schedule. For example, if you want to make sure you do not lose more than x seconds worth of data, you would execute sp_flush_log every x seconds.
I made some tests to see how much delay delayed transaction could introduce, but generally it’s very fast to harden log records on disk, even with a busy system, but if you want to be 100% sure about no data loss you have to obey to the above official recommendation. During my tests, I also discovered a couple of interesting facts:
Finally, a consideration related to interoperability with other SQL Server features. I did not test on my own, but based on the BooksOnLine documentation I reviewed, the following considerations apply:
At the end, I consider this a nice new feature that may be useful in several but specific scenarios, including SQL Server 2014 in Azure IaaS where you may easily hit transaction log performance bottlenecks; be also aware that several open-source database engines, including MySQL, include this feature as a customizable behavior to accommodate similar scenarios.
That’s all folks… Best regards.