BACKGROUND IMAGE: iSTOCK/GETTY IMAGES
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.
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.
Example 2 -- Altering a memory-optimized table to change CarrierTrackingNumber column data type from nvarchar(25) to nvarchar(50).
Example 3 -- Altering a memory-optimized table to drop a UnitPriceDiscount column.
Example 4 -- Altering a memory-optimized table to rebuild the 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
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.
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.
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