Blog - comments

Hi Greg,

Thanks

great job Dave ! thanks

greg

Nice writeup

yup
using System; using System.Diagnostics; namespace ConsoleApplication1 { class Program { ...
praveen rathore
sql server is one of the best for utilize the data recovery. and its very informative for all the us...
Server instalattion
Blog Stéphane Savorgnano SQL Server 2014: In-memory OLTP project "Hekaton"

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

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

b2ap3_thumbnail_blog_hekaton1.jpg

  • 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:

b2ap3_thumbnail_blog_hekaton2.jpg

  • 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.

Rate this blog entry:
1

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 Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server implementation and maintenance. 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.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Saturday, 20 September 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter