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

 

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

 

下面是在网上找到的一些测试资料：

http://topic.csdn.net/t/20040115/20/2667829.html 

 

经过多次重装实验和对比,基本找到原因了!!!

是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 的问题。

 

 

 

 

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

转载于:http://blog.itpub.net/35489/viewspace-681946/