为什么从 SQL Server 2000 升级到 2005 会导致查询速度变慢？
将数据库从 SQL Server 2000 升级到 SQL Server 2005 是否有任何常见原因会导致查询速度变慢？ 这是来自具有数百个表的 ASP.NET 1.1 应用程序，所有内容都已索引，并且似乎在旧版本上运行良好。
Are there any common reasons why upgrading a database from SQL Server 2000 to SQL Server 2005 would result in slower queries? This is coming from an ASP.NET 1.1 application with hundred of tables, everything is indexed and seems to run well on the older version.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
升级后，您需要做的第一件事是通过完整扫描更新统计信息并重建索引，否则您将得到次优计划
After the upgrade first thing you need to do is update the statistics with full scan and rebuild the indexes or you will get suboptimal plans
您确定所有索引都在升级中幸存下来吗？ 硬件上有什么区别吗？ 您是否使用 SQL Profiler 来确定哪些查询运行速度较慢，以尝试找出问题？
可能有很多事情。 如果没有具体的查询示例和其他信息，我认为任何人都无法提供太多帮助。
Are you certain that all of your indexes survived the upgrade? Are there any differences in hardware? Have you used the SQL Profiler to determine which queries are running slower to try to track down the problem?
There could be a lot of things. Without specific query examples and other information I don't think anyone will be able to help much.
您可能希望通过查看最麻烦的查询的执行计划来重新评估索引。 SQL 2005 查询优化器可能会提出完全不同的执行计划。
您还应该确保更新整个数据库的统计信息。
You may want to re-evaluate your indexes by looking at the execution plans of your most-troublesome queries. The SQL 2005 query optimizer may be coming up with completely different execution plans.
You should also make sure you update statistics on your entire database.
有几件事...
如果您使用服务器端游标，请注意从 SQL Server 2000 升级到 SQL Server 2005 后可能会开始出现一些性能问题。如果您遇到这种情况，有几个修补程序可能会有所帮助。 只需搜索 SQL Server 2005 修补程序和服务器端游标即可。
除此之外，请务必在升级、重建索引和更新统计信息后检查数据库完整性。
A few things...
If you are using server side cursors, be aware that there are some performance problems that can start to surface after upgrading from SQL Server 2000 to SQL Server 2005. If this is your situation, there are a couple of hotfixes that might help. Just search for SQL Server 2005 hotfixes and server side cursors.
Aside from that, always be sure to check db integrity after the upgrade, rebuild indexes and update stats.
我们刚刚从 2000 Ent SP4 升级到 2005 Std 64 位 SP2 后遇到了这个问题，这是一个更强大的服务器（2 个 4 核，32GB RAM），
SELECT 查询在 2000 和 20 多分钟上花费了 2~3 秒（仍然没有）完成）2005年
重新构建所有索引，sp_updatestats，结果相同。 很奇怪，除了NOLOCK之外没有使用任何索引提示
但 2005 盒子上的数据库仍处于 8.0 兼容模式
尽管在我们进行测试时恢复到另一个 2005 盒子，
We just experienced this issue after upgrade from 2000 Ent SP4 to 2005 Std 64 bit SP2, a much more powerful server too (2 4-core, 32GB RAM)
SELECT query took 2~3 secs on 2000 and 20+ minutes (and still not finished) on 2005
Re-built ALL indexes, sp_updatestats, same results. Very strange, no index hints were used except NOLOCK
The databases remained in 8.0 compatible mode on the 2005 box though
Restoring to another 2005 box as we speak to test
确保您正在运行的查询和存储过程没有使用任何索引提示。 正如其他人提到的，优化器在 2000 年到 2005 年之间发生了变化，因此这些提示可能不再有用。
Make sure that the queries and stored procedures you're running are not utilizing any index hints. Like everyone else has mentioned, the optimizer has changed between 2000 and 2005, so these hints may no longer be useful.
另外，如果所有其他方法均失败，则 SP2 累积更新 6 中解决的 2005 优化器中存在一个错误（并且需要应用 2 个跟踪标志）。
Also, if all else fails, there is a bug in the 2005 optimizer addressed in SP2 cumulative update 6 (and requires applying 2 traceflags).
你没有说你正在运行哪个版本。
但是，如果您刚刚从 2000 标准版或企业版迁移到 2005 Express 版； Express 版本仅使用一个处理器。 上周我就遇到了这种事； 我的一个查询从本来就很慢的 1.5 秒变成了 55 秒！ 我运行了查询计划，唯一的区别是并行操作。 无法相信速度差异。
You didn't say which edition you are running.
But if you just moved from a 2000 Standard or Enterprise edition to a 2005 Express edition; The Express edition only uses one processor. I jsut had this happen to me last week; one of my queries went from an already slow 1.5 seconds to 55 seconds! I ran the query plan, and the only difference was the parallel operations. Couldn't beleive the speed difference.