In-Memory OLTP (In-Memory Optimization)


Updated: October 5, 2016

Applies To: SQL Server 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In-Memory OLTP can significantly improve OLTP database application performance. It improves throughput and reduces latency for transaction processing, and can help improve performance of transient data scenarios such as temp tables and ETL. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for transaction processing. To jump into the basic code and knowledge you need to quickly test your own memory-optimized table and natively compiled stored procedure, see Quick Start 1: In-Memory OLTP Technologies for Faster Transact-SQL Performance.

A 17-minute video explaining In-Memory OLTP and demonstrating performance benefits: In-Memory OLTP in SQL Server 2016.

To download the performance demo for In-Memory OLTP used in the video: In-Memory OLTP Performance Demo v1.0

Note that In-Memory OLTP is the SQL Server technology for improving performance of transaction processing. For the SQL Server technology that improves reporting and analytical query performance see Columnstore Indexes Guide.

Several improvements have been made to In-Memory OLTP in SQL Server 2016 as well as in Azure SQL Database. The Transact-SQL surface area has been increased to make it easier to migrate database applications. Support for performing ALTER operations for memory-optimized tables and natively compiled stored procedures has been added, to make it easier to maintain applications. For information about the new features in In-Memory OLTP, see What's New in Database Engine.

System_CAPS_ICON_note.jpg Note

Try it out

In-Memory OLTP is in preview for Premium Azure SQL databases. To get started with an In-Memory OLTP sample, and an In-Memory Analytics sample, see Get started with In-Memory (Preview).

To use In-Memory OLTP, you define a heavily accessed table as memory optimized. Memory-optimized-tables are fully transactional, durable, and are accessed using Transact-SQL in the same way as disk-based tables. A query can reference both memory-optimized tables and disk-based tables. A transaction can update data in memory-optimized tables and disk-based tables. Stored procedures that only reference memory-optimized tables can be natively compiled into machine code for further performance improvements. The In-Memory OLTP engine is designed for extremely high session concurrency for OLTP type of transactions driven from a highly scaled-out middle-tier. To achieve this, it uses latch-free data structures and optimistic, multi-version concurrency control. The result is predictable, sub-millisecond low latency and high throughput with linear scaling for database transactions. The actual performance gain depends on many factors, but 5-to-20 times performance improvements are common.

The following table summarizes the workload patterns that may benefit most by using In-Memory OLTP:

Implementation ScenarioCharacteristicsBenefits of In-Memory OLTP
High data insertion rate from multiple concurrent connections, for example sensors in IoT (Internet of Things) scenario.Primarily append-only store.

Unable to keep up with the insert workload.
Eliminate contention.

Reduce logging.
Read performance and scale with periodic batch inserts and updates.High performance read operations, especially when each server request has multiple read operations to perform.

Unable to meet scale-up requirements.
Eliminate contention when new data arrives.

Lower latency data retrieval.

Minimize code execution time.
Intensive business logic processing in the database server.Insert, update, and delete workload.

Intensive computation inside stored procedures.

Read and write contention.
Eliminate contention.

Minimize code execution time for reduced latency and improved throughput.
Low latency.Require low latency business transactions which typical database solutions cannot achieve.Eliminate contention.

Minimize code execution time.

Low latency code execution.

Efficient data retrieval.
Session state management.Frequent insert, update and point lookups.

High scale load from numerous stateless web servers.
Eliminate contention.

Efficient data retrieval.

Optional IO reduction or removal, when using non-durable (SCHEMA_ONLY) tables.
Temp Tables and Table-Valued ParametersFrequent use of temporary tables in application logic.

Frequent use of Table-Valued Parameters (TVPs) to send data from the client to the server.
Eliminate contention in tempdb.

Remove IO by using memory-optimized table variables and non-durable (SCHEMA_ONLY) tables.
ETL (Extract, Transform, Load)Use of staging tables to load data into a data warehousing database.Efficient data access.

Remove IO by using non-durable (SCHEMA_ONLY) tables.

For more information about scenarios where In-Memory OLTP will result in the greatest performance gains, see In-Memory OLTP – Common Workload Patterns and Migration Considerations.

In-Memory OLTP will improve performance best in OLTP with short-running transactions.

Programming patterns that In-Memory OLTP will improve include concurrency scenarios, point lookups, workloads where there are many inserts and updates, and business logic in stored procedures.

Integration with SQL Server and Azure SQL Database means you can have both memory-optimized tables and disk-based tables in the same database, and query across both types of tables.

Note that there same are limitations in Transact-SQL surface area supported for In-Memory OLTP. See Transact-SQL Support for In-Memory OLTP.

In-Memory OLTP achieves significant performance and scalability gains by using:

  • Algorithms that are optimized for accessing memory-resident data.

  • Optimistic concurrency control that eliminates logical locks.

  • Lock free objects that eliminate all physical locks and latches. Threads that perform transactional work don’t use locks or latches for concurrency control.

  • Natively compiled stored procedures, which have significantly better performance than interpreted stored procedures, when accessing a memory-optimized table.

System_CAPS_ICON_important.jpg Important

Some syntax changes to tables and stored procedures will be required to use In-Memory OLTP. For more information, see Migrating to In-Memory OLTP. Before you attempt to migrate a disk-based table to a memory-optimized table, read Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP to see which tables and stored procedures will benefit from In-Memory OLTP.

This section provides information about the following concepts:

Requirements for Using Memory-Optimized TablesDiscusses hardware and software requirements and guidelines for using memory-optimized tables.
In-Memory OLTP Code SamplesContains code samples that show how to create and use a memory-optimized table.
Memory-Optimized TablesIntroduces memory-optimized tables.
Memory-Optimized Table VariablesCode example showing how to use a memory-optimized table variable instead of a traditional table variable to reduce tempdb use.
Indexes on Memory-Optimized TablesIntroduces memory-optimized indexes.
Natively Compiled Stored ProceduresIntroduces natively compiled stored procedures.
Managing Memory for In-Memory OLTPUnderstanding and managing memory usage on your system.
Creating and Managing Storage for Memory-Optimized ObjectsDiscusses data and delta files, which store information about transactions in memory-optimized tables.
Backup, Restore, and Recovery of Memory-Optimized TablesDiscusses backup, restore, and recovery for memory-optimized tables.
Transact-SQL Support for In-Memory OLTPDiscusses Transact-SQL support for In-Memory OLTP.
High Availability Support for In-Memory OLTP databasesDiscusses availability groups and failover clustering in In-Memory OLTP.
SQL Server Support for In-Memory OLTPLists new and updated syntax and features supporting memory-optimized tables.
Migrating to In-Memory OLTPDiscusses how to migrate disk-based tables to memory-optimized tables.

More information about In-Memory OLTP is available on:

Database Features

Community Additions