In memory table vs temporary table - benefits if temdb in already on ram disk

Question

In the docs, we have example of how temporary tables can be replaced with in-memory tables and because a memory-optimized table:

• is stored only in memory, and has no component on disk
• involves no IO activity
• involves no tempdb utilization or contention

they said that:

Memory-optimization results in speed increases that are often 10 times faster or more.

I am wondering if our system is already using ram for tempdb database, which of the benefits will still apply? I guess they are testing temdb on hard drive or ssd and doubt getting such great results.

---

More details. One of the system mostly used table variable function is the one calculating access to specific entity. Then, the result is joined to various table to get the request data. In order to optimize the latter joins, I am storing the result of this function in temporary table and the results are nice.

But for really large volumes (for example when the function returns 1-2 millions of entities) the execution of the function itself is slow (which is again normal since we are inserting so many rows in table variable).

So, I am thinking to rewrite this function as stored procedure and insert the entities in memory table, hoping that the CRUD operation with the table will 10 times faster or more.

Answer 1

In a comment from Ned Otter:

If the bottleneck was due to locking/blocking on system tables in tembdb, then I would imagine that placing it in RAM would not make much difference.

If your current workload is getting slowed down by contention over internal components, then putting tempdb on a ramdisk won't help you. This is because the problem is not I/O throughput related. Check out this detailed post from Paul Randal on the subject:

The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention

Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

Ned continues with:

That's why SQL 2019 supports a memory-optimized version of tempdb.

This is a new feature in 2019 that's designed to help with these types of contention issues. You can see a demo of how this helps, and how it's not quite ready in the current preview of SQL Server 2019, on Erik Darling's blog:

SQL Server 2019 In Memory tempdb

---

You really need to measure whether the slowdown is due to I/O, or due to contention. Use Paul Randal's post above to dig into that.

Since you updated your question to mention that the slow cases are when you insert millions of rows into a table variable, I'll assume the above-mentioned contention isn't likely you're problem (unless you're creating these table variables from many different connections at a time). In which case, the ramdisk approach could certainly help.

---

In your last update to the question, you said:

It seems that parallel plan is not supported for in-memory plan table (at least on insert), so I failed to optimize anything

This is a well-known "issue" with in-memory OLTP. Rather than using built-in parallelism, you need to "roll your own" by loading data into the in-memory tables from multiple connections.