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!

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

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)