Should I use tempdb or memory optimized table variable?

Question

I would like to change some of my stored procs to use memory optimized tables. Before I do this, could you help me to clarify some things?

parallel execution

Here I can read, the tempdb doesn’t support it for insert-operation (What's the difference between a temp table and table variable in SQL Server?). But what about with data retrieving, like select?

memory underrun

With memory optimized tables could it be possible, that I get an error, if the server is less of RAM? In this thread (https://www.brentozar.com/archive/2014/06/temp-tables-table-variables-memory-optimized-table-variables/) I can read about this.

performance boost

Memory optimized tables do the boost, or not (What's the difference between a temp table and table variable in SQL Server?)? This thread (https://www.brentozar.com/archive/2014/04/memory-ssd-tempdb-temp-table-sql-2014/) describes, that tempdb (starting with SQL 2014) can also reduce disk IO.

Should I use tempdb or memory optimized table variables?

I'm using Microsoft SQL 2014 (12.0.5000.0) Enterprise 64 bit. The SQL queries are a bit complex: e.g. there is a query, that uses 2 views and each of these views depending on other views.

The result of the whole query is not big (a couple thousands of rows). Some of the tables, from which the views are depending, have 40 - 50k rows. I alraedy realized, if rewrite a view into a table valued function, it increases the performance. Especially if the original view is embedded in another view, and used more time.

But what about the 3 points above? For me the links are somehow conflicting.

Answer 1

Parallel Execution:

SQL Server does support parallel inserts into temp tables in SQL Server 2014 with the SELECT INTO syntax. In SQL Server 2016 parallel inserts are also supported into temp tables that are heaps. In SQL Server 2016 SP1 parallel inserts into heaps require the TABLOCK hint.

A query that modifies table variables will not contain any parallel zones. However, a query that references a table variable may run in parallel.

Memory:

Yes, memory-based tables can only be in RAM and cannot spill to disk. See this article.

Performance Boost:

My advice would be to first measure the performance of your stored procedure to confirm that the temp tables are the source of the unacceptable performance. If you confirm that consider the following simpler options first:

• Only save data to temp tables that you need. Apply filters when inserting data as soon as possible.

• Create columns with the right data types.

• Create indexes as appropriate for your temp tables.

• Drop your temp tables as soon as you no longer need them.

• Make sure that your tempdb database follows best practices. This could be a separate post but here's one resource.

If none of that works and you feel confident that you understand the limits of memory optimized table variables go ahead and test your procedures with them. The previously referenced blog post does mention that as a use case:

As replacement for traditional table variables, and in some cases for #temp tables that are local to a stored procedure. This is particularly useful if there is a lot of tempdb contention in the system.