Can queries that read table variables generate parallel exection plans in SQL Server 2008?

Question

First, from BOL:

Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.

That seems clear enough. Queries that read table variables, without modifying them, can still be parallelized.

But then over at SQL Server Storage Engine, an otherwise reputable source, Sunil Agarwal said this in an article on tempdb from March 30, 2008:

Queries involving table variables don't generate parallel plans.

Was Sunil paraphrasing BOL re: INSERT, or does the presence of table variables in the FROM clause prevent parallelism? If so, why?

I am thinking specifically of the control table use case, where you have a small control table being joined to a larger table, to map values, act as a filter, or both.

Thanks!

Shank

Answer 1

OK, I have a parallel select but not on the table variable

I've anonymised it and:

• BigParallelTable is 900k rows and wide
• For legacy reasons, BigParallelTable is partially denormalised (I'll fix it, later, promise)
• BigParallelTable often generates parallel plans because it's not ideal and is "expensive"
• SQL Server 2005 x64, SP3, build 4035, 16 cores

Query + plan:

DECLARE @FilterList TABLE (bar varchar(100) NOT NULL)

INSERT @FilterList (bar)
SELECT 'val1' UNION ALL 'val2' UNION ALL 'val3'

--snipped

SELECT
     *
FROM
    dbo.BigParallelTable BPT
    JOIN
    @FilterList FL ON BPT.Thing = FL.Bar

StmtText
  |--Parallelism(Gather Streams)
       |--Hash Match(Inner Join, HASH:([FL].[bar])=([BPT].[Thing]), RESIDUAL:(@FilterList.[bar] as [FL].[bar]=[MyDB].[dbo].[BigParallelTable].[Thing] as [BPT].[Thing]))
            |--Parallelism(Distribute Streams, Broadcast Partitioning)
            |    |--Table Scan(OBJECT:(@FilterList AS [FL]))
            |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[BigParallelTable].[PK_BigParallelTable] AS [BPT]))

Now, thinking about it, a table variable is almost always a table scan, has no stats and is assumed one row "Estimated number of rows = 1", "Actual.. = 3".

Can we declare that table variables are not used in parallel, but the containing plan can use parallelism elsewhere? So BOL is correct and the SQL Storage article is wrong

Casuistry

Awesome. Did you compare that to a plan generated by #FilterList, rather than @FilterList? – Shank

I'd put your conclusion at the top. Thanks for testing this. I also found this: social.msdn.microsoft.com/Forums/en-SG/sqldatabaseengine/thread/…. Quoting Boris B: "Read-only queries that use table variables can still be parallelized. Queries that involve table variables that are modified run serially. We will correct the statement in Books Online." – Shank

You can get a parallel SELECT on a table variable. The row count is maintained in sys.partitions but you need to use OPTION (RECOMPILE) to get it to take account of this. – Tress

@Martin Smith: old answer, MS docs saying different things, probably out of date now (yes, I linked it...) – Casuistry

Answer 2

Simple Example showing a parallel operator on a table variable itself.

DECLARE @T TABLE
(
X INT
)
INSERT INTO @T
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1,master..spt_values v2;

WITH E8(N)
     AS (SELECT 1
         FROM   @T a,
                @T b),
     Nums(N)
     AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
         FROM   E8)
SELECT COUNT(N)
FROM   Nums
OPTION (RECOMPILE)  

Tress

Answer 3

[Answering my own question here, so I can present the relevant quotes appropriately....]

Boris B, from an thread at MSDN SQL Server forums:

Read-only queries that use table variables can still be parallelized. Queries that involve table variables that are modified run serially. We will correct the statement in Books Online. (emp. added)

and:

Note that there are two flavors of parallelism support:

A. The operator can/can not be in a parallel thread

B. The query can/can not be run in parallel because this operator exists in the tree.

B is a superset of A.

As best I can tell, table variables are not B and may be A.

Another relevant quote, re: non-inlined T-SQL TVFs:

Non-inlined T-SQL TVFs...is considered for parallelism if the TVF inputs are run-time constants, e.g. variables and parameters. If the input is a column (from a cross apply) then parallelism is disabled for the whole statement.

Shank

Answer 4

My understanding is that parallelism is blocked on table variables for UPDATE/DELETE/INSERT operations, but not for SELECTs. Proving that would be a lot more difficult than just hypothesizing, of course. :-)

Altissimo

I'm inclined to believe it isn't blocked on SELECT. I was hoping someone might know definitively one way or the other. Time for some testing! – Shank