Why would upgrading from SQL Server 2000 to 2005 result in slower queries?

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

A few things...

1. What Service Pack are you on?
2. Have you applied any additional Hotfixes or CUs?
3. Did you change the db compatibility level from 80 to 90 during the upgrade?

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.

Answer 5

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.

Answer 6

Also, if all else fails, there is a bug in the 2005 optimizer addressed in SP2 cumulative update 6 (and requires applying 2 traceflags).

Answer 7

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.

Answer 8

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