SQL Server 2012 - can I make OLTP transactions from my ERP run in memory?


77 Answers

author avatar


The answer to this question is - it depends. There are a lot of compromises that you will have to concede in order to configure a table to run in-memory with SQL Server 2012, these include:

- In-Memory OLTP currently doesn’t support foreign keys. This is already a show-stopper that many people 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…

- Any ALTER TABLE statement is not supported, you can’t later create any additional indexes that you might need. To facilitate a change, you have to drop your Memory Optimized Table, and recreate it again.

- 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.

- 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.

You must 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. 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.

- Memory Constraints: 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.

In summary, it is possible to have OLTP tables run in memory as long as you are prepared to concede a lot of best practice items and deal with the additional considerations that this brings.

author avatar

The answer to your question is yes and no. Can you tell me more about the problem that you have?

author avatar


There used to be commands such as dbccpintable that could be used to do this but is no longer present. Other ways to try and keep things in memory is to run transactions regularly of possible and to add good indexes.

author avatar

Based on my experience, not yet, there are several practical problems or constraints to this. It's a great technology but comes with few really good challenges, you won't be able to use foreign keys with these, you won't be able to make schema changes, for example alter statements etc, also when time comes for troubleshooting the problem there is no way to see the execution plan, these all things prevent to use in production. Hopefully soon in the future

author avatar

Indeed - Please note that my comments above relate to SQL Server 2014 (not 2012) as indicated.

author avatar


Here's the MSDN discussion of SQL 2014 in-memory OLTP

author avatar

Can you please be more specific .
If I understand your question/request . the In memory feature is from the sql server 2014 version see in the link.

Learn what your peers think about SQL Server. Get advice and tips from experienced pros sharing their opinions. Updated: April 2021.
502,104 professionals have used our research since 2012.