Aaron Bertrand

SQL Server 2016 : In-Memory OLTP Enhancements

November 30, 2015 by in SQL Server 2016 | 32 Comments
SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Itzik is a T-SQL trainer, a co-founder of SolidQ, and blogs about T-SQL fundamentals and query tuning.

Itzik’s Posts

Update: November 30, 2015

The SQL Server team has published a blog post with some new functionality for In-Memory OLTP in CTP 3.1:

Update: November 17, 2015
Jos de Bruijn has posted an updated list of In-Memory OLTP changes as of CTP 3.0:


I posted earlier about the changes to Availability Groups in SQL Server 2016, which I learned about at MS Ignite largely from a session by Joey D'Antoni and Denny Cherry. Another great session was from Kevin Farlee and Sunil Agarwal on the changes in store for In-Memory OLTP (the feature formerly known as "Hekaton"). An interesting side note: the video of this session shows a demo where Kevin is running CTP2.0 (build 13.0.200) – though it is probably not the build we'll see publicly this summer.

Feature/Limit SQL Server 2014 SQL Server 2016
Maximum combined size of durable tables 256 GB 2 TB
LOB (varbinary(max), [n]varchar(max)) Not supported Supported*
Transparent Data Encryption (TDE) Not supported Supported
Offline Checkpoint Threads 1 1 per container
ALTER PROCEDURE / sp_recompile Not supported Supported (fully online)
Nested native procedure calls Not supported Supported
Natively-compiled scalar UDFs Not supported Supported
ALTER TABLE Not supported
(DROP / re-CREATE)
Partially supported
(offline – details below)
DML triggers Not supported Partially supported
(AFTER, natively compiled)
Indexes on NULLable columns Not supported Supported
Non-BIN2 collations in index key columns Not supported Supported
Non-Latin codepages for [var]char columns Not supported Supported
Non-BIN2 comparison / sorting in native modules Not supported Supported
Foreign Keys Not supported Supported
Check/Unique Constraints Not supported Supported
Parallelism Not supported Supported
OUTER JOIN, OR, NOT, UNION [ALL], DISTINCT, EXISTS, IN Not supported Supported
Multiple Active Result Sets (MARS)
(Means better Entity Framework support.)
Not supported Supported
SSMS Table Designer Not supported Supported

* LOB support will not be available in the CTP shipping this summer.

ALTER TABLE is an offline operation, and will support adding/dropping columns, indexes, and constraints. There will be new syntax extensions to support some of these actions. You can change your bucket count values with a simple rebuild (however note that any rebuild will require 2X memory):

ALTER TABLE dbo.InMemoryTable
  ALTER INDEX IX_NC_Hash 
  REBUILD WITH (BUCKET_COUNT = 1048576);

In addition to these capacity / feature enhancements, there are also some additional performance enhancements. For example, there will be the ability to add an in-memory, updateable, non-clustered columnstore index over either disk-based or in-memory tables. And they have simplified the way that deleted rows are processed (in 2014, those operations use FileStream; in 2016, they will skip this step). There have also been improvements to the migration advisors and the best practices analyzer – they are now lighter on data gathering and provide more context about migration complexity.

There are still some limitations with some of these changes. TDE, as an example, requires additional steps when upgrading a database. But it's clear that as In-Memory OLTP gets more mature, they are chipping away at many of the biggest roadblocks to adoption.

But wait, there's more! If you want to use In-Memory OLTP in Azure SQL Database, there will be a public preview with full support coming this summer. So you won't need your own physical server with 2 TB of memory to push this feature to its limits. Do not expect any trickling of this feature into Standard Edition, however.