This content is part of the Essential Guide: SQL Server 2016 release guide: News and analysis on the new version
Manage Learn to apply best practices and optimize your operations.

SQL Server 2016 addresses in-memory OLTP limitations

Once hampered by limitations, in-memory OLTP will be more robust and complete in SQL Server 2016. Learn about the improvements.

Like many features in SQL Server 2016, in-memory OLTP has received its fair share of enhancements, with improvements...

in scalability and performance as well as expanded T-SQL capabilities for working with and managing memory-optimized tables.

In-memory OLTP provides row-based data access to memory-optimized tables, making it possible to support high-performance workloads, without requiring specialized hardware or software. The technology includes its own engine that works directly with the tables, dramatically improving throughput and reducing latency. The engine manages and maintains the table in-memory, while the data itself remains fully durable and transactional.

Microsoft introduced in-memory OLTP in SQL Server 2014 under the codename Hekaton, but the feature had a number of limitations, such as not supporting table updates or multithreaded log reads. SQL Server 2016 addresses many of those limitations and offers more robust and complete capabilities, tackling issues ranging from memory and socket limitations to adding support for Transparent Data Encryption (TDE) and multiple active result sets (MARS).

Scalability and performance

For SQL Server 2014, Microsoft recommended that the total size of your memory-optimized tables should not exceed 256 GB of memory. Although this was not a hard line, larger sizes were impractical because of the way in which in-memory OLTP worked. In SQL Server 2016, Microsoft has raised the recommended limit to 2 TB because of the improvements that have been made in various aspects of in-memory OLTP. In addition, SQL Server 2016 also supports four-socket machines for use with in-memory OLTP, unlike SQL Server 2014, which could not handle multi-socketed servers easily.

Another performance boost comes in the form of multiple log reader threads. In SQL Server 2014, the in-memory OLTP engine used a single offline checkpoint thread per database to scan the transaction log for changes related to memory-optimized tables. If there were a large number of transactions, the thread could fall behind. But SQL Server 2016 can use multiple threads to persist changes to the memory-optimized table, leading to gains in scalability and performance.

In-memory OLTP in SQL Server 2014 had another limitation. The optimizer could not create parallel plans for queries accessing memory-optimized tables. SQL Server 2016 overcomes that limit, at least for operations that use hash indexes and are not used in natively compiled stored procedures. SQL Server 2016 also has improved the garbage collection algorithms used to clean up row versions no longer needed. These improvements can be especially beneficial in large workloads that before could result in garbage collection processes not being able to keep up with the columns of data needing to be removed.

SQL Server 2016 also improves in-memory OLTP by decoupling individual files in the filegroup from filestream dependencies. SQL Server 2014 used the filestream technology to allocate and manage files indirectly. In SQL Server 2016, the In-Memory OLTP engine has direct control over the files, helping to streamline such operations as creating or dropping files or performing garbage collections.

T-SQL programming

One of the biggest challenges with in-memory OLTP in SQL Server 2014 was the inability to modify a memory-optimized table after it had been created, which could make tuning a table very difficult. SQL Server 2016 lifts this limitation and lets you alter the table without having to drop and re-create it. Note, however, that altering a memory-optimized table is an offline operation that needs twice as much memory to perform the update. If an application tries to access the table during the update, the operation is blocked until the table is back online. SQL Server 2016 also supports the ability to alter natively compiled procedures.

In fact, SQL Server 2016 includes a number of improvements in the T-SQL arena when it comes to in-memory OLTP. Memory-optimized tables now support foreign keys, check constraints, unique constraints, outer joins and operators, such as UNION [ALL], DISTINCT, IN and EXISTS. In addition, you can run the sp_recompile system stored procedure against a natively compiled procedure. Plus, SQL Server 2016 has added support for DML triggers and LOB data types on memory-optimized tables. You can even index nullable columns.

In-memory OLTP collation also wins big. Before SQL Server 2016, character columns in a memory-optimized table that participated in an index had to use a BIN2 collation. Plus, in a natively compiled procedure, all character comparisons had to use the BIN2 collation. In-memory OLTP in SQL Server 2016 provides full collation support for in-memory OLTP comparisons and indexing. That said, BIN2 still provides the best performance. Other collations can result in a significant performance penalty.

SQL Server 2016 also has improved natively compiled procedures in other ways, such as adding support for subqueries and nested native procedure calls. In addition, Microsoft has added support for natively compiled scalar user-defined functions (UDFs), which you can use any place you use built-in scalar functions. And like their stored procedure counterparts, natively compiled scalar UDFs can be altered or dropped after their initial creation.

Tool enhancements

Microsoft introduced transparent data encryption (TDE) in SQL Server 2008. Built into the database engine, TDE is an encryption technology that can be implemented on a database's data, log and backup files. The encryption and decryption processes are transparent to the applications and users that are accessing the data and require no special privileges outside the normal required authorizations. Unfortunately, SQL Server 2014 did not support TDE for memory-optimized tables. This changed in SQL Server 2016 and memory-optimized tables persisted to disk can be protected with TDE just like the rest of the database.

SQL Server 2016 also supports MARS access to memory-optimized tables. MARS makes it possible for applications to request data via multiple queries without needing to retrieve an entire result set before retrieving the next result set. In this way, applications can have multiple result sets open, while being able to interweave the data and execute other statements. And those result sets can be based on queries that reference memory-optimized tables and natively compiled procedures.

SQL Server 2016 has made improvements in other areas as well. For example, it includes enhancements to the transaction performance analysis reports, which let you evaluate whether in-memory OLTP will help you improve application performance. In addition, the table designer in SQL Server Management Studio now supports memory-optimized tables.

SQL Server 2016 in-memory OLTP

By no means is this an exhaustive list of in-memory OLTP improvements in SQL Server 2016, but the information does demonstrate some of the important ways Microsoft has enhanced its in-memory technologies. With its support for greater memory, more sockets, and multithreaded logging, as well as the ability to alter memory-optimized tables and natively compiled procedures, in-memory OLTP has taken a big leap forward by addressing many of the limitations in SQL Server 2014.

Will these changes be enough to convince SQL Server 2014 customers to upgrade? Of course, it's too soon to tell. But SQL Server 2016 comes with plenty of other new and improved features, so the in-memory OLTP enhancements might be just enough to tip the scales in Microsoft's favor.

Next Steps

Learn how in-memory OLTP works in SQL Server 2014

Listen to a podcast on the new features of SQL Server 2016

Quiz: How much do you really know about SQL Server 2016 licensing


Dig Deeper on SQL-Transact SQL (T-SQL)