With SQL server 2016 around the corner, I would like to share some of my thought on the usage for In-Memory OLTP. I will not go through the setup of In-Memory OLTP, however, will discuss if it can address the needs from the common enterprise workload.

Let's go back to SQL server 2014 where it all started, in-memory OLTP was a new concept then, it was a just start of a great journey. It did made a big noise and its great in demo, but can you really use it in your enterprise environment, hmm, I doubt it. It does has many limitation, and to really enjoy the performance improvement, you do need to re-write some of your code (major part). It does have its place, if you can develop around its limitations and do all the compiled stored proc, you do get the improvement as stated, but if you are like many companies out there that use third party application, you are mostly out of luck. You might still can get a bit of the performance improvement, but by far to what it stated. Try talking to a vendor asking them to change their code for performance, either they will say no or its under development and wait for months (if not years) to get it.

Move forward to SQL server 2016 (CTP 3 as of writing), many of the limitation has been lifted, to name some major ones will be:

• Duration table size can now be 2 Tb instead of 256 GB. 
• Support of foreign key
• Parallelism support
• Support of alter table statement
• TDE support
• Option to change the HASH index bucket_count through index rebuild
• More data type supported

You can see that it now become a more usable feature and you can really start to migrate your enterprise workload to it. With the new changes, some of the third party application can also be migrated without heavy modification of the code, which means its a very valuable option for DBA to check if they can improve their environment. Not to mention with columnstore index can now utilize parallelism, real-time analytic will become lighting fast. You can clearly see that Microsoft had make a big effort on this and helping them to continue improve their performance.

There are only one limitation I am still facing, which is the table row size still limited to 8060 bytes, which means some of the large width tables out there cannot use this awesome feature without some modification of the underlying tables, But apart from this, I can see that many of the enterprise workload should be able to utilize this great feature and see how it can improve your environment.

If you haven't try out the latest SQL server 2016 CTP 3 yet, here is the link to download it, I would strongly suggest you to test it out, there are more new features to discover!