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