Was this page helpful?
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

In-Memory OLTP (In-Memory Optimization)


Applies To: Azure SQL Database, SQL Server 2016 Preview

In-Memory OLTP can significantly improve OLTP database application performance. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP.

For what information about the new features in In-Memory OLTP, see What's New in Database Engine.


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).

In SQL Server 2016 Community Technology Preview 3 (CTP 3.0), several improvements have been made to In-Memory OLTP. 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.

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 Scenario

Implementation Scenario

Benefits of In-Memory OLTP

High data insertion rate from multiple concurrent connections.

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 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 means you can have both memory-optimized tables and disk-based tables in the same database, and query across both types of tables.

In SQL Server 2014 there are limitations in Transact-SQL surface area supported 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.


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 Tables

Discusses hardware and software requirements and guidelines for using memory-optimized tables.

Using In-Memory OLTP in a VM Environment

Covers using In-Memory OLTP in a virtualized environment.

In-Memory OLTP Code Samples

Contains code samples that show how to create and use a memory-optimized table.

Memory-Optimized Tables

Introduces memory-optimized tables.

Memory-Optimized Table Variables

Code example showing how to use a memory-optimized table variable instead of a traditional table variable to reduce tempdb use.

Introduction to Indexes on Memory-Optimized Tables

Introduces memory-optimized indexes.

Introduction to Natively Compiled Stored Procedures

Introduces natively compiled stored procedures.

Managing Memory for In-Memory OLTP

Understanding and managing memory usage on your system.

Creating and Managing Storage for Memory-Optimized Objects

Discusses data and delta files, which store information about transactions in memory-optimized tables.

Backup, Restore, and Recovery of Memory-Optimized Tables

Discusses backup, restore, and recovery for memory-optimized tables.

Transact-SQL Support for In-Memory OLTP

Discusses Transact-SQL support for In-Memory OLTP.

High Availability Support for In-Memory OLTP databases

Discusses availability groups and failover clustering in In-Memory OLTP.

SQL Server Support for In-Memory OLTP

Lists new and updated syntax and features supporting memory-optimized tables.

Migrating to In-Memory OLTP

Discusses how to migrate disk-based tables to memory-optimized tables.

More information about In-Memory OLTP is available on:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft