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

Stored procedure taking long time to execute

This can happen due to many factors like lack of indexes, out of dates statistics, if it returns a large number of records, etc.

I observed a similar behavior recently in a production environment. The stored procedure had the code similar to the following;

    SELECT Col3   
    FROM dbo.Tab1  
    WHERE  Col1 = @Col1 
    AND Col2 = @Col2  
    AND RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(CAST(Comments AS VARCHAR(2000)),'<br />',''),CHAR(10),''),CHAR(13),''),'&nbsp;',''))) <> ''  

Let me give you some background information about this table.

• Col2 and Col3 are UNIQUEIDENTIFIER
  
• Comments column is ntext and does not have indexes
  
• All three columns Col1, Col2 and Col3 has indexes
  
• Col2 and the PK is the clustered index of the table
  
• Table has 825,452,256 records (825 million)

The FORMAT function

Recently one of colleagues asked, how to format a date value according to the style he wants. His date style is as below;

10/15/13 - 04:11 pm

The immediate solution that came into my mind was the CONVERT function which you can specify the conversion style. He had already worked out a solution which using that function and the code is as below;

SELECT LEFT(Lower(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 1) + ' - ' + RIGHT( 
       CONVERT ( 
       VARCHAR, CURRENT_TIMESTAMP, 100), 8)), 17) 
       + ' ' 
       + Lower(RIGHT(CONVERT (VARCHAR, CURRENT_TIMESTAMP, 100), 2)) AS 
       "Date/Time" 

However it is bit of complex string concatenation. Wouldn’t there be any simple way of doing it? So after bit of research I found the following code which does the exact same thing that my colleague wants.

DECLARE @d DATETIME = CURRENT_TIMESTAMP; 

SELECT Lower(Format(@d, 'MM/dd/yy - hh:mm tt')) AS 'Date/Time' 

Using FORMAT function, you can format numeric and date values. It also supports the culture. The syntax is as below;

FORMAT ( value, format [, culture ] )

SSMS Limitations

When performing admin tasks or development tasks, some people like to use SQL Server Management Studio (SSMS) while some are like to use scripts. As many of you know, SSMS has limitations when it comes to perform some tasks. I found such instance while setting up replication for a testing purpose.

The requirement is simple, I wanted to setup a replication for a table. The uncommon factor here is, the publisher and subscriber databases are same. I used a AdventureWorks2008R2 as the database.

Identifying slow running queries

This post is for SQL Server Database Administrators who have find difficulty in some situations when identifying slow running queries. The DBAs who work in production environment specially might have faced situations where users or other production monitoring teams complaining that suddenly they see a slowness in their systems. The “slowness in the system” a very generic term. This could be due to various reasons. Such as;

• Network issue
• Application server issue
• Database issue

As a DBA, it is a responsibility to make sure everything is clear in database perspective. Before start troubleshooting the broader phenomena, it is necessary to ask few questions to shed some light into the dark area. Below questions might be useful;

SET XACT_ABORT Option and Transaction Behavior

One of my colleagues asked what if a transaction has only BEGIN TRAN and COMMIT TRAN without a ROLLBACK TRAN. I did not have a proper answer on top of my head however I could remember the behavior is depending on the SET OPTION known as XACT_ABORT.
I initially tried the below query to test the above mentioned situation. Note that I did not change the default setting of XACT_ABORT option. The default is set to OFF.

“Table Alias”, How it behaves?

One of my colleagues asked me a question about a simple T-SQL query which uses table alias. See the below T-SQL code;

USE AdventureWorks2012
GO

SELECT E.loginID,HumanResources.Employee.JobTitle FROM HumanResources.Employee E

The above query used a table alias “E” and in SELECT list one column refers with table alias while the other column referring  full table name.  Seems like technically correct query. However the query returned the following error.

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "HumanResources.Employee.JobTitle" could not be bound.

As per the error message, use of table name, “HumanResources.Employee” to refer JobTitle column is incorrect. When you remove HumanResources.Employee in the SELECT list then the query works fine.

The theory behind this is when you have a table alias, it logical rename the table to table alias. So if you want to refer the table in the query, it needs to use table alias instead of the actual table name.

Relational algebra explains this more clearly.

The relevant operator for table alias in relational algebra (RA) is RENAME. As SQL derived from RA it is always better to learn RA before learning SQL.