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:
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.
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.
Minimize code execution time for reduced latency and improved throughput.
Require low latency business transactions which typical database solutions cannot achieve.
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.
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:
Discusses hardware and software requirements and guidelines for using memory-optimized tables.
Covers using In-Memory OLTP in a virtualized environment.
Contains code samples that show how to create and use a memory-optimized table.
Introduces memory-optimized tables.
Code example showing how to use a memory-optimized table variable instead of a traditional table variable to reduce tempdb use.
Introduces memory-optimized indexes.
Introduces natively compiled stored procedures.
Understanding and managing memory usage on your system.
Discusses data and delta files, which store information about transactions in memory-optimized tables.
Discusses backup, restore, and recovery for memory-optimized tables.
Discusses Transact-SQL support for In-Memory OLTP.
Discusses availability groups and failover clustering in In-Memory OLTP.
Lists new and updated syntax and features supporting memory-optimized tables.
Discusses how to migrate disk-based tables to memory-optimized tables.
More information about In-Memory OLTP is available on: