Tag Archives: Memory Optimized Tables

Why you should upgrade to SQL Server 2014:

I shared my blog with my brother Zulfi Haja, he is a SQL Server Consultant too and works for an investment bank in London,UK. Having reviewed the top 5 features of SQL Server 2014, He shot me an email saying, Bro the effort you put on summarizing the top 5 features was worth reading and it’s a neat approach, would definitely recommend it to my colleagues, but I am still not convinced for an upgrade to SQL Server 2014. Can you help?

I thought only a Tech Evangelist from Microsoft will be able to answer him. I took the question from him with a little hesitation and replied back saying, “I don’t know how long will it take but surely will get back to you once I find a convincing answer”. He said, All the best!!

Was looking out for users experience with Enterprise 2014 version across my network circle and over the blogs, it was of no use. In the beginning of May I joined a local SQL Server User group and they invited me for their monthly meeting. To my surprise there was a presentation about In Memory OLTP and SQL Server 2014 by a Product Specialist from Microsoft. I said to myself, I found the guy and this Product Specialist and Tech Evangelist should be the one to answer my question. His presentation was very convincing enough and I found the answer from his talk. After the presentation the Q&A session was open and I asked him the same question and he gave me the expected answer from presentation. It is SQL Server 2014 can now support Tier1 application.

Let me elaborate this, if you have hanged out with a developer crowd from different domains they have this pre destined notion with them where they will give you an analogy, Oracle is like a business class of a flight and major critical applications are built using it and SQL Server is like an economy class where middle tiered companies uses it. In fact, of all my projects 60-70% of them will be the front end applications supported by Oracle and we had the data replicated to us in SQL Server environments for our reporting and analysis purposes. With SQL Server 2014 that pre destined notion is no more and as per my conversation with the Tech Evangelist, SQL Server 2014 will be good enough to support Tier1 applications and compete with Oracle supported systems because of its new DB engine design. Also he hinted out saying, if NASDAQ uses SQL Server why not your applications?

So Oracle folks better be aware of it :)

#JunaithHaja

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