Sample Header Ad - 728x90

Should I use tempdb or memory optimized table variable?

3 votes
1 answer
2199 views
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 (https://dba.stackexchange.com/questions/16385/whats-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 (https://dba.stackexchange.com/questions/16385/whats-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.
Asked by deemon (33 rep)
Feb 21, 2017, 09:28 AM
Last activity: Dec 9, 2017, 11:06 PM