ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 为什么thread数目会超过MAXDOP的限制?

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

原创 Linux操作系统 作者:apgcdsd 时间:2011-08-14 00:49:12 0 删除 编辑

由于各种各样的原因,我们可能需要通过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 Servermanagement studiooutput窗口中看到语句的执行计划了。

 

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的结果。这里我们假设执行上面语句的sessionSPID 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上,而不会作用在整个执行计划上

  

在让我们看看上面的执行计划。该执行计划有3operator,他们分别是: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 streamsParallel 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" />

 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中。

 

更多内容,参考文献:

http://blogs.msdn.com/psssql/archive/2008/02/13/how-it-works-sql-server-per-query-degree-of-parallelism-worker-count-s.aspx

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25175503/viewspace-704803/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-21

  • 博文量
    69
  • 访问量
    75815