Persistent Log Buffer with SQL Server 2016 SP1

You can get significant performance gain by just moving the existing applications to SQL Server 2016. There are several case studies to prove this story. You can even get more performance gain (2X - 4X) with combination of SQL Server 2016 SP1 and Windows Server 2016 (not yet released). This performance gain achieved through Persistent (non-volatile) Log Buffer technique. This performance enhancement has been released to SQL Server with SP1. SQL Server utilizes Windows Server 2016 direct memory access (DAX). With this new feature SQL Server can write transaction log data in memory access speed over disk IO speed earlier. With the new type of memory which is persistent, the moments SQL Server writes to log cache (memory area for t-log), the data is persistent. 

For more details:

Transaction Commit latency acceleration using Storage Class Memory

SQL Server 2014 changes ACID behavior

Any database professional know what it means by ACID. They are some golden properties of relational systems.

A – Atomicity

C – Consistency

I  - Isolation

D – Durability

SQL Server supports ACID properties fully until SQL Server 2012.  In SQL Server 2014 (still in CTP level) DBA has the option to control the D of ACID. Till SQL Server 2012, the log records need to be flushed to the disk before a transaction can be declared committed. However in SQL Server 2014, this statement is not 100% valid with the introduction of new feature called “delayed durability”.

With this new option, SQL Server transactions can be either fully durable or delayed durable. BOL states;

“Fully durable transaction commits are synchronous and report a COMMIT as successful and return control to the client only after the log records for the transaction are written to disk. Delayed durable transaction commits are asynchronous and report a COMMIT as successful before the log records for the transaction are written to disk. Hardening transaction log entries to disk is required for a transaction to be durable. Delayed durable transactions become durable when the transaction log entries are flushed to disk.”

As it obvious thing, the delayed durability comes with a cost that, there could be a data loss in the event like fail over. The advantages of this option are, reduce contention and increase throughput.

Use of delayed durability option is not suitable for all the cases. This can not be used in situations where you cant tolerate a data loss.

Delayed durability option can be applied at Database level, Atomic code block level and Commit level.

It is worth to be considered why Microsoft changed the ACID behavior after 22 years of SQL Server history. I believe this is an response to the highly volatile database technology market which we are seeing today, specially with the widely use of NoSQL technologies. NoSQL technologies are using “BASE” (Basically Available, Soft state, Eventual Consistency) against the ACID in relation systems. With the introduction of BASE the NoSQL vendors claim that the support for big data and huge performance gain in OLTP as opposed to the relational databases.

Finally, I believe it is a great option and DBAs will like the new feature. However, SQL Server 2014 is still in CTP which means it is subjected to change in future releases, sometimes this could be completely removed.

For more details, How to: Control Transaction Durability

SQL Server 2012 Tutorials and Sample Databases

Microsoft SQL Server 2012 has released RTM recently. No doubt that you're interested to learn new features of the new version. If so then it is useful to have the tutorials and sample databases installed on your server. You can get them using below links;

Tutorials here

Sample Databases here

Note that sample databases are just data files (No log file). You need to attach it to your server to create the databases. Use the following T-SQL script.

CREATE DATABASE AdventureWorks2012

ON (FILENAME = 'D:\SQL Server\DATA\AdventureWorks2012_Data.mdf')

FOR ATTACH_REBUILD_LOG

Enjoy with the new version of SQL Server.

Multi-Subnet Clustering

This is a new feature in SQL Server 2012. However Windows 2008 has this feature. With the introduction of the new feature, SQL Server can have a cluster node in a different subnet. Subnet is a virtual grouping concept available in network. Subnet will enable network administrators to manage networks effectively over geographical areas. 


Since the subnet is a geographical network concept, now SQL Server clustering can be used as DR (Disaster Recovery) solution than ever before. 


More information can be found here.