为什么thread数目会超过MAXDOP的限制?
由于各种各样的原因,我们可能需要通过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
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的线程
<RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan"….>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="2" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
2个执行 Nested Loop 和 clustered index seek的线程
<RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" ….>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="2" ActualRows="50" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="50" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
同样的线程也用来执行Clustered Index Seek
<RelOp NodeId="4" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek"…>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="2" ActualRows="50" ActualEndOfScans="0" ActualExecutions="50" />
<RunTimeCountersPerThread Thread="1" ActualRows="50" ActualEndOfScans="0" ActualExecutions="50" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
最后Thread 0 (这个线程在所有的operator中都出现)汇拢所有并行的线程并展现最终的结果给客户端程序
<RelOp NodeId="0" PhysicalOp="Parallelism" LogicalOp="Gather Streams"….>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="100" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
于是这就解释了为什么你会看到比MAXDOP设置更多的线程数出现在sysprocesses中。
更多内容,参考神文: