In-Memory OLTP (In-Memory Optimization)

SQL Server 2016 and later

Updated: November 22, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure 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

A 17-minute video explaining In-Memory OLTP and demonstrating performance benefits:

To download the performance demo for In-Memory OLTP used in the video:

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 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. For information about the new features in In-Memory OLTP, see What's New in Database Engine.

System_CAPS_ICON_note.jpg Note

Try it out

In-Memory OLTP is available in Premium Azure SQL databases. 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.

This section provides includes the following topics:

Quick Start 1: In-Memory OLTP Technologies for Faster Transact-SQL PerformanceDelve right into In-Memory OLTP
Overview and Usage ScenariosOverview of what In-Memory OLTP is, and what are the scenarios that see performance benefits.
Requirements for Using Memory-Optimized TablesDiscusses hardware and software requirements and guidelines for using memory-optimized tables.
In-Memory OLTP Code SamplesContains code samples that show how to create and use a memory-optimized table.
Memory-Optimized TablesIntroduces memory-optimized tables.
Memory-Optimized Table VariablesCode example showing how to use a memory-optimized table variable instead of a traditional table variable to reduce tempdb use.
Indexes on Memory-Optimized TablesIntroduces memory-optimized indexes.
Natively Compiled Stored ProceduresIntroduces natively compiled stored procedures.
Managing Memory for In-Memory OLTPUnderstanding and managing memory usage on your system.
Creating and Managing Storage for Memory-Optimized ObjectsDiscusses data and delta files, which store information about transactions in memory-optimized tables.
Backup, Restore, and Recovery of Memory-Optimized TablesDiscusses backup, restore, and recovery for memory-optimized tables.
Transact-SQL Support for In-Memory OLTPDiscusses Transact-SQL support for In-Memory OLTP.
High Availability Support for In-Memory OLTP databasesDiscusses availability groups and failover clustering in In-Memory OLTP.
SQL Server Support for In-Memory OLTPLists new and updated syntax and features supporting memory-optimized tables.
Migrating to In-Memory OLTPDiscusses how to migrate disk-based tables to memory-optimized tables.

More information about In-Memory OLTP is available on:

Database Features

Community Additions