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 Database Engine. Like traditional transactions on disk-based tables, the transactions on in-memory-optimized OLTP tables are fully atomic, consistent, isolated, and durable. The in-memory OLTP engine solves problems in high-concurrency situations as it uses data structures that are entirely latch-free (lock-free), which means there are no latches or spinlocks on performance-critical paths in the system. Instead, it uses an optimistic Multiversion Concurrency Control (MVCC) technique that provides transaction isolation semantics, which help avoid interference among transactions. Thus, any user process can access any row in a table without acquiring latches or locks.
The combination of these MVCC and latch-free data structures results in a system in which user processes can run without stalling or waiting. In addition, stored procedures that operate on memory-optimized tables, though written in Transact-SQL, are compiled to highly efficient machine code. This maximizes the runtime performance for certain workloads and types of queries because the generated machine code only contains exactly what is needed to run the query, and nothing more. According to Microsoft, some applications can achieve a 50x performance increase only using the in-memory OTLP engine.
There are two main types of in-memory optimized OLTP tables: SCHEMA_AND_DATA and SCHEMA_ONLY. The following is a brief explanation of these:
SCHEMA_ONLY in-memory optimized OLTP tables are useful as staging tables for your database application. On the other hand, SCHEMA_AND_DATA in-memory optimized OLTP tables are more useful as transactional OLTP applications, where you would not want to lose data and transactions after SQL Server crashes or restarts.
The in-memory OLTP feature is only supported on the 64-bit Enterprise, Developer, or Evaluation editions of SQL Server 2014.
You can use the Memory Optimization Advisor wizard, which can be launched from SQL Server 2014 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.
For more information on how to use this wizard, see the Memory Optimization Advisor article at http://msdn.microsoft.com/en-us/library/dn284308.aspx.
In-memory optimized OLTP tables do not support a full set of SQL Server and Transact-SQL features that are supported by traditional, disk-based tables. Some of the key limitations of in-memory optimized OLTP tables include no support for SPARSE, IDENTITY, and computed columns; DML triggers; FILESTREAM data; columnstore, filtered, and full-text indexes; the ROWGUIDCOL option; FOREIGN KEY, CHECK, and UNIQUE constraints; TRUNCATE TABLE, MERGE, and dynamic and key set cursors.
The following data types are not supported by in-memory optimized tables: Datetimeoffset, geography, geometry, and LOBs (varchar(max), image, XML, text, and ntext).
For a full list of SQL Server 2014 features that are not supported with memory-optimized tables, see the MSDN resource Transact-SQL Constructs Not Supported by In-Memory OLTP at http://msdn.microsoft.com/en-us/library/dn246937.aspx.