Table Variables And Parallelism

Erik Darling shows your brain on table variables:

Inserts and other modifications to table variables can’t be parallelized. This is a product limitation, and the XML warns us about it.

The select could go parallel if the cardinality estimate were more accurate. This could potentially be addressed with a recompile hint, or with Trace Flag 2453.

Click through to see an example of what Erik means.

Related Posts

Demonstrating Page Latch Waits

Brent Ozar shows us how to generate PAGELATCH_UP and PAGELATCH_EX waits: That’s what it looks like when your system is under heavy contention due to a lot of queries partying in table variables: in each second, each core on this system is spending 48 seconds waiting on PAGELATCH_UP. That’s awesome. What about temp tables instead, you ask? […]

Read More

Forwarded Records Without User Table Heaps

Erik Darling unravels a conundrum: When people think about Heaps and the problems they can cause, they don’t often think of temp tables and table variables as Heaps. Of course, without a clustered index, any table is a Heap. This isn’t an argument for or against indexing temp tables, but while working with a client […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

June 2018
MTWTFSS
« May  
 123
45678910
11121314151617
18192021222324
252627282930