Export (0) Print
Expand All

In-Memory OLTP (In-Memory Optimization)

SQL Server 2014

New in SQL Server 2014, 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 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.

Important note 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:

Topic

Description

Scenarios for Improving Performance with In-Memory OLTP

Discusses the purpose and common workloads for In-Memory OLTP.

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:

Community Additions

ADD
Show:
© 2014 Microsoft