但是不知道你们是否注意到了,即使设置了MAXDOP,有时候你在sysprocesses中看到的对应一个SPID的线程数目仍可能大于MAXDOP的值。这是为什么?
让我们用以下的脚本来举例解释这个问题。
createtable [HugeTable1]
(
[Key] int,
[Data] int,
[Pad] char(200),
Constraint [PK1]PRIMARYKEY ([Key])
)
SETNOCOUNTON
DECLARE @i int
BEGINTRAN
set @i = 0
WHILE @i < 250000
BEGIN
INSERT [HugeTable1]Values (@i,@i,NULL)
SET @i = @i + 1
if @i % 1000 = 0
BEGIN
COMMITTRAN
BEGINTRAN
END
END
COMMITTRAN
SELECT [KEY],[DATA],[PAD]INTO [HugeTable2]FROM HugeTable1
ALTERTABLE [HugeTable2]ADDCONSTRAINT [PK2]PRIMARYKEY ([Key])
然后我们执行以下语句这样我们就可以在SQLServer的management studio的output窗口中看到语句的执行计划了。
setstatisticsprofileon
好,让我们运行以下语句,注意这句语句我们使用了一个hint (MAXDOP 2)用来将该语句的并行度限制为2:
select T1.[Key],T1.[Data],T2.[Data]From HugeTable1 T1Join [HugeTable2] T2ON T1.[Key]=T2.[Key]where T1.Data< 100OPTION(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 206050 suspended
56 5936 0 0x00BB 3 CXPACKET 2147483647 01 suspended
56 1252 0 0x00BB 1 CXPACKET 2147483647 02 suspended
56 3508 56 0x0024 0 LATCH_EX 2147483647 03 suspended
56 3580 0 0x0000 0 LATCH_EX 2147483647 04 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的线程
<RelOpNodeId="3"PhysicalOp="Clustered Index Scan"LogicalOp="Clustered Index Scan"….>
<RunTimeInformation>
<RunTimeCountersPerThreadThread="2"ActualRows="100"ActualEndOfScans="1"ActualExecutions="1" />
<RunTimeCountersPerThreadThread="1"ActualRows="0"ActualEndOfScans="1"ActualExecutions="1" />
<RunTimeCountersPerThreadThread="0"ActualRows="0"ActualEndOfScans="0"ActualExecutions="0" />
</RunTimeInformation>
2个执行Nested Loop和clustered index seek的线程
<RelOpNodeId="1"PhysicalOp="Nested Loops"LogicalOp="Inner Join"….>
<RunTimeInformation>
<RunTimeCountersPerThreadThread="2"ActualRows="50"ActualEndOfScans="1"ActualExecutions="1" />
<RunTimeCountersPerThreadThread="1"ActualRows="50"ActualEndOfScans="1"ActualExecutions="1" />
<RunTimeCountersPerThreadThread="0"ActualRows="0"ActualEndOfScans="0"ActualExecutions="0" />
</RunTimeInformation>
同样的线程也用来执行Clustered Index Seek
<RelOpNodeId="4"PhysicalOp="Clustered Index Seek"LogicalOp="Clustered Index Seek"…>
<RunTimeInformation>
<RunTimeCountersPerThreadThread="2"ActualRows="50"ActualEndOfScans="0"ActualExecutions="50" />
<RunTimeCountersPerThreadThread="1"ActualRows="50"ActualEndOfScans="0"ActualExecutions="50" />
<RunTimeCountersPerThreadThread="0"ActualRows="0"ActualEndOfScans="0"ActualExecutions="0" />
</RunTimeInformation>
最后Thread 0(这个线程在所有的operator中都出现)汇拢所有并行的线程并展现最终的结果给客户端程序
<RelOpNodeId="0"PhysicalOp="Parallelism"LogicalOp="Gather Streams"….>
<RunTimeInformation>
<RunTimeCountersPerThreadThread="0"ActualRows="100"ActualEndOfScans="1"ActualExecutions="1" />
</RunTimeInformation>
于是这就解释了为什么你会看到比MAXDOP设置更多的线程数出现在sysprocesses中。
更多参考MSDN:How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s)