In-Memory OLTP

In-Memory OLTP is an in-memory computing technology developed by Microsoft to accelerate the performance of transaction processing applications running on SQL Server databases. Originally called Hekaton, In-Memory OLTP is integrated with SQL Server's database engine and can be used to process tables of transaction data stored in system memory instead of having to pull them from disk storage. In-Memory OLTP is built around two core components: memory-optimized tables and natively compiled stored procedures.

Memory-optimized tables are typically ones that need to be frequently accessed as part of transaction processing applications. In addition to the standard representation of such tables on a hard drive, one is stored in active memory. Being able to process data there improves the speed of business transactions run against the tables because the SQL Server system only needs to interact with the data in memory. Microsoft says memory-optimized tables are fully durable, ensuring that data from completed transactions will be preserved even if a database crashes. They're accessed using T-SQL commands just like conventional disk-based tables are, and you can query both types of tables simultaneously.

Natively compiled stored procedures are preassembled sets of T-SQL statements that can be called by different applications to speed up query execution and the processing of business logic. The stored procedures are compiled in the form of native code when they're created, not when they're first executed as with conventional stored procedures stored on disk. The natively compiled ones can only interact with memory-optimized tables via In-Memory OLTP.

The In-Memory OLTP engine also uses latch-free data structures and multi-version concurrency control to help streamline transaction processing jobs -- Microsoft claims that performance is commonly five to 20 times faster than it is with disk-based processing on applications that are suited to the in-memory technology. The types of workloads that Microsoft recommends for use of In-Memory OLTP include: applications with high data insertion rates coming from multiple concurrent connections; ones requiring scalable read performance for periodic batch insertions and updates; intensive processing of business logic; low-latency transactions that disk-based processing can't handle; session state management tasks; processing of temporary tables; and extract, transform and load (ETL) data integration jobs. In addition, the company says that In-Memory OLTP is best at boosting the performance of applications with short-running transactions.

The in-memory processing engine was originally released with SQL Server 2014 and then updated in SQL Server 2016 to remove initial limitations on the amount of data it could handle and add support for various Transact-SQL (T-SQL) programming functions that weren't part of the first release. Microsoft also made In-Memory OLTP available for use with its cloud-based Azure SQL Database platform in late 2015.

This was last updated in August 2016

Continue Reading About In-Memory OLTP

Dig Deeper on Microsoft SQL Server Tools and Utilities