為什麼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

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 < 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]

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

Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />

RunTimeInformation>

最後Thread 0（這個執行緒在所有的operator中都出現）匯攏所有並行的執行緒並展現最終的結果給客戶端程式

RunTimeInformation>

於是這就解釋了為什麼你會看到比MAXDOP設定更多的執行緒數出現在sysprocesses中。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-705774/，如需轉載，請註明出處，否則將追究法律責任。

為什麼thread數目會超過MAXDOP的限制？

相關文章