Microsoft’s In-Memory OLTP: Why, What, and How

During the keynote address on day two of the 2013 Professional Association for SQL Server (PASS) Summit, David DeWitt, Technical Fellow at Microsoft, presented a deep dive into the why, what, and how of Microsoft’s In-Memory OLTP database (formerly code named Hekaton).

As DeWitt explained, the new In-Memory OLTP database engine is essentially the latest evolution in database engine technology. The evolutionary step taken with the In-Memory OLTP is largely driven by three factors in our changing computing landscape:

With these changes, it makes sense that the greatest performance benefits are going to be made by taking advantage of memory. If that’s the case, then why not just put the entire database in memory? Would that accomplish the same thing?

Related: In-Memory OLTP Engine FAQs

New Lockless Engine Design

DeWitt explained that just moving the database into memory would not solve the problem because of the way that the database engine guarantees data consistency and handles locking. The latches and locks that the database engine must use to maintain data consistency slowdown the amount of data and the number of threads that the engine can process. What was needed was an all-new lockless engine design and that's what Hekaton is—an all new database engine that sits inside a SQL Server instance exactly like the classic relational engine or the newer columnar store engine (formerly codenamed Apollo).

Inner Workings of OLTP Engine

So how does it work? The detailed inner workings of Hekaton are far beyond this column, but it's a topic that SQL Server Pro will cover in the near future. The primary defining characteristics of the new In-Memory OLTP engine are:

The new In-Memory OLTP database engine doesn’t use any locks. Instead, when a row in a shared buffer is modified, the engine makes an entirely new version of that row with timestamps. This process is very fast because it’s done entirely in memory. The engine then validates and updates the rows before committing them. A garbage collection process periodically cleans up all of the unneeded rows.

Performance Improvements

Interpreted SQL code is compiled into native code in order to reduce the number of instructions that the CPU must execute to process the query. You don’t have to compile code to run it against Hekaton tables, but doing so will make the queries significantly faster. In many cases, the In-Memory OLTP engine will provide a 10X-30X improvement in performance.

SQL Server 2014 will include wizards to help you know what tables to convert to the In-Memory OLTP engine and what stored procedures to compile.

Related: PASS Summit 2013 Keynote Day One