Chief Data Officer at a tech company with 11-50 employees
Vendor
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
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
SQL Server is a comprehensive database management system known for ease of use, robust performance, and seamless Microsoft integration, making it a preferred choice for data management and analysis needs.SQL Server offers high availability with Always On availability groups and dynamic memory configuration. It integrates effortlessly with Microsoft products via tools like SSMS and Visual Studio. Supporting advanced features such as replication, clustering, and in-memory OLTP, it provides a...
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/en-us/library/dn133186(v=sql.120).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/sql-server-2014/application-performance-inmemory-oltp-database-engine