2

Is an in-memory (aka Hekaton) table faster to read from than a non-in-memory table that's already cached in the memory of a SQL server?

I ask this because I have a case where I'm considering converting one of my tables to an in-memory table, but right now it's pretty fast to load into the memory cache, but the performance to read / operate against it after it's loaded into the cache is where it's slow.

Can I still see potential performance improvements by converting this table to an in-memory table?

2

What is the wait type you experience when your on-disk table is slow to read from when it's already cached?

Memory-optimized tables are optimized for writing, not reading, so it's very unlikely you'd see any performance benefit from migration to Hekaton.

1
  • I don't think any wait types are the core issue, it's more so the amount of data that needs to be scanned to fulfill the reads against it, regardless of what index is used. (The table is in the tens of billions of rows big.) But during the day, it is a highly transactional table, so that can tend to be a sizable secondary issue as well.
    – J.D.
    Nov 29 '19 at 18:51
1

You can see a large speed-up using natively compiled procedures, which require in-memory tables. There are, of course, many caveats.

In-memory uses different data structures in RAM than do disk-based tables. It also enforces concurrency through MVCC rather than locking (which the default READ COMMITTED isolation level uses), which will eliminate waiting on other transactions' locks to be released. This would suggest to me that in-memory, by-and-large, will run faster than traditional approaches for multiple concurrent transactions.

Note that the server behaviours are different. For example, traditionally with conflicting writes the second would block waiting for the first to finish. With in-memory MVCC one will rollback. The client application must account for this (and many other differences). Whether the additional work is worth the benefits, on your hardware with your data and your workload, only you can say.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.