SQL2000迁移到SQL05后PROC执行超慢

Question

谢谢HAIWER。你说的这个肯定是首先就检查过了。

Answer 1

做以下调整试试.1. 关闭cpu并行(如果有多顆CPU的话)2. 将tempdb放到另外一个盘上.

Answer 2

1,如果你的2005还是装在原来2000的那台机器上，如果硬件资源没有得到改善，sql2005自身耗用的资源大了，应用程序耗用的资源就小了哈，特别使用大数据量的临时表。
2，给临时表建索引试试

Answer 3

试试把该重建, 可重新编译的重新弄一次USE 你的库名;
GO-- 1. 所有表的 索引 rebuild 一次
EXEC sp_msforeachtable N'
ALTER INDEX ALL
ON ?
REBUILD
';
GO-- 2. 所有的存储过程记为重新编译
EXEC sp_msforeachtable N'
EXEC sp_recompile N''?''
';
GO-- 3. 所有的视图标记为重新编译
DECLARE
@sql nvarchar(max);SET @sql = (
SELECT
N'EXEC sp_refreshview N' + QUOTENAME(SCHEMA_NAME(schema_id) + N'.' + name) + N';'
+ N'EXEC sp_recompile N' + QUOTENAME(SCHEMA_NAME(schema_id) + N'.' + name) + N';'
FROM sys.views
WHERE is_ms_shipped = 0
FOR XML PATH(''), TYPE
).value('/', 'nvarchar(max)');
EXEC(@sql);
GO

Answer 4

还是不行的话, 使用事件探查器(profile) 把性能不好的查询抓出来, 比较 2000 和 2005 下的执行计划差异来寻找原因

Answer 5

TO:邹建       重建, 可重新编译的重新弄一次。（这个试过不行）
而且检查了一个存储过程里有关系的几个表，索引都一样。 
TO：茶杯     环境肯定多少有一点点不一样，并不大。但不能执行存储过程的效率慢100多倍这种地步的。这是同一个库，没有任何变化。
TO：landi    你这方法我试试如果行，那05比2000麻烦多了。谢谢大家抽时间帮助我。

Answer 6

而且检查了一个存储过程里有关系的几个表，索引都一样。 
-------------------------------------------- 索引 rebuild 了吗? 定义一样不能说明问题啊

Answer 7

rebuild了，没作用。我上上个星期也发过这个问题的帖子。用优化顾问基本上能解决这个问题，但是有临时表的解决不了。
现在用优化顾问解决就是，在同样的库里，同样的存储过程里，同样的表上建立更多的索引和统计信息来到达2000里的速度。
这是用优化顾问优化没有临时表的PROC的办法。
还有一个方法也能解决，就是需要改PROC里的WHERE条件。用OR换掉CASE WHEN。也能提升到2000里的速度。
现在是不想改语句，因为要改的存储过程太多了，上百个都需要改。
用PROFILER也能跟踪到是哪条语句慢，就是最后用WHERE CASE WHEN 那段超时。已经有办法解决了，但是想找找什么原因导致的。
别的没有临时表的，也包含CASE WHEN 优化完了执行速度也OK。
如果是这样，2000升级到05后就实在没什么意义了，PROC执行慢100多倍，要想不慢就要手动改上百个存储过程。
传说中的“向下兼容”。
找这个问题2个多星期了，网上能找到的方法几乎全试过了。头疼，还得继续找。。

Answer 8

还有 PROC 里分为4段查询。
前三段查询分别生成#A,#B,#C
第四段查询返回#A#B#C联合的查询结果。
跟踪就是这句运行的时间占用了最长时间。
把这句的WHERE注释掉后，速度也提升到了2000中的速度。
其中用到了多句
CASE WHEN @tid=-1 THEN o1.TBID ELSE @TBID END
这类条件
最后有个order by a1,a2,a3,a4,a5。
如果改CASE WHEN也能达到效果。
2005中对CASE WHEN在语句里的执行步骤发行了变化吗？  

Answer 9

完整条件是
CASE WHEN @tid=-1 THEN o1.TBID ELSE @TBID END = @TBID
还是
o1.TBID=CASE WHEN @tid=-1 THEN o1.TBID ELSE @TBID END 
还是其他？对于距离的两种情况，貌似确实case when 会影响索引的效用，应该改为
(@tid=-1 and o1.TBID = @TBID or @tid<>-1)
和
(o1.TBID=@TBID or @tid=-1)
最好改为
if @tid=-1
begin
   select ... and o1.TBID = @TBID ...  
end
else 
begin
   select ...  ...   ---不要o1.TBID的条件
end
和
if @tid=-1
begin
   select ...  ...  ---不要o1.TBID的条件
end
else 
begin
   select ... and o1.TBID = @TBID ...   end
end

Answer 10

谢谢各位。看来是解决不了了。
如果只有把原来的存储过程都拿出来改语句，我就不发这个帖子了。
看来SQL2005还是用作新项目好，迁移问题太多。
迁移后要手改几百个过程，真的头疼。

Answer 11

谢谢大家抽出时间来帮助我解答这个问题。
前几天参加了一个SQL2008的沙龙，主讲的是微软金牌讲师 孙巍老师。
对于这个问题我们也咨询了他，他说这种迁移问题只有手动去解决优化了。并不是每个迁移都会碰到这种问题，也是SQL语句写法的习惯问题。
SQL2000可以支持没问题，05就不支持了，查询引擎变了。