Why I now (eventually) recommend In-Memory OLTP

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Around 8 months ago I wrote a blog posting where I discussed the various reasons why I don’t yet recommend In-Memory OLTP to my customers. A lot of people are approach me these days because they have some performance problems and they are thinking about using In-Memory OLTP to solve them.

As we all know, In-Memory OLTP is a very specialized technology in SQL Server and doesn’t yet have that many scenarios where it makes sense. In addition the first implementation in SQL Server 2014 has some huge limitations that I have also covered in my weblog posting.

Things have changed in the meantime – thank to God! For a few weeks now, the CTP 3 version of SQL Server 2016 has been available for public download. And Microsoft has made some huge improvements in the area of In-Memory OLTP. Let’s have a more detailed look at them.

Foreign-Key Constraints

One of the most important changes or should I say – improvements – is the support for Foreign-Key constraints! Yes, you heard correctly: In-Memory OLTP now supports Foreign-Key constraints. Normally you would have expected Foreign-Key constraints in the initial implementation, because it is about OLTP scenarios, but Microsoft didn’t support them on SQL Server 2014. Let’s have a look at the following code.

This code fragment creates a simple Foreign-Key constraint between 2 tables: a parent table, and a child table. In addition I have also populated both tables with some test data. And now let’s execute a simple query against both tables:

When you look at the query itself, you can see that I only want to retrieve information from the child table. Based on the Foreign-Key constraint the Query Optimizer knows that a record in the parent table must be there. Therefore the Query Optimizer can simplify this query by removing the unneccesary join. And when you look at the execution plan, you can see that this simplification has actually happened – nice:

Foreign-Key contraints are finally supported with In-Memory OLTP!

That’s one of the greatest improvements in In-Memory OLTP in SQL Server 2016 – the support for Foreign-Key constraints. The support for Foreign-Keys works even in Native Compiled Stored Procedures as you can see from the following listing.

When you execute a Native Compiled Stored Procedure, you still have no actual execution plan available. The only thing that you can still request is the estimated execution plan…

Check Constraints

Another nice improvement is that we now support Check Constaints. Check Constraints are very important, because they give the Query Optimizer more information about the shape of your data. And based on this information the Query Optimizer can give you a better performing execution plan. The following listing shows a simple example of how you can define a Check Constraint with In-Memory OLTP.

As you can see from this table definition, I have created here a simple Check Constraint, and told SQL Server that we only store the value 1 in the column Value. Even the contradiction detection works in combination with Check Constraints. The following query leads to a Constant Scan operator in the execution plan.

Indexes on Character columns

Back in the old days with SQL Server 2014, indexes on character columns in In-Memory OLTP didn’t work out of the box, because you had to use the BIN2 collation. That was a show stopper for a lot of people, because using another collation also affects the results when you perform comparisons or sorts on character columns.

With SQL Server 2016 Microsoft has now finally removed this limitation, and you can now create a Hash- or Range Index directly on a character column without using the BIN2 collation. Let’s have a look at the following example, which now just works with SQL Server 2016.

That’s really cool – well done!

Schema & Bucket Count Changes

Back in SQL Server 2014 this is where it got really bad and ugly: schema changes on Memory Optimized tables where not supported in any way. You had to drop and recreate your table, even if you just wanted to add a new index or change an existing index. Sorry, but this was one of most prominent limitations why I didn’t recommend In-Memory OLTP to my customers in any way. Even for changing the bucket count for a Hash Index you had to drop and recreate your table.

With SQL Server 2016 life is now fine and quite easy. First of all you can change the bucket count of an existing index with a simple ALTER INDEX REBUILD statement. Be aware that the memory that you need for the rebuild is twice the size of the table itself. The following listing shows you that improvement.

And in addition you can now alter your table. Unfortunately the creation of an index after the table creation doesn’t yet work on a Memory Optimized table. I haven’t yet have tried every possible change, but the following listing should give you an idea of what you can expect from SQL Server 2016.

Summary

As you have seen throughout this blog posting, In-Memory OLTP got a quite nice face-lift in SQL Server 2016. Now if someone asks me if they should use In-Memory OLTP, I would say yes – as long as they have the right problems, and these problems can’t be solved with the traditional relational engine of SQL Server.

Besides the improvements that I have mentioned in this blog posting, In-Memory OLTP in SQL Server 2016 gives you other new improvements and I will cover those in one of my upcoming blog postings.

What do you think about all these new improvements? Please feel free to leave a comment.

Like or share to get the source code.

Thanks for your time,

-Klaus

2 Comments

  • Hi Klaus,
    What is your opinion about memory-optimized features in the final SQL 2016 version?

    Regards.

    • Klaus Aschenbrenner

      Hello Oleg,

      My opinions haven’t changed for the RTM version 😉

      Thanks,

      -Klaus

It`s your turn

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top