October 19, 2015 at 11:22 am
Is there any benefit to using "In-Memory" OLTP structures if SQL's data files are sitting on an "All flash array"?
Thanks!
October 23, 2015 at 5:07 am
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
October 23, 2015 at 5:19 am
October 23, 2015 at 5:45 am
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
November 10, 2015 at 2:45 pm
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.
November 11, 2015 at 12:50 am
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
November 11, 2015 at 1:54 am
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'
November 11, 2015 at 2:08 am
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
November 11, 2015 at 2:13 am
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/
November 11, 2015 at 2:26 am
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'
November 12, 2015 at 8:14 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply