Essential Guide

SQL Server 2016 release guide: News and analysis on the new version

A comprehensive collection of articles, videos and more, hand-picked by our editors
Manage Learn to apply best practices and optimize your operations.

In-memory OLTP reborn with SQL Server 2016

Microsoft's in-memory OLTP needed improvements, and they are here with SQL Server 2016. Learn about the enhancements to this query-boosting feature.

To take full advantage of a modern computing environment, and to meet the demands and expectations of business...

applications, Microsoft introduced in-memory OLTP. This lets you take advantage of large memory and dozens of cores to boost performance of OLTP operations, as well as reduce processing times by up to 30 to 40 times. The in-memory OLTP engine is a fully ACID-compliant transactional database engine that is integrated with the native SQL Server database engine.

In-memory OLTP was first introduced with SQL Server 2014; now, SQL Server 2016 offers significant improvements. Let's look at some of the key changes to in-memory OLTP.

Enhanced in-memory performance

Compared with SQL Server 2014 in-memory technology, SQL Server 2016 in-memory technology provides improved query performance. For example, the same transactions that run against SQL Server 2014 memory-optimized tables will run 30 times faster when run against SQL Server 2016 memory-optimized tables.

Maximum memory for memory-optimized tables

With SQL Server 2014, Microsoft recommended a maximum of 256 GB of memory for memory-optimized tables. This recommendation is based on the number of checkpoint files that can be supported to store data from your memory-optimized tables, which are used during the recovery process. In SQL Server 2016, the recommended maximum is 2 TB of memory for memory-optimized tables. Of course, no matter how much data you have in memory-optimized tables, all data for all of those tables must fit into the memory.

Collation

In SQL Server 2014, it is mandatory to use BIN2 collation for any character columns that use all or part of an index key. Moreover, in natively compiled stored procedures, you must also use BIN2 collation for all comparisons between character values. Although this is no longer a hard limitation for the SQL Server 2016 in-memory OLTP engine, meaning you can use any collation for comparisons between character values in natively compiled stored procedures or for character columns that are part of an index, you must accept that you may experience a significant loss in performance when using collations other than BIN2 for character columns.

Schema and data changes

SQL Server 2014 in-memory OLTP does not allow you to modify memory-optimized tables once they are created. If you have to make changes to a memory-optimized table schema or rebuild its indexes, you must drop and re-create the memory-optimized table, using DROP TABLE and CREATE TABLE statements. With SQL Server 2016 in-memory OLTP, this is no longer a constraint; you can make changes to memory-optimized tables after creation, using the ALTER TABLE statement. For example, you can use the ALTER TABLE statement to add, alter or remove columns from memory-optimized tables, or to add, drop or rebuild indexes. Here are examples that show how you can modify a memory-optimized table schema and how to rebuild its indexes:

Example 1 -- Altering a memory-optimized table to add two columns with a NOT NULL constraint and with a DEFAULT definition, and using WITH VALUES to provide values for existing rows in the table.

Figure 1: Add two columns to provide specific values for existing rows
Figure 1: Add two columns to provide specific values for existing rows

Example 2 -- Altering a memory-optimized table to change CarrierTrackingNumber column data type from nvarchar(25) to nvarchar(50).

Figure 2: Change CarrierTrackerNumber column data type
Figure 2: Change CarrierTrackerNumber column data type

Example 3 -- Altering a memory-optimized table to drop a UnitPriceDiscount column.

Figure 3: Drop UnitPriceDiscount column
Figure 3: Drop UnitPriceDiscount column

Example 4 -- Altering a memory-optimized table to rebuild the non-clustered hash index with a new bucket count.

Figure 4: Rebuild non-clustered hash index with a new bucket count
Figure 4: Rebuild non-clustered hash index with a new bucket count

More Transact-SQL features supported

SQL Server 2014 natively compiled stored procedures do not support the full set of Transact-SQL language constructs that are supported with interpreted (disk-based) stored procedures. Fortunately, SQL Server 2016 natively compiled procedures support a wider range of Transact-SQL features, including the following Transact-SQL constructs:

  • LEFT and RIGHT OUTER JOIN
  • SELECT DISTINCT
  • OR and NOT operators
  • Subqueries in all clauses of a SELECT statement
  • Nested stored procedure calls
  • UNION and UNION ALL
  • All built-in math functions

Parallel plans

SQL Server 2014's optimizer only creates a serial plan for operations that run against memory-optimized tables. However, SQL Server 2016 creates a parallel plan for certain operations that use a hash index, provided they are not used in natively compiled stored procedures.

Transparent Data Encryption (TDE)

In SQL Server 2014, the TDE feature is not compatible with memory-optimized tables. That means data stored for memory-optimized tables on a MEMORY_OPTIMIZED_DATA filegroup are not encrypted on disk. This is no longer a limitation and the TDE function is fully compatible with SQL Server 2016 memory-optimized tables -- i.e., all memory-optimized data tables will be encrypted on disk.

AlwaysOn

In SQL Server 2014, the visibility of data in memory-optimized tables on secondary replicas is delayed by few transactions. This is not the case with SQL Server 2016 memory-optimized tables; like disk-based tables, all data is visible immediately to the user.

Number of sockets

In SQL Server 2016, several improvements have been made to in-memory OLTP algorithms, including better partitioning of work-time queues and more capacity in the threads assigned to do garbage collection of in-memory data. Because of this, the SQL Server 2016 in-memory OLTP is much more efficient with multiple socket machines. For example, compared to the SQL Server 2014 in-memory OLTP, you can expect efficient scaling with SQL Server 2016 in-memory OLTP on a four-socket machine.

Next Steps

SQL Server 2016 expands the capabilities of in-memory OLTP

SQL Server 2016 offers new features for cloud, security and analytics

SQL Server 2014 introduced in-memory OLTP to SQL Server

This was last published in October 2015

Essential Guide

SQL Server 2016 release guide: News and analysis on the new version

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What other features do you want to see in SQL Server 2016?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close