February 14, 2014 at 9:36 am
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?
February 15, 2014 at 4:03 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 16, 2014 at 5:22 am
This is one case where SQL Server 2014 In-Memory OLTP is successfully implemented.
___________________________
Do Not Optimize for Exceptions!
February 18, 2014 at 4:44 am
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.
Code:
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.
Workload:
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.
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply