SQL Server 2016 In-Memory OLTP Enhancements

The In-Memory OTLP feature that Microsoft first introduced to SQL Server 2014 was the most significant enhancement that Microsoft made to the relational database side in several releases. Originally, code-named Hekaton, the In-Memory OLTP improves the performance of OLTP queries by moving select tables into memory as well as compiling stored procedure into native x86 code. This isn’t just a limited feature like the old DBCC pintable that was around back in the SQL Server 6.5 days. The In-Memory OLTP engine came with an entirely new optimistic concurrency control lock-free query engine. The new In-Memory OLTP engine is designed to work at RAM speed.  It requires the SQL Server 2016 Enterprise edition but it works with all standard x86 servers – provided they have enough memory to take advantage of it. Depending on the application, the new In-Memory OLTP engine can provide anywhere from 2x to 30X performance gains.  If you’ve ever done any performance tuning you know that even a 2X gain is very significant for most business critical applications. To help you migrate your applications to In-Memory OLTP Microsoft provided the Analysis Migrate Report (AMR) tool. The AMR tool can analyze your database workload and select the tables and stored procedures that are best suited to migrate to In-Memory OLTP.

Although the new In-Memory OLTP engine offered some astonishing performance improvements its original implementation in SQL Server 2014 had some limitations. The total of in-memory tables was recommended to be under 256 MB. Not all data types were supported and not all of the SQL Server database features were supported. For instance LOBs and XML were not supported. Likewise, some of the more significant missing database features included support for Clustered Indexes, Check Constraints or Foreign Keys.

SQL Server 2016 eliminated many of these restrictions. The recommended maximum table size was boosted up to 2 GB. Another great new feature is the ability to combine the Columnstore index with In-Memory OLTP tables essentially doubling down on your in-memory performance enhancements. The FileStream data type is now supported as is Transparent Data Encryption (TDE). Other improvements include support for Foreign Keys and Check Constraints as well as Multiple Active Result Sets (MARS). Nested native procedure calls and natively compiled scalar UDFs are also now supported. ALTER TABLE is now partially supported. In the earlier release you had to drop and recreate the in-memory tables in order to make schema changes. With SQL Server 2016 you can perform off-line operations to add and drop columns, indexes, and constraints. In addition, you can now work with in-memory tables using the SQL Server Management Studio (SSMS) table designer.

With the new SQL Server 2016 release, Microsoft has eliminated many of the issues that might block the adoption of In-Memory OLTP enabling more businesses to take advantage of the performance improvements the in-memory technologies can provide.   

Underwritten by Microsoft and HPE