在SQL Server 2000中設定OPTION (MAXDOP 1) 效能提高問題

Windows 2003 ,      SQL Server 2000 SP4  .   

執行下面的語句需要近 20 秒。 但是在後面加入 OPTION (MAXDOP 1)    或者 OPTION (MAXDOP 2)   ..... 到 OPTION (MAXDOP 9) ， 速度變得非常快， 執行時間都小於1秒， 但是 OPTION (MAXDOP 10) 的時候就變回原來的20秒左右，  當然快和慢時候的執行計劃也不一樣，      慢的時候關鍵解析處是 Merge Join / Right Anti Semi Join ,  快的時候為 Nested Loops/Inner Join   ,  Hash Match/Left Anti Semi Join  .   

 

SELECT 
'004' AS MSGFN,
'OB20031120' AS AENNR,
b.climat AS IDNRK,
'L' AS POSTP,
CONVERT(INT,b.SOITMNUM) AS POSNR,
b.ordqty AS MENGE_C ,
'X' AS SANFE
FROM purchaseordersitem a (nolock),purchaseordersitemdetail b (nolock)
WHERE  a.mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
AND  a.ordnum='20058439390'
and a.itmnum='1'
AND  a.mesgid= b.mesgid
AND  a.ordnum=b.ordnum
and a.itmnum=b.itmnum
and a.highlevel IS NOT NULL
AND  not exists (select * from purchaseorderscomponent(nolock)
     where climat=b.climat
     and rdnum='20058439390'
     and mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
                                        and itmnum='1'
     and ((ATTRIBNAME='InSystemBox'and ATTRIBVALUE='N')
                                               or (ATTRIBNAME='PartType'and ATTRIBVALUE='OVERPACK')
                                             )
                 )
ORDER BY POSNR

 

 

SELECT 
'004' AS MSGFN,
'OB20031120' AS AENNR,
b.climat AS IDNRK,
'L' AS POSTP,
CONVERT(INT,b.SOITMNUM) AS POSNR,
b.ordqty AS MENGE_C ,
'X' AS SANFE
FROM purchaseordersitem a (nolock),purchaseordersitemdetail b (nolock)
WHERE  a.mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
AND  a.ordnum='20058439390'
and a.itmnum='1'
AND  a.mesgid= b.mesgid
AND  a.ordnum=b.ordnum
and a.itmnum=b.itmnum
and a.highlevel IS NOT NULL
AND  not exists (select * from purchaseorderscomponent(nolock)
     where climat=b.climat
     and rdnum='20058439390'
     and mesgid='0a5dc698-6cc4-49bd-ab0b-e33121f6ec44'
                                        and itmnum='1'
     and ((ATTRIBNAME='InSystemBox'and ATTRIBVALUE='N')
                                               or (ATTRIBNAME='PartType'and ATTRIBVALUE='OVERPACK')
                                             )
                 )
ORDER BY POSNR
OPTION (MAXDOP 1)  

 

-------------------------------------------------------------

 

下面是在網上找到的一些測試資料：

 

 

經過多次重灌實驗和對比,基本找到原因了!!!

是Windows   2000   的問題. 

對於上述儲存過程,反覆比較執行結果如下.
--------------------------------------------------------------------

重灌win2000,   不打補丁,安裝SQL   SERVER   2000,   測試100,000記錄,   28秒.

win2000作業系統打補丁

打   補丁   sp1   ,還是28-30秒左右.

打   補丁   sp2   ,27-30秒之間.       (這三種情況下,CPU   大約55%左右)

打   補丁   sp3   ,   或   sp4   ,   則效能急劇   下降,   需要10分7秒,   CPU   使用5%以下.

解除安裝SP3   或SP4   到(   SP1   ,   SP3   ,   或沒有補丁)   效能立即恢復如前.

因此推斷:

win2000   在對稱SMP機器上,或者在此類似架構的機器上(XEON   的超執行緒對於作業系統,就象2個CPU   ),   對於SMP的排程,   沒有補丁,   SP1   ,   SP2   時,還是很正常的.但到補丁SP3   ,   SP4可能出現了問題. 而SQL   SERVER   2000在SMP類計算機上,   可能使用了作業系統的特殊內部功能,這些功能就是與SMP相關的. 可能是內部CPU排程等原因造成CPU沒有被很好利用.ORACLE公司,不知道這些特殊功能,自然就無法使用, 因此ORACLE也就不會出現效能下降問題.   而在普通相容機上,單CPU   ,   P3或P4   ,   根本不會使用這些特殊統能.所以沒有效能問題暴露.

關於沒有補丁和只使用SP1   ,   或   SP2   CPU   使用55%左右,   我想可能是磁碟   IO限制.   因為儲存過程在 SQL   SERVER   內部執行,   經過內部編譯,   已經不完全是CPU密集型,   而是磁碟io密集型,所以不需要消耗太多CPU,   ,   CPU還需要等待IO.

 

----------------------

 

我的作業系統是 Windows 2003 ， 所以可能類似windows2000打了SP4補丁的情況 。 準備將幾張表匯入到sql 2008 測試一下，  看看到底是widows 的問題， 還是SQL Server 2000 的問題。