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]))

现在，考虑一下，表变量几乎总是表扫描，没有stats 并假设一行“估计行数 = 1”，“实际.. = 3”。

我们可以声明表变量不并行使用，但包含计划可以在其他地方使用并行性吗？所以 BOL 是正确的，而 SQL Storage 文章是错误的

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

回复收藏 0 原文

腻橙味 2024-08-16 13:25:34

显示表变量本身的并行运算符的简单示例。

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)

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)

Plan

回复收藏 0 原文