Blog - comments

Excellent article. Thanks for sharing.

Satya

satya
Hi Helmy, If you use the GUI to create the availability group, it requires you have the same path / ...

could you please share the needed class files (or the D2.jar) it seems, this class is missing

me
but when i am gonna create an AG the following error shown >> the following folder locations do not ...
Helmy Mohamed
Nice one! I like the outfit of the characters. Wish i could do the same thing too but im not that te...
utah
Blog Stephane Haby Delayed Transaction Durability: A new feature in SQL Server 2014 CTP2

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
Posted by on in Development & Performance

Delayed Transaction Durability: A new feature in SQL Server 2014 CTP2

Delayed Transaction Durability is a new SQL Server 2014 feature that was not included in SQl Server 2014 CTP1 but discreetly published in CTP2. It is a very interesting functionality that helps reducing the IO contention for transaction log writes.

In other databases engine, you will find equivalents such as:  

  • Oracle: 'COMMIT WRITE BATCH WAIT|NOWAIT' (link here)
  • MySQL: 'group_commit' since version 4.x (link here)
  • PostgreSQL: 'group_commit' introduce in version 9.2 (link here)

 

What is it for?

It is very simple: 'Delayed Transaction Durability' helps reducing the IO contention for transaction log writes. Transaction commits are asynchronous. The most important advantage is to release faster locks, but you have to consider that data can be lost (Innocent)...

 

Full or Delayed Transaction Durability

What kind of Transaction Durability should you choose - full or delayed? This is not an easy topic:

Transaction durability

Explanation

Advantage

Disadvantage

Full

transactions are written in the transaction log to disk before returning control to the client

No data loss

IO contention during log writes

Delayed

transactions are written in the transaction log to disk after returning control to the client

reduces IO contention

Data loss possible

The best way to apply Transaction Durability maybe consists in mixing both types...

This feature is not available for In-Memory OLTP transaction but only for regular transaction.

 

How to use it?

This option is available in 3 control levels:

  • Database
  • Atomic block in Natively Compiled Procedure
  • COMMIT command option in T-SQL

 

Database level control

You can set this option with SSMS GUI or directly by script.

 

GUI: Right-click on database > properties > options -:  

Delayed-durability.png

 

Script:

ALTER DATABASESET DELAYED_DURABILITY = {DISABLED (default) | ALLOWED | FORCED}

 

To insure backward compatibility, this option is set on DISABLED by default.

 

Three setting options are available:  

  • DISABLED: feature cannot be used
  • ALLOWED: each transaction controls its durability (default is OFF for Atomic block in Natively Compiled Procedure and COMMIT command option in T-SQL)  
  • FORCED: every transaction is under delayed durability (might be dangerous)

 

Atomic block in Natively Compiled Procedure level control

In the creation of the stored procedure, it is just the option DELAYED_DURABILITY with ON or OFF like this sample:

 

CREATE PROCEDUREWITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERASBEGIN ATOMICWITH(    DELAYED_DURABILITY = ON,    TRANSACTION ISOLATION LEVEL = SNAPSHOT, ....) END

 

COMMIT command option in T-SQL level control

In the same way as for the Atomic Block in Natively Compiled Procedure, the COMMIT command has the option to set DELAYED_DURABILITY ON or OFF.

 

Conclusion

Delayed Transaction Durability is a new feature in SQL Server 2014, but given the feedback from my colleagues concerning other database systems (thanks a lot!), I would advise to use it with caution.

Data loss is a risk for many applications and the data consistency is not granted, but in most case, you should have no problem.

A good example is a (very, very good) application inserting a lot of data and for each INSERT line a COMMIT is done...

Don't smile, it's a common use case!

It is very practical in this case to have this option available in order to insure a fast workload.

I hope this will help you find the right answers and encourage you evaluating SQL Server 2014 CPT2 in order to resolve IO contention problems. SQL Server 2014 CTP2 is downloadable here.

For more information on Delayed Transaction Durability, look up the msdn website here.

Rate this blog entry:
2

Stéphane Haby is Delivery Manager and Senior Consultant at dbi Services. He has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. Stéphane Haby is Microsoft Certified Solutions Associate MCSA) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. He is also ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals & Pharmaceuticals, Banking / Financial Services, and many other industries.


MCSA  MCSE  mvp

Comments

  • Guest
    James Day Sunday, 16 February 2014

    Delayed Transaction Durability is not what concurrent commit in MySQL delivers. MySQL concurrent commit is durable at the time the system returns a result to the clients, subject to the value used for the innodb_flush_log_at_trx_commit setting.

    The equivalent of Delayed Transaction Durability is innodb_flush_log_at_trx_commit, which has three options:

    0: buffered within MySQL, but it'll try to flush about once a second.
    1: an fsync with each commit (or batch of commits when concurrent commits are happening). Safe against power loss and lesser failures. ACID. Default.
    2: flush to OS with each commit, so crash-safe for MySQL but not power loss or OS crash.

    1 is what's needed for ACID, 2 is a good compromise where power outages are believed to be unlikely. 1 with battery-backed write caching controller is the preferred way to get both durability and speed.

    James Day, MySQL Senior Principal Support Engineer, Oracle

Leave your comment

Guest Tuesday, 02 September 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter