This Is A Parallel Query
This Is A Temp Table
|
1 |
CREATE TABLE #Posts (OwnerUserId INT); |
This Is A Parallel Insert Into A Temp Table
(The Insert isn’t parallel, but the part of the plan leading up to it is)
This Is A Table Variable
|
1 |
DECLARE @Posts TABLE (OwnerUserId INT); |
This A Serialized Insert Into A Table Variable
The XML Tells Us Why
|
1 2 |
QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" |
This Is A Parallel Query Against A Temp Table
The Estimate Tells Us Why
This Is A Serial Query Against A Table Variable
The Estimate Tells Us Why
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!
6 Comments. Leave new
Yup, I’ve ran into this exact issue. Changed the table variable into a temp table and BAM! Performance issues gone.
When you solve a problem, do you yell “STICK A FORCK IN IT!”?
I would.
I will from now on!
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?
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…