Post reply

When is an In-Memory table a good solution?

  • February 14, 2014 at 9:36 am

    #304422

    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

    #1689280

    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

    #1689331

    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!

  • February 18, 2014 at 4:44 am

    #1689827

    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