Benefit of In-Memory OLTP Structures on all flash arrays?

  • Is there any benefit to using "In-Memory" OLTP structures if SQL's data files are sitting on an "All flash array"?

    Thanks!

  • Yes, In-Memory OLTP stores data in RAM, which is faster than SSDs so you should see a performance improvement. There are many unsupported features with In-Memory OLTP, however. Check out the following links:

    https://msdn.microsoft.com/en-us/dn246937.aspx - Transact-SQL Constructs Not Supported

    https://msdn.microsoft.com/en-us/library/dn133181.aspx - Supported and Unsupported SQL Server Features

  • are you referring to the Filgroup that needs to be created with in memory OLTP? if so then yes, when persisting the data for an in memory table the faster you can save the data to the FG the better.

    Jayanth Kurup[/url]

  • Yes.

    Hekaton is actually less about the 'in-memory' and more about the way the rows are stored, the complete lack of locking and latching and the optimised access path.

    That said, it's not something you'll use for every system or every table. It's aimed at high-throughput OLTP systems, it's not something to be used everywhere.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks to everyone for the thoughtful answers. I wish I was insightful enough to have thought through the scenarios that percolated up in the replies.

    Why question was much simpler and to this point: We often think "SSDs are basically the same building blocks as RAM; therefore if we put something on a SSD it will be as fast as RAM".

    As you point out, THIS IS NOT THE CASE. Bandwidth to memory vs SSD, also write latency speeds, DRAM is constantly refreshed and thus has a latency measured in nanoseconds, often SSD latency is measured in microseconds.

    I was having a conversation with our infrastructure guy, and he kept saying "Well, everything is going to be on FLASH anyway, so it really doesn't matter".

    Not true.

  • nickm_GR (11/10/2015)


    I was having a conversation with our infrastructure guy, and he kept saying "Well, everything is going to be on FLASH anyway, so it really doesn't matter".

    "Everything going on flash" doesn't remove lock or latch overheads, which hekaton does. But again, it's absolutely NOT something that will be used for every table in a system.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hekaton does have a rather impressive list of limitations to its usage, those need checking prior to implementing.

    'Only he who wanders finds new paths'

  • david.alcock (11/11/2015)


    Hekaton does have a rather impressive list of limitations to its usage, those need checking prior to implementing.

    Yes, but to be clear, most of those limitations are for natively compiled procedures rather than for the in-memory tables (the limitations on T-SQL language features), and quite a number of the limitations are removed in SQL 2016.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I think the benefit would be to use the in-memory tables as a landing table, for ETL loads etc... left in schema mode only.

    If you set the table to be durable, it uses disk for the deltas being created to keep track of changing data, and this could delay SQL Server start-up times in the event of the instance being restarted. Could this delay be mitigated if the in-memory filegroup is placed on SSD?

    reference to delayed start-ups: https://www.mssqltips.com/sqlservertip/3355/sql-server-inmemory-tables-impact-on-startup-and-recovery/

  • GilaMonster (11/11/2015)


    david.alcock (11/11/2015)


    Hekaton does have a rather impressive list of limitations to its usage, those need checking prior to implementing.

    Yes, but to be clear, most of those limitations are for natively compiled procedures rather than for the in-memory tables (the limitations on T-SQL language features), and quite a number of the limitations are removed in SQL 2016.

    Yes absolutely, I did specify usage limitations. Whilst it has been enhanced in 2016, there are still caveats to consider.

    Useful link for the OP:

    http://sqlperformance.com/2015/05/sql-server-2016/in-memory-oltp-enhancements

    'Only he who wanders finds new paths'

  • nickm_GR (11/10/2015)


    Thanks to everyone for the thoughtful answers. I wish I was insightful enough to have thought through the scenarios that percolated up in the replies.

    Why question was much simpler and to this point: We often think "SSDs are basically the same building blocks as RAM; therefore if we put something on a SSD it will be as fast as RAM".

    As you point out, THIS IS NOT THE CASE. Bandwidth to memory vs SSD, also write latency speeds, DRAM is constantly refreshed and thus has a latency measured in nanoseconds, often SSD latency is measured in microseconds.

    I was having a conversation with our infrastructure guy, and he kept saying "Well, everything is going to be on FLASH anyway, so it really doesn't matter".

    Not true.

    Leaving SQL Server aside (which Gail has addressed already), I think your infrastructure guy is in need of some educating. I seriously hope that "infrastructure guy" isn't spec'ing and building servers at your company.

    Even if SSDs and the I/O bus were every bit as fast as RAM and its bus (not happening, but let's suppose), you'd still need to read data from disk into RAM before SQL Server could touch it, essentially reading it twice. And, as you point out, RAM is RAM and disk is disk, even when "disk" is a NAND-based SSD.

    Rich

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply