Coming October 25: PeerSpot Awards will be announced! Learn more
2015-09-09T03:56:00Z
PeerSpot user
SAP NetWeaver Implementer at a tech services company
  • 7
  • 5

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

Thanks!

7
PeerSpot user
7 Answers
PeerSpot user
Chief Data Officer at a tech company with 11-50 employees
Vendor
2015-09-09T13:38:11Z
09 September 15

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.

Vendor
2015-09-09T16:00:18Z
09 September 15

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

it_user135999 - PeerSpot reviewer
User at a tech company with 51-200 employees
Vendor
2015-09-09T15:59:15Z
09 September 15

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.

PeerSpot user
Consultant at a healthcare company with 1,001-5,000 employees
Real User
2015-09-09T14:39:56Z
09 September 15

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

PeerSpot user
Chief Data Officer at a tech company with 11-50 employees
Vendor
2015-09-09T13:57:17Z
09 September 15

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

PeerSpot user
Enterprise Architect with 51-200 employees
Vendor
2015-09-09T13:45:09Z
09 September 15

https://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx

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

Learn what your peers think about SQL Server. Get advice and tips from experienced pros sharing their opinions. Updated: September 2022.
634,590 professionals have used our research since 2012.
PeerSpot user
Database Expert at a tech services company
Consultant
2015-09-09T12:17:50Z
09 September 15

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.
http://sqlmag.com/sql-server-2014/application-performance-inmemory-oltp-database-engine

Related Questions
CA
It Application Analyst at City of Corpus Christi
Jul 25, 2022
I work as an IT Application Analyst at a governmental organization (size: 1000+ employees). Currently, I'm researching Oracle Database and SQL Server. Which product is better and why?
See 1 answer
Evgeny Belenky - PeerSpot reviewer
Director of Community at PeerSpot (formerly IT Central Station)
25 July 22
Hi @Ariful Mondal, @Anteneh Asnake, @OmidKoushki, @reviewer1488372 ​and @reviewer1362099, Would you have any recommendations for @Celes Agiriga? Thank for sharing your knowledge with PeerSpot community members!​​ ​ ​ ​
Akın Kurtulan - PeerSpot reviewer
System Director at Türkiye İş Bankası
Jun 09, 2022
Hi community members, I work as a System Engineer at a large Financial Services company. Currently, I'm trying to understand the main differences between the two Relational Databases tools: MSSQL and Oracle Multitenant Architecture? Can you share your opinion? Thanks.
2 out of 3 answers
PG
User at rpc relational publishing & consulting GmbH
07 June 22
Hi Akin, First, both DBs manage your relational data on several operating systems (Linux, Windows Server, ...) and as Cloud Services. The newer architecture of Oracle tries to support you in a mixed environment where you can distribute a large DB over your own servers and cloud services. But as we always saw in the past, if a new feature of Oracle is good, Microsoft will follow. So your main questions should be: -How big is your DB? The bigger, the more I suggest Oracle. -Are you in a mixed world (Cloud and your own servers)? If Cloud is Azure, I think SQL Server is a good choice. -Is the price a topic? The liscence rules of Oracle are sometimes complicated. Hope this helps a little.  
Martin Zwarthoed - PeerSpot reviewer
Freelance Database Specialist at a tech services company with 51-200 employees
08 June 22
Hi Akin, Without going into the technical details; did you have a look a the pricing of MSSQL and Oracle databases?  I always hear that the Oracle database is better than MS SQL. But I never got to test this myself. What I do know is that when I tell a customer the Oracle pricing, they are usually going in another direction.  You must have a very good functional reason to go for Oracle considering the price difference. As @Patric Gehl ​suggested: a very big database is good but for a good reason. Kind regards, Martin Zwarthoed
Related Solutions
Download Free Report
Download our free SQL Server Report and get advice and tips from experienced pros sharing their opinions. Updated: September 2022.
DOWNLOAD NOW
634,590 professionals have used our research since 2012.