In-Memory OLTP (In-Memory Optimization)
New in SQL Server 2014 (12.x), 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.
 |
Do you want to try out SQL Server 2016? Sign up for Microsoft Azure, and then go Here to spin up a Virtual Machine with SQL Server 2016 already installed. You can delete the Virtual Machine when you're finished. |
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 (12.x) 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.
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.
In this section
This section provides information about the following concepts:
| Topic | Description |
|---|---|
| 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. |
| Indexes on Memory-Optimized Tables | Introduces memory-optimized indexes. |
| 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: