Changes to in-memory OLTP in SQL 2016

What are the changes?  In this post, I will be summarizing the changes to in-memory OLTP component of the product.  This feature was first introduced in SQL server 2014. We all know the first release will have limitations. From my past experience with SQL server, new features tend to have limitations.  So, what are the improvements?

Schema and data changes:  The limitation of altering a table is now removed. You should be able to use alter table on memory-optimized tables to drop, add and alter columns.  You should also be able to drop or rebuild indexes.

There is a bit of improvement on parallel plans.  Operations that use hash indexes can now be performed in parallel unlike SQL 2014 were the optimizer never creates parallel plan for any operation

 SQL 2016 now supports TDE for memory optimized data filegroup.

Support for multiple log reader threads to read transactions that affects memory-optimized tables from transaction log. One more bottleneck removed and this operation is now faster than it was in 2014. SQL server 2016 now allows multiple threads for both recovery and checkpoint, read and apply logged transactions which can result in the ability to scale memory optimized operations.

Some improvements on filestream processing.  The removal of dependency on windows files stream processing.

Garbage collection in memory:  In SQL 2014 garbage collection was too slow. In some cases, even when memory optimized table was dropped the memory wasn’t immediately available to be used due to slower  garbage collection rate as opposed to  row version changes. The algorithms of collecting garbage are now improved and hope this is the case.

In SQL 2016, all data whether it from disk or memory optimized tables will be visible immediately on AlwaysOn secondary replica. So less work for developers.

The original recommendation of 250GB of memory for memory-optimized tables are now lifted to 2TB

The requirement for columns of Memory-optimized tables that uses an index to be BIN2 collation is also removed.

Support for more DML operations such as select distinct, different type of joins (such as Left and right outer join), union, union all, nested stored procedure calls, subqueries and OR and not are now added.