Manage Learn to apply best practices and optimize your operations.

Dive deep into SQL Server 2014 in-memory OLTP

Probably the biggest feature of the upcoming release, SQL Server 2014 in-memory OLTP has benefits that DBAs should know about. Learn how to set it up.

The SQL Server 2014 in-memory online transaction processing (OLTP) engine, previously code-named "Hekaton," allows you to create in-memory optimized OLTP tables within a conventional relational database. It is one of the key new performance-related architectural enhancements to SQL Server 2014, due out this year, although it will only be supported on 64-bit Enterprise, Developer and Evaluation editions.

Like traditional transactions on disk-based tables, the transactions on in-memory-optimized OLTP tables are fully atomic, consistent, isolated and durable (ACID). The in-memory OLTP engine solves problems in high-concurrency situations, as it uses data structures that are entirely latch-free (lock-free), meaning there are no latches or spinlocks on performance-critical paths in the system. Instead, it uses an optimistic multi-version concurrency control (MVCC) technique that provides transaction isolation semantics, which helps to avoid interference among transactions. Thus, any user process can access any row in a table without acquiring latches or locks.

The combination of MVCC and latch-free data structures results in a system in which the user processes can run without stalling or waiting. In addition, stored procedures operating on optimized memory tables, though written in Transact-SQL, are actually compiled to highly efficient machine code. This maximizes the runtime performance for certain workloads and certain types of queries, because the generated machine code only contains exactly what is needed to run the request, and nothing more. According to Microsoft, some applications can achieve a 50x performance increase simply by using the in-memory OTLP engine.

There are two main types of in-memory-optimized OLTP tables: SCHEMA_AND_DATA and SCHEMA_ONLY.  The first resides in memory when the schema and data persist after SQL Server crashes or restarts, and is useful as a transactional OLTP application where you wouldn't want to lose data and transactions after SQL Server crashes or restarts. The second, SCHEMA_ONLY, resides in memory when only the schema persists, and is useful for staging tables for your database application.

You can use the Memory Optimization Advisor wizard, which can be launched from SQL Server Management Studio, to help identify and migrate fully compatible tables in memory, and select the stored procedures that can be compiled into machine code for high-performance execution.

Creating memory-optimized tables

To create memory-optimized tables, the database must have a MEMORY_OPTIMIZED_DATA file group. To create a MEMORY_OPTIMIZED_DATA file group, run the following code:

creating a MEMORY_OPTIMIZED_DATA filegroup

Next, run the following code to add database files to the memory-optimized file group:

adding database files to the filegroup

Here is the general syntax to create a memory-optimized table:

syntax for creating memory-optimized tables

Here's an example of some code that creates an in-memory-optimized table inside AdventureWorks' 2012 sample database:

code for creating an in-memory-optimized table

In this code, the BUCKET_COUNT parameter is the number of buckets that should be created within the hash indexes. For more information in determining the correct bucket count for hash indexes, check here.

Here is the general syntax to create a natively compiled stored procedure:

syntax for creating a natively compiled stored procedure

The actual code that makes the stored procedure a natively compiled procedure is as follows:

code for creating a natively compiled stored procedure

For example, here is some code for a natively compiled stored procedure that I created to retrieve the order detail information from the Sales.SalesOrderDetail_MemoryOptimized table.

example of creating an in-memory-optimized table

Limitations of memory-optimized tables

Memory-optimized tables do not support the full set of SQL Server and Transact-SQL features that are supported on traditional disk-based tables. Some of the key limitations of memory-optimized tables include no support for Sparse, IDENTITY and computed columns, DML triggers, FILESTREAM data, columnstore filtered and full-text indexes, ROWGUIDCOL option, FOREIGN KEY, CHECK and UNIQUE constraints, TRUNCATE TABLE, MERGE, and DYNAMIC and KEYSET cursors.

The following data types are not support by memory-optimized tables: Datetimeoffset, geography, geometry, LOBs (varchar(max), image, XML, text and ntext).  For a full list of SQL Server features that are not supported with memory-optimized tables, see here.

About the author:
Basit Farooq is a lead database administrator, trainer and technical author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning