Category Archives: SQL SERVER 2014

Delayed Transaction Durability In SQL Server 2014

Microsoft has introduced a new feature called Delayed Transaction Durability with its SQL Server 2014 version which aims to reduce the delays happening at the transaction level and to facilitate a full time availability of the database to the front end client application. By default, the transactions in SQL Server are durable which means the committed transactions will remain in the system even after a system failure. With its 2014 version Microsoft has introduced two types of durability in its transactions as follows,

1. Full Transaction Durability and 2. Delayed Transaction Durability.

Full Transaction Durability:

Consider a data entry user using a front end application built by ASP.Net backed by a SQL Server database. When the data entry operator enters 100 records the data gets written into the log file of the database and when he hits submit button from the Client application, the 100 records are moved from the log file into the data file. During this process the user will not get the control of client application until write process from log file to data file gets completed and he wouldn’t be able to process/enter any data at front end level (This corresponds to the processing screen which appears when we submit data over a website – annoying right?).Once the write process is completed, the log file becomes available and the user gets availability of the Front End and continues with his work. This process is called Full Transaction Durability. On the pros side there are no data loss and on the cons side there will be a significant latency at the client side application.

Delayed Transaction Durability:

In this method a buffer is used at the log file and the data is sent periodically to the data file whenever the buffer gets filled up. Imagine if the buffer has the capacity to hold up to 25 records flushes the data to a data file once it gets filled up. Flipping the above said data entry process for this method, the data gets written into the data file when the user enters the 26th record and when he enters the 100th record and hits submit, 75 records were already moved to the data file and only 25 records needs to be moved which will be taken care by the buffer flush and the client application is readily available to the user when the write happens which mean a no wait time. This process is called Delayed Transaction Durability. The latency at client side is significantly reduced and user is given an all-time availability of the client application. The DBA has to configure the database to handle full durable/delayed durable transactions.

Couple of month’s back one of my colleague had an issue of the log file getting filled up at a rapid pace and the nightly jobs were getting failed. I hope configuring the data base with Delayed Transaction Durability will enable periodic flushing of data from log file to data file which will prevent the log file from getting filled up. I am going to recommend this to my colleague. Hope you all will:-)

#JunaithHaja

Inline Specification of Indexes

We know from our experience with previous versions of SQL Server’s that there are two ways to create an index in a table.

One is to Right click the table at Object Explorer level and create it.

Second is to explicitly write a T-SQL statement starting with Create Index on Table name after the create table statement.

There is a single pitfall in the above two methods, the index cannot be created simultaneously within table definition like a Primary key or Foreign key constraint. SQL Server 2014 overcomes this draw back by facilitating to create an index within the Create table definition which is referred to as Inline Specification for Index creation.

Hence the index could be defined as below within the Create table statement,

CREATE TABLE DBO.FRUITSHOP (

ITEMNAME VARCHAR (25) NULL,

QUANTITY INT NOT NULL,

UNITPRICE FLOAT NOT NULL,

INDEX IX_RATE NONCLUSTERED (ITEMNAME, QUANTITY)

The above code will create a Non Clustered Index named IX_RATE and can be checked from SYS.INDEXES table by using the below query.

SELECT * FROM SYS.INDEXES WHERE NAME=‘IX_RATE’

Create Index by Inline Specification in SQL Server 2014Create Index by Inline Specification in SQL Server 214

Create Index by Inline Specification in SQL Server 2014Create Index by Inline Specification in SQL Server 2014

Create Index by Inline Specification in SQL Server 2014However the  above create table code will fail in SQL Server 2012 and earlier versions with the following error.

Msg 1018, Level 15, State 1, Line 7

Incorrect syntax near ‘INDEX’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Hence 2014 makes our coding easier. The Inline specification can be extended for Clustered and Non Clustered Indexes with varying number of columns as needed and can be found in MSDN site.

Hope it helps!

In Memory OLTP

I would say the top notch feature of SQL Server 2014 is In Memory OLTP concept. Do you know how we view an image or video stored in our hard disk? The image to be viewed is brought to the Main Memory from the hard disk through the software service and it helps us to see the image. The same happens in case of SQL Server too. Generally, the data stored in SQL Server is in the form of data tables called disk based tables (The disk based tables are our regular tables which we were using from 2000 till now). Consider if we run a query against the disk based tables using the SQL Server Management Studio, the query gets executed and the result set is produced to us through the main memory. In other words, the result set lies in the memory as compared to the original data from a disk based table stored in the secondary storage device. Technically there is no storage happens at main memory level. Hope this reminds us all of a concept we studied in our school days that Main memory is meant for faster processing and secondary storage is for storing the data, am glad it didn’t change. One obvious question hits our mind now; with all the advancement in DB applications is it possible to store data in main memory? Yes we can. It’s called Memory Optimized tables, a new feature built by Microsoft for SQL Server 2014 version

Hence we define Memory Optimized tables as the tables which store the data in the memory all time. Microsoft has introduced a new In Memory OLTP engine to work with Memory optimized tables. One awesome feature of memory optimized tables is there can be no locks issued to a memory optimized table which ensures no waiting because of blocking and data is available at all time. Having the data staged in the memory and no blocking of data through any locks significantly ensures faster processing and easier accessing of data by applications.

Did I not realize one thing that data in a memory will be lost in the event of power failure, so what happens if the server gets shut down or a system crash happens? Yes of course the data stored in the memory will be lost but it can be recovered when the server is up and running through Checkpoints which keeps a local backup of the data in memory optimized table as a file group in the hard disk. Memory optimized table does comes with lot of pros and few cons which I wouldn’t be able to summarize in a single post. I would recommend you to go through the White Paper Microsoft released about In Memory OLTP over this weekend and remember to utilize this feature when we get to work with 2014.Here is the link for the White paper.

I would like to finish up this post with the syntax to create a Memory Optimized table as below,

Create Table TableName(

Column1 Datatype,

Column2 Datatype,

Column3 Datatype

) (WITH MEMORY_OPTIMIZED=ON)

                                                                                                                      -Junaith Haja               

Top 5 features of SQL Server 2014:

Microsoft has officially launched its SQL SERVER 2014 this April at an event held in San Francisco, CA. Microsoft adds some new features and updates some of its existing features in every other SQL Server version. Sooner or later all our systems will be updated with this version.

So what does it means to a developer?

Is it a good sign or bad sign to a developer?

Did Microsoft made any ground breaking changes to its product?

Should I have to reinvigorate my T-SQL skills to work with 2014?

A Developer mind is bombarded with so many questions like above when we hear about a version upgrade. Don’t worry folks, I have got your back. I was excited enough like every other developer to get the 2014 version within first few weeks of its launch and done my review of it. I did learn a lot and my goal is to share some of its valuable insights a SQL Server developer must know to work with 2014. I will be posting my top 5 favorite features of SQL Server 2014 as a series over next few days. Please keep tuned.

Cheers!!

-Junaith Haja