Posts Tagged ‘Hekaton 2017’

SQL Server 2017 In-Memory enhancements and improvements over previous versions

December 20, 2017 1 comment

In-Memory tables were introduced in SQL Server 2014 and were also known as Hekaton tables. You can check my previous articles about In-memory tables for [SQL Server 2014] and [SQL Server 2016].

–> In-memory tables as new concept in SQL Server 2014/2016 had lot of limitations compared to normal Disk based tables. But with the new release of SQL Server 2017 some limitations are addressed and other features have been added for In-Memory tables. These improvements will enable scaling to larger databases and higher throughput in order to support bigger workloads. And compared to previous version of SQL Server it will be easier to migrate your applications to and leverage the benefits of In-Memory OLTP with SQL Server 2017.

–> I have collated all the major improvements here in the table below:

1. sp_spaceused is now supported for memory-optimized tables.

2. sp_rename is now supported for memory-optimized tables and natively compiled T-SQL modules.

3. CASE statements are now supported for natively compiled T-SQL modules.

4. The limitation of eight indexes on memory-optimized tables has been eliminated.

5. TOP (N) WITH TIES is now supported in natively compiled T-SQL modules.

6. ALTER TABLE against memory-optimized tables is now substantially faster in most cases.

7. Transaction log redo of memory-optimized tables is now done in parallel. This bolsters faster recovery times and significantly increases the sustained throughput of AlwaysOn Availability Group configuration.

8. Memory-optimized filegroup files can now be stored on Azure Storage. Backup/Restore of memory-optimized files on Azure Storage is supported.

9. Support for computed columns in memory-optimized tables, including indexes on computed columns.

10. Full support for JSON functions in natively compiled modules, and in check constraints.

11. CROSS APPLY operator in natively compiled modules.

12. Performance of B-tree (NonClustered) index rebuild for MEMORY_OPTIMIZED tables during database recovery has been significantly optimized. This improvement substantially reduces the database recovery time when NonClustered indexes are used.