In-Memory OLTP and Memory-Optimization

APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

In-Memory OLTP can significantly improve the performance of transaction processing, data ingestion and data load, and transient data scenarios. To jump into the basic code and knowledge you need to quickly test your own memory-optimized table and natively compiled stored procedure, see

We have uploaded to YouTube a 17-minute video explaining In-Memory OLTP on SQL Server, and demonstrating the performance benefits.

For a more detailed overview of In-Memory OLTP and a review of scenarios that see performance benefits from the technology:

Note that In-Memory OLTP is the SQL Server technology for improving performance of transaction processing. For the SQL Server technology that improves reporting and analytical query performance see Columnstore Indexes Guide.

Several improvements have been made to In-Memory OLTP in SQL Server 2016 (13.x) and SQL Server 2017 (14.x), as well as in Azure SQL Database. 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.

Note

Try it out

In-Memory OLTP is available in Premium and Business Critical tier Azure SQL databases and elastic pools. To get started with In-Memory OLTP, as well as Columnstore in Azure SQL Database, see Optimize Performance using In-Memory Technologies in SQL Database.

In this section

This section provides includes the following topics:

Topic Description
Quick Start 1: In-Memory OLTP Technologies for Faster Transact-SQL Performance Delve right into In-Memory OLTP
Overview and Usage Scenarios Overview of what In-Memory OLTP is, and what are the scenarios that see performance benefits.
Requirements for Using Memory-Optimized Tables Discusses hardware and software requirements and guidelines for using memory-optimized tables.
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.
   

This section provides links to other websites that contain information about In-Memory OLTP on SQL Server.

17 minute video, indexed

  • Video title: In-Memory OLTP in SQL Server 2016
  • Published date: 2019-03-10
  • Duration: 17:32
  • Hosted by: Jos de Bruijn, Senior Program Manager on SQL Server

Demo can be downloaded

At the time mark 08:09, the video runs a demonstration twice. You can download the runnable performance demo that is used in the video, from the following link:

The general steps seen in the video are as follows:

  1. First the demo is run with a regular table.
  2. Next we see a memory-optimized edition of the table being created and populated by a few clicks in SQL Server Management Studio (SSMS.exe).
  3. Then the demo is rerun with the memory-optimized table. An enormous speed improvement is measured.

Index to each section in the video

Time mark link Section title
A.  00:00 The beginning.

B.  00:56

Why customers should care about In-Memory OLTP.
    01:03 Modern hardware requires modern architecture of database system.
    02:10 Explosion in data being generated; operations need to be instant (low latency).
    03:19 Reduce TCO - do more with the resources you have.

C.  03:33

What In-Memory OLTP is.
Performance optimized using memory-optimized technology.
    05:03 Up to 30X faster transaction processing.
    05:22 Fully durable - data survives server failures.
    06:15 Fully integrated in SQL Server. Thus no new languages or tools to learn.
    07:22 First released in SQL Server 2014, but major improvements in 2016.
    07:58 Available in Azure SQL Database too (in the cloud).

D.  08:09

Performance demonstration.
Run the demo with a regular table.
    09:11 SSMS context menu: Reports > Transaction Performance Analysis
    10:38 SSMS context menu: Memory Optimization Advisor
    Actually create a memory-optimized table from a regular table, plus migrate the data.
    11:28 Rerun the demo, see 45X speed improvement.

E.  12:17

Easier to use In-Memory OLTP in SQL Server 2016 (compared to 2014).
    12:43 Simplified analysis to help with app migration.
    13:03 Reduced complexity of app migration through increased Transact-SQL language support (for example, with foreign keys and triggers).
    13:56 Improved manageability.
    For example, change schema and indexes, auto-update of statistics.

F.  14:46

Improved scalability.
    15:12 Large memory-optimized tables (up to 2TB per database).
    15:34 Even better scaling.
    16:41 Do more with the resources you already have!

G.  16:53

Final comments. (Ends at 17:32.)
   

See also

Database Features