Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
Posted Friday, February 14, 2014 9:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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?


[url=http://www.databasejournal.com/features/mssql/natively-compiled-stored-procedures-with-sql-server-2014.html][/url]
Post #1541653
Posted Saturday, February 15, 2014 4:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 14,000, Visits: 28,381
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

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


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:02 AM
Points: 194, Visits: 661
This is one case where SQL Server 2014 In-Memory OLTP is successfully implemented.

https://blogs.technet.com/b/dataplatforminsider/archive/2013/07/30/sql-server-2014-in-memory-oltp-bwin-migration-and-production-experience.asp



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


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:24 AM
Points: 153, Visits: 981
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
Post #1542483
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse