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

在SQL Server 2000中设置OPTION (MAXDOP 1) 性能提高问题