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

SQLServer走起 2020-07-08

661

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

但是不知道你们是否注意到了，即使设置了MAXDOP，有时候你在sysprocesses中看到的对应一个SPID下的kpid数目（线程数目）仍可能大于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
       INNER 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中。

最后总结：

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

文章转载自：

http://blogs.msdn.com/b/apgcdsd/archive/2011/03/01/thread-maxdop.aspx

文章经作者授权转载，版权归原文作者所有

图片来源于网络，侵权必删！

技术

文章转载自SQLServer走起，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论