As I’m downloading SQL Server 2014 CTP2 at this very moment, I’ve noticed new fascinating feature that hadn’t been announced in CTP1: delayed transaction durability. It means that if your system is heavy on writes and on another hand you can tolerate data loss on some rare occasions – you can consider declaring transaction as DELAYED_DURABILITY = ON. In this case transaction would be committed when log is written to some buffer in memory – not to disk as usual. This way transactions can become much faster – especially when log IO is the bottleneck. Delayed durability can smoothen write peaks. Looking forward to use it. But… unfortunately, this feature works for In-Memory OLTP only! Which greatly reduces its effectiveness. Prior to noticing this limitation, I’ve already imagined how I can use it on one of my heaviest procedures that generates tens and sometimes hundreds megabytes of log every time it runs. And no, it isn’t OLTP procedure, it works with BLOBs, so Hekaton isn’t an option. And I can always reload this data if something fails. So delayed durability could potentially reduce procedure duration by 30-40%. Potentially – in case Microsoft would some day implement it for really heavy writers and not only for In-Memory OLTP.
I guess, primary use of this feature is in extremely loaded environments with IO bottleneck on transaction log – in this case even millisecond or several microseconds reduced from transaction duration can prevent collision. And possible failure because as you probably already know, Hekaton treats conflicts in different way: instead of blocking, transaction can just fail on inconsistency.