How Table Variables Mess With Parallelism

This Is A Parallel Query

Hi There

This Is A Temp Table

This Is A Parallel Insert Into A Temp Table

Sort of asterisk

(The Insert isn’t parallel, but the part of the plan leading up to it is)

This Is A Table Variable

This A Serialized Insert Into A Table Variable

Yeah Nah®

The XML Tells Us Why

This Is A Parallel Query Against A Temp Table

I Feel Pretty

The Estimate Tells Us Why

Good Guess®

This Is A Serial Query Against A Table Variable

Face Gym

The Estimate Tells Us Why

Bad Guess®

Two Different Limitations

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.

Temp Tables don’t have those limitations, however they do incur some overhead. Accurate table and column cardinality aren’t free.

There isn’t much more of a point to this post except to consolidate this stuff in one place where it’s not all side notes.

Thanks for reading!

Previous Post
Stupid T-SQL Tricks
Next Post
Demoing Latch Waits with Stupid Tricks

6 Comments. Leave new

  • David Forck
    June 7, 2018 8:44 am

    Yup, I’ve ran into this exact issue. Changed the table variable into a temp table and BAM! Performance issues gone.

  • What are table variables good at? It wasn’t cheap to implement them in SQL Server. There were meetings and marketing chicks and sales jocks and all the rest of it. Did they just pooch the implementation?

    • Erik Darling
      June 7, 2018 5:40 pm

      They have their uses for high frequency/low row volume processes, as well as tasks where you don’t need to do anything particularly relational with them. For example, Ola Hallengren uses them to store lists of databases, tables, and indexes to iterate over.

      • Also functions: you can pass in a table variable to a function and do stuff with it (joins etc), but you can’t do that with a temp table – wreaks havoc with performance but as at right now temp tables still cannot be used within functions…

Menu
{"cart_token":"","hash":"","cart_data":""}