为什么thread数目会超过MAXDOP的限制？

最新推荐文章于 2023-06-15 16:49:10 发布

apgcdsd

最新推荐文章于 2023-06-15 16:49:10 发布

阅读量1.6k

点赞数

文章标签： thread nested loops sql server join semaphore

本文链接：https://blog.csdn.net/apgcdsd/article/details/6416704

版权

由于各种各样的原因，我们可能需要通过sp_configure来设置最大并行度，也就是Max Degree of Parallelism (MAXDOP)。常见的场景包括由于并行引起的死锁，由于并行造成的CXPACKET类型的等待，以及由于并行导致的RESOURCE_SEMAPHORE类型的等待。

但是不知道你们是否注意到了，即使设置了MAXDOP，有时候你在sysprocesses中看到的对应一个SPID的线程数目仍可能大于MAXDOP的值。这是为什么？

让我们用以下的脚本来举例解释这个问题。

create table [HugeTable1]

(

[Key] int,

[Data] int,

[Pad] char(200),

Constraint [PK1] PRIMARY KEY ([Key])

)

SET NOCOUNT ON

DECLARE @i int

BEGIN TRAN

set @i = 0

WHILE @i < 250000

BEGIN

INSERT [HugeTable1] Values (@i,@i,NULL)

SET @i = @i + 1

if @i % 1000 = 0

BEGIN

COMMIT TRAN

BEGIN TRAN

END

COMMIT TRAN

SELECT [KEY],[DATA],[PAD] INTO [HugeTable2] FROM HugeTable1

ALTER TABLE [HugeTable2] ADD CONSTRAINT [PK2] PRIMARY KEY ([Key])

然后我们执行以下语句这样我们就可以在SQL Server的management studio的output窗口中看到语句的执行计划了。

set statistics profile on

好，让我们运行以下语句，注意这句语句我们使用了一个hint (MAXDOP 2)用来将该语句的并行度限制为2：

select T1.[Key],T1.[Data],T2.[Data] From HugeTable1 T1 Join [HugeTable2] T2 ON T1.[Key] =T2.[Key] where T1.Data < 100 OPTION (MAXDOP 2)

你可以看到执行计划如下所示：

|--Parallelism(Gather Streams)

|--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[Key]))

|--Parallelism(Repartition Streams, RoundRobin Partitioning)

| |--Clustered Index Scan(OBJECT:([AdventureWorks2008].[dbo].[HugeTable1].[PK1] AS [T1])

WHERE:([AdventureWorks2008].[dbo].[HugeTable1].[Data] as [T1].[Data]<(100)))

|--Clustered Index Seek(OBJECT:([AdventureWorks2008].[dbo].[HugeTable2].[PK2] AS [T2]),

SEEK:([T2].[KEY]=[AdventureWorks2008].[dbo].[HugeTable1].[Key] as [T1].[Key]) ORDERED FORWARD)

让我们将上面的语句在一个循环里不断的执行。然后在Management Studio里打开一个新的查询窗口并且查看sysprocesses的结果。这里我们假设执行上面语句的session是SPID 56。

你可能会看到如下结果：

spid kpid blocked waittype waittime lastwaittype cpu physical_io ecid status

56 5640 0 0x00BB 3 CXPACKET 66653 20605 0 suspended

56 5936 0 0x00BB 3 CXPACKET 2147483647 0 1 suspended

56 1252 0 0x00BB 1 CXPACKET 2147483647 0 2 suspended

56 3508 56 0x0024 0 LATCH_EX 2147483647 0 3 suspended

56 3580 0 0x0000 0 LATCH_EX 2147483647 0 4 runnable

这里我们明显看到，SQL Server使用了5个线程来执行这个query。这就和MAXDOP 2的这个hint相冲突了。

根本的原因在于MAXDOP的限制只会作用在执行计划的每个operator上，而不会作用在整个执行计划上。

在让我们看看上面的执行计划。该执行计划有3个operator，他们分别是：Clustered Index Scan， Clustered Index Seek 和 Nested Loops。

因此我们就有：

- 2个线程 (受到MAXDOP hint的限制) 用来执行Clustered Index Scan，

- 2个线程 (受到MAXDOP hint的限制) 用来执行Nested Loop Join并同时执行Clustered Index Seek 来和 Clustered Index Scan的结果做join。因此没有专门用来执行Clustered Index Seek的线程，

- 1 个线程用来做parallel gather streams。Parallel gather streams 会汇拢所有并行执行的Nested Loop的输出结果。也就是说这个线程是一个并发执行计划中的同步线程（在XML的执行计划中，这个线程用0号线程来标示）。

我们还可以用XML形式的执行计划来进一步观察这个query的线程使用情况。

2 个执行 Clustered Index Scan的线程

</RunTimeInformation>

2个执行 Nested Loop 和 clustered index seek的线程

</RunTimeInformation>

同样的线程也用来执行Clustered Index Seek

</RunTimeInformation>

最后Thread 0 （这个线程在所有的operator中都出现）汇拢所有并行的线程并展现最终的结果给客户端程序

</RunTimeInformation>

于是这就解释了为什么你会看到比MAXDOP设置更多的线程数出现在sysprocesses中。

为什么thread数目会超过MAXDOP的限制？

“相关推荐”对你有帮助么？