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.
To use In-Memory OLTP, you need to 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 refer to 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 transactions driven from a highly scaled-out middle-tier. To achieve this, it uses latch-free data structures and an 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 about 10 times performance improvements are common.
The following table summarizes the most benefited workload patterns by using In-Memory OLTP:
| Implementation Scenario 1 | Implementation Scenario 2 | Benefits of In-Memory OLTP |
|---|---|---|
| High data insertion rate from multiple concurrent connections. | Unable to keep up with the insert workload.Primarily append-only store. | Reduce logging.Eliminate contention. |
| Read performance and scale with periodic batch inserts and updates. | Unable to meet scale-up requirements. High performance read operations, especially when each server request has multiple read operations to perform. | Lower latency data retrieval. Minimize code execution time. Eliminate contention when new data arrives. |
| Intensive business logic processing in the database server. | Intensive computation inside stored procedures.Read and write contention.Insert, update, and delete workload. | Minimize code execution time for reduced latency and improved throughput.Eliminate contention. |
| 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. | High scale load from numerous stateless web servers. Frequent insert, update and point lookups. |
Efficient data retrieval. Optional IO reduction or removal, when using non-durable tables Eliminate contention. |
In-Memory OLTP will improve programming patterns that include concurrency scenarios, point lookups and workloads in which 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.
SQL Server 2014 is designed to improve OLTP database application performance.