SQL Server 2014: In-memory OLTP project "Hekaton"

I have the chance to be during this week in Madrid to participate in the TechEd Europe 2013. I will give you a feedback of my best sessions along this week and I'm beginning with the SQL Server In-Memory OLTP project "Hekaton"! Microsoft has been working for four years on this new technology which will come with SQL Server 2014.

The goal of this new feature is to:

Memory optimized table and index structures

Native compilation of business logic in Stored Procedure

Get rid of Latch and Lock data structures

To be fully integrated into SQL Server

How do they achieve that

Avoiding latches... Pages are used to store data rows and in multi-threaded applications you need to ensure that the threads do not trip over each other, latches have been developed to perform the task of thread synchronisation. Once an operation is complete, the thread synchronisation objects (latches) will be released and the other threads will be able to access that page and memory structures again. But some time has been lost...

With In-memory technology table and index structures are memory optimized. This way, you get rid of pages, b-trees and other latches as the row is the atomic unit!

Of course, as it will not be possible to load all tables in the memory, you will have to choose the ones which are causing the most latching.

You will be able to select tables or Stored Procedure and this tool will analyse the workload and find contention. It will give you a chart showing which table should be moved to in-memory OLTP to obtain the maximun gain. The same applies for stored procedure if you choose to analyze Stored Procedures.

Once you have found the table which causes latches you will have to drop it and recreate it with some special attributes

table is memory optimized, need to be specified during the creation

table is durabe: schema and data are durable by default

nonclustered hash index: new type of indexbucket count: specify how many bucket will be in the hash table

secondary indexes are specified inline, you will not be able to specified them later because this table will be compile and become a DLL.

Hash indexes are a set of buckets (8-bytes memory pointers). SQL Server 2014 has a hash function to map values to buckets. It is built into the system.

This process will take place during the script execution:

Create table DLL

Code generation and compilation

Table DLL produced

Table DLL loaded in memory

For a Stored Procedure:

native compilation: this proc is natively compiled

schemabinding: native proc should be schema-bounds

execute as owner: execution contect is required: don't wan't to take care about permission each time this proc is running

begin atomic: atomic block: the procedure is a block, you don't need to take care about commit or rollback, it is done automaticaly

transaction level... : session settings are fixed at create time

Approximatively the same process will take place with Stored procedure:

Create table DLL

Query optimization

Code generation and compilation

Table DLL produced

Table DLL loaded in memory

Stored procedures are not anymore interpreted, but converted to a C program, compiled with a C compiler to create a DLL which will be directly running in machine code. This new kind of Stored Procedure is calling Native Stored Procedure.

Conclusion

For the moment there is some limitation with the first version of SQL Server 2014 CTP1:

row limitation to 8060 bites

no varchar(max) object

no DML Triggers

not possible with AlwaysOn(for CTP2)

But this is really an impressive feature that I will test quickly. The very good point is that you don't have to rewrite your application or change your hardware to see your performance increase 10- to 30-fold in the best scenario.

Stéphane Savorgnano is Consultant at dbi services. He has more than fifteen years of experience in Microsoft software development and in SQL Server database solutions. He is specialized in SQL Server installation, performance analysis, best practices, etc. Stéphane Savorgnano is Microsoft Certified Solutions Associate (MCSA) and Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012 as well as Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008. Prior to joining dbi services, he was software engineer at Ciba Specialty Chemicals in Basel. Stéphane Savorgnano holds a Master of Informatics from Mulhouse University (F). His branch-related experience covers Banking / Financial Services, Chemicals & Pharmaceuticals, etc.