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/