Monday, March 31, 2014

The In-Memory OLTP Engine in SQL Server 2014: An Overview

A few days ago, on March 21st, I participated as an invited speaker at Microsoft Conference 2014 that took place in Nicosia, Cyprus.

My session was on SQL Server 2014 and specifically about what’s new in this release of SQL Server.

My session’s agenda was on the following areas of new features on SQL Server:
  • Interaction with Windows Azure
  • Security Enhancements
  • Performance Improvements & Enhancements
  • T-SQL Enhancements
  • In-Memory OLTP Engine
Someone might wonder why the “In-Memory OLTP Engine” is in bold, red letters :)

The reason is that this feature is the long-awaited in-memory processing engine that it is built-in into the SQL Server Engine and when you properly use it, you can get extremely high speedups in data processing when compared to the “traditional processing”.

The In-Memory OLTP Engine introduces new data structures described as “memory-optimized” (i.e. memory-optimized tables and table variables). 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) when it is created
  • 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.

Feel free to view/download my presentation below:



This was the introductory of a series of posts, screencasts and webcasts having as a topic the In-Memory OLTP Engine of SQL Server 2014.

Last but not least: SQL Server 2014 will be generally available tomorrow, April 1st, 2014, so feel free to evaluate it and see the true power of the In-Memory OLTP Engine!

[Ads]
Check out my latest eBook on SQL Server:
Tuning SQL Server - Ebook

Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: