Introducing In-Memory Optimization in SQL Server

Introducing In-Memory Optimization in SQL Server

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
In-Memory OLTP (codenamed “Hekaton”) is the most significant feature shipped with SQL Server 2014. It is Microsoft’s new technology which is fully integrated into SQL Server’s Database Engine. In-Memory OLTP introduces new data structures for optimizing the performance of OLTP workloads. These data structures are called “memory-optimized” tables and along with natively compiled stored procedures, they offer a huge performance boost when it comes to processing large amounts of information, especially in data environments with high levels of concurrency. In certain cases, the execution time can be up to 50 times faster.

As the name of the technology implies, the In-Memory OLTP Engine is optimized for OLTP workloads. Some examples of best-suited workloads are:

  • Staging tables (i.e. DW)
  • High data insert rate (i.e. smart metering)
  • Low latency execution (i.e. investment trading)


When used in such scenarios, the performance gain of using the In-Memory OLTP Engine can be huge.

Memory-optimized tables store their data into memory using multiple versions of each row’s data. This technique is characterized as “Non-blocking multi-version optimistic concurrency control” and eliminates both Locks and Latches thus achieving breakthrough performance.

The list of main features of memory-optimized tables are:

  • Rows in the table are read from and written to memory
  • The entire table resides in memory
  • Non-blocking multi-version optimistic concurrency control
  • Durable & non-durable
  • A second copy maintained on disk for durability (if enabled)
  • Data in memory-optimized tables is only read from disk during database recovery
  • Interoperable with disk-based tables

Another feature of the In-Memory OLTP Engine is “natively compiled stored procedures”. A natively-compiled stored procedure is a SQL Server object that can access only memory-optimized data structures such as memory-optimized tables, table variables, etc. The main features of a natively-compiled stored procedure are:

  • It is compiled to native code (DLL) upon its creation (the interpreted stored procedures are compiled at first execution)
  • Aggressive optimizations take time at compile time
  • Can only interact with memory-optimized tables

The call to a natively compiled stored procedure is actually a DLL Entry Point which is something that offers very fast execution times!

When you properly combine memory-optimized tables with natively compiled stored procedures for heavy workloads like DW ETL processes, high data insert rate processes and processes that demand low latency execution you can expect some serious speedups.

Subsequent posts will dive into the In-Memory OLTP Engine of SQL Server 2014 and illustrate via different examples the benefits of using this technology.


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.