Ok, some of you will hate me after this blog posting, but it has to be said. Over the last months I have done a lot of work in the area of In-Memory OLTP, and I also had a lot of customer requests regarding this amazing new technology. Unfortunately none of them were really excited about In-Memory OLTP – after I talked to them.

What is wrong with In-Memory OLTP?

NOTHING! Really! I like this amazing new technology, but I can’t yet recommend it to any of my customers. That’s it! A lot of my customers are currently running on SQL Server 2008 (R2), and they just want to move on – to SQL Server 2014. And there is that amazing new technology that gives them a throughput improvement of 100 times. So let’s use it! Unfortunately it’s not that easy.

My customers are paying me a hell of a lot of money to advise them whether the switch to SQL Server 2014 makes sense for their workload and their environment. And based on my recommendations, they will make a decision which will last for at least 5 years. Therefore I have a huge responsibility here. And I have to make sure that I will not propose a technology which doesn’t fit 100% into their environment. Unfortunately In-Memory OLTP doesn’t fit in 99% of all environments.

So what is wrong with In-Memory OLTP, so that I can’t recommend it to my valuable customers? Let’s start…

No Foreign Keys

When Microsoft first showed In-Memory OLTP to the public, it was just amazing: you switch the switch and afterwards the throughput of your workload is 100 times faster. You remember the needle that immediately goes to the right hand side, don’t you? But the reality looks completely different. Imagine you have an existing database, and you want to move some of its tables to In-Memory OLTP.

It will not work, because In-Memory OLTP currently doesn’t support foreign keys. And hopefully your database design has referential integrity. If not, please go back to the basics about database design and learn about referential integrity. This is already one of the most common show-stoppers that many customers have experienced. They would love to migrate some tables to In-Memory OLTP, but they don’t want to lose their referential integrity with it…

No Schema Changes

Imagine now, you have agreed with yourself not to use foreign keys on your tables, and you have moved them to In-Memory OLTP. So far so good. How do you deal with schema changes on your Memory Optimized Tables? Any ALTER TABLE statement is not supported, you can’t later create any additional indexes that you might need (Hash- and Range Indexes). When you define a Hash Index, you always have to specify the Hash Bucket count during the table creation.

This means that you have to project your data growth, and how your data will change over time. When you want to change your Hash Bucket count later on, because you have realized that you have a huge amount of Hash Collisions, you have to drop your Memory Optimized Table, and recreate it again. What do you do with your data in the meantime? You have to transfer it another table so that you don’t lose it. Where you will store that data in the meantime? Will the users be able to access the data in the meantime? A lot of questions but no really helpful answers…

No Execution Plans

Ok, you are now fine with Memory Optimized Tables, and now you want to use the Native Compilation feature of Stored Procedures. Wow, now the real fun begins! Native Compiled Stored Procedures are really, really fast, because you just execute native machine code – C code that was compiled down to assembly instructions! Awesome! But during the query execution there is no actual execution plan anymore, because you just execute assembly instructions, and you no longer execute those traditional slow interpreted execution plans.

What do you do, when you have encountered some performance problems? You can’t look at the actual execution plan to spot the problem. SQL Server only provides you the estimated plan, but the estimated plan doesn’t tell you anything about the reality. It’s just an estimate, nothing more. Do you really want to dig into the generated C code to find out why your Native Compiled Stored Procedure is so slow? I don’t think so.

No Recompilations

In addition to no actual execution plans, there are also no Recompilations. When your data distribution changes, you still run with the same compiled execution plan. Native Compiled Stored Procedures can’t be recompiled during runtime. As I have said, you just execute assembly instructions during runtime – they can’t be recompiled.

How can you get rid of this problem? Drop and recreate your stored procedure, because a ALTER PROCEDURE statement is not supported. When you drop your stored procedure, you will also lose all the granted security permissions. Do you really want to lose them? In addition, your Native Compiled Stored Procedure must be created with SCHEMABINDING. Therefore you can only drop your Memory Optimized Table (e.g. when you want to change the Hash Bucket count), when you have dropped the stored procedure itself. Ouch…

Native Compiled Business Logic

That’s one of my favourite ones: running complicated CPU intensive business logic in stored procedures with Native Compilation. Wow, I would like that large wallet that you have! Running CPU intensive business logic in SQL Server is one of the most expensive ideas you could have. SQL Server is licensed with USD 7000 per core in the Enterprise Edition. Therefore I don’t want to run any CPU intensive work in SQL Server! That’s just not economical. Of course from a Microsoft perspective it makes very good sense.

If you really have some complex CPU intensive business logic in SQL Server, move it to an Application Server (e.g. Web Services), because an Application Server is licensed in a completely different – cheaper – way. You mainly have an architectural problem, nothing more. I never ever want to run business logic inside SQL Server.

“640K Ought to be Enough for Anyone”

Haha, that’s one of my famous quotes in computer history. Fortunately he was wrong as you can see when you look at the hardware specification of the device where you are currently reading this blog posting. When you work with In-Memory OLTP you need to have a huge amount of RAM. Microsoft recommends an amount of RAM which is double what your Memory Optimized Table is in size, e.g. 300 GB RAM for a table size of 150 GB. The additional RAM is used for the row versioning that In-Memory OLTP uses internally. Imagine now, when you drop your table, and in the mean time you want to transfer the data into another Memory Optimized Table: in that case you should have around 600 GB RAM! Are you prepared for that?

It’s about OLTP!

In the first week of March I presented at the SQLRally conference in Copenhagen/Denmark and also at SQLBits in London/UK. At both conferences I covered some In-Memory OLTP stuff in my sessions. Of course I also covered some of the disadvantages that you have just read about. After my sessions people were coming to me, and asking me why I was so negative about this amazing new technology. No, I’m not really negative, I just want to show you both sides of the story, so that you are able to make the right decision.

And some people told me that In-Memory OLTP is so cool, because it can be used for staging table in ETL processes, and we even don’t need temp tables anymore in SQL Server. Whew! Let’s spell out the name of this technology again – very slowly: In-Memory O L T P! It’s about OLTP – Online Transaction Processing. Of course it can also make sense in other scenarios, but the main focus should be OLTP scenarios! Just think about that. If you currently have problems with your TempDb, work on your workload and try to reduce the impact that the workload has on TempDb. As I always say during my performance troubleshooting sessions: our ultimate goal is to solve the underlying root cause, and not to create a workaround for it. (e.g. set MAXDOP to 1, when you see CXPACKET waits).

Summary

In-Memory OLTP is an amazing new technology. But it’s a v1 implementation. There are too many restrictions in place that prevent a large amount of people from using it yet in production environments. I have outlined some of these restrictions in this blog posting, so that you think twice about whether you want to integrate In-Memory OLTP into your specific scenarios.

And I’m more than happy to go back to this blog posting in the next version of SQL Server, and to mark every paragraph as not relevant anymore, because Microsoft has removed these limitations. It’s up to you Microsoft to drive this awesome technology forward!

Thanks for your time,

-Klaus