读取表变量的查询是否可以在SQL Server 2008中生成并行执行计划? [英] Can queries that read table variables generate parallel exection plans in SQL Server 2008?

问题描述

首先，来自 BOL :

修改表的查询不会生成并行查询执行计划.修改非常大的表变量或复杂查询中的表变量时，性能可能会受到影响.在这种情况下，请考虑使用临时表.有关更多信息，请参见CREATE TABLE(Transact-SQL).读取表变量而不修改它们的查询仍可以并行化.

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.

这似乎很清楚.可以对 read 表变量进行查询，而无需对其进行修改，就可以对其进行并行化.

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

，然后在 SQL Server存储引擎，这是一个享有盛名的消息来源.

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.

Sunil是对BOL的重新解释:INSERT，还是FROM子句中的表变量是否阻止了并行性?如果是这样，为什么?

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.

谢谢！

推荐答案

好，我对表变量有一个并行选择，但 not

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

我已经将其匿名化了:

• BigParallelTable的行宽为900k
• 出于遗留原因，BigParallelTable进行了部分非规范化(我将对其进行修复，以后再保证)
• BigParallelTable经常生成并行计划，因为它不理想且昂贵"
• SQL Server 2005 x64，SP3，内部版本4035、16个内核

查询+计划:

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

现在，考虑一下，一个表变量几乎总是一个表扫描，没有统计信息，并且被假定为一行估计的行数= 1"，实际.. = 3".

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".

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

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

这篇关于读取表变量的查询是否可以在SQL Server 2008中生成并行执行计划?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！