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.
For that, SQL Server 2014 will provide a new tool called: Transaction Performance Analysis Overview.

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 index
  bucket 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.