Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooing Skills

SQL 2014 Learning Series # 7–New Feature – Delayed Durability (Part 1)

Posted by blakhani on May 1, 2014


SQL Server 2014 is having a lot of useful feature and some are hidden as well. I was able to learn about “delayed durability” feature while preparing my session at GIDS. Since the session is over now, it’s right time to write about this feature. First, let’s understand durability.

In general, every transaction in relational database should follow ACID properties. Atomicity, Consistency, Isolation and Durability.

image

In SQL Server, durability is maintained by writing log record of modification to transaction log “synchronously”. If write to the disk is slow (or huge data to be written) the total time of transaction would become longer and we would see WRITELOG wait for the SPID. Once log record is hardened to transaction log, client would get commit confirmation.

image

SQL Server 2014 gives DBA and Application team to achieve durability with a delay. Delayed durability may give you some performance gain at the cost of durability factor of a transaction. It is achieved by writing the log record asynchronously. Log record is still generated and kept in memory area called “Log Cache”. Only difference is that it’s written to disk with the delay. So, even if application has received confirmation from SQL about the commit, it’s not actually hardened to disk. This is what is called as “delayed” durability.  If there is a unexpected failure of SQL before the data is written to disk, the data is essentially lost.

image

I must emphasize the fact that if your application can’t afford any data loss, don’t use this feature. (I don’t have option to put red flashing light otherwise I would have done that to stress on the data loss factor). Does this mean that you have no control about the flush of the log records from log cache to disk? No, product team has thought of this and provided a extended stored procedure called sp_flush_log. I would talk more about this later.

Now since you know the fundamentals of delayed durability, let’s see how to implement it. This can be controlled at various level. We can set delayed durability at Database Level, Transaction Level and in-memory stored procedure.

Database Level

We can change the setting using SSMS as below

image

Same options are provided in T-SQL as well.

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = DISABLED

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = ALLOWED

ALTER DATABASE [AdventureWorks2014]
SET DELAYED_DURABILITY = FORCED

If we set the value to disabled at database level, it would take highest priority. This means that transaction level setting would not be honored.

Transaction Commit Level

During commit of a transaction we can specify delayed durability option as ON or OFF

COMMIT TRAN [ transaction name | @transaction name variable ] [ WITH ( DELAYED_DURABILITY = { OFF | ON })]

Natively Compiled Stored Procedure Level

To know more about natively compiled procedure, I would like you to read here. While creating natively compiled procedure, we can specify delayed durability as below.

CREATE PROCEDURE SQLServerHelp
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH 
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
    …
)
END

Here is the quick chart which explains effect of settings done at database level and transaction level.

image

In next part, we would see delayed durability in action.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • About these ads

    4 Responses to “SQL 2014 Learning Series # 7–New Feature – Delayed Durability (Part 1)”

    1. manu said

      Nicely written post. Eager to read your next post on sp_flush_log.

    2. Frank said

      I was looking for this certain info for a very long time. Thank you and best of luck

    3. […] part 1 of the post about delayed durability, we have learned the basics of delayed durability. In this […]

    Leave a Reply

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

    WordPress.com Logo

    You are commenting using your WordPress.com 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 )

    Google+ photo

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

    Connecting to %s

     
    Follow

    Get every new post delivered to your Inbox.

    Join 779 other followers

    %d bloggers like this: