Chief Data Officer at a tech company with 11-50 employees
Vendor
2015-09-09T13:38:11Z
Sep 9, 2015
Hi,
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.
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.
Consultant at a healthcare company with 1,001-5,000 employees
Real User
2015-09-09T14:39:56Z
Sep 9, 2015
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
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.
sqlmag.com
SQL Server is a relational database management system (RDBMS) by Microsoft. The product's main purposes are to store data and retrieve it as requested by other software applications - on the same computer or on another computer across a shared network. The solution is built on top of Structured Query Language (SQL), which is a standardized programming language used for relational database management.
The product is tied to Transact-SQL (T-SQL), which is an implementation of SQL from Microsoft...
Hi,
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.
The answer to your question is yes and no. Can you tell me more about the problem that you have?
Hi
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.
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
Indeed - Please note that my comments above relate to SQL Server 2014 (not 2012) as indicated.
msdn.microsoft.com.aspx
Here's the MSDN discussion of SQL 2014 in-memory OLTP
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.
sqlmag.com