Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

When is an In-Memory table a good solution? Expand / Collapse
Posted Friday, February 14, 2014 9:36 AM


Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:54 AM
Points: 13, Visits: 91

I've read through a few articles on In-Memory tables for 2014, such as the one below. It seems that there are a lot of limitations to the feature, or at least when combined with natively compiled sprocs. I'm curious, what is an example of a real world scenario that would be made much efficient via an in-memory table?

Post #1541653
Posted Saturday, February 15, 2014 4:03 AM



Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 17,171, Visits: 32,132
It's all about speed. When you need really fast OLTP processing, you go with the in-memory tables and the compiled procedures. The limitations are too severe for any small or simple implementations.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1541834
Posted Sunday, February 16, 2014 5:22 AM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 12, 2016 6:09 AM
Points: 219, Visits: 774
This is one case where SQL Server 2014 In-Memory OLTP is successfully implemented.

Do Not Optimize for Exceptions!
Post #1541898
Posted Tuesday, February 18, 2014 4:44 AM



Group: General Forum Members
Last Login: Saturday, November 5, 2016 8:10 AM
Points: 153, Visits: 1,053
The picture is in fact a bit more than "just speed".

You have too also look at the code and the workload that you are running.
the more complex computation and logic, the more you will benefit from natively compiled stored procedures. - On the opposite: if your procedure consists of nothing than a pure “INSERT INTO xyz” and that’s it, you will probably not see a performance gain and it can even be slower (!)
The picture changes again, if you have a lot of such inserts coming in within a batch.
The more you have Updates the less you will benefit from the In-Memory Optimized Tables because of the version chains.
Also for a pure read-workload in OLAP style consisting of thousands of rows returned, you’d better go with Clustered ColumnStore Indexes.

That’s not everything but should get you an idea.


MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
Post #1542483
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse