Issue: Using the detach/attach SQL database from a SQL 2000 SP4 instance to a much beefier SQL 2005 SP2 server.
Run reindex, reorganize and update statistics a couple of times, but without any success.
Queries on SQL 2000 took about 1-2 sec. to complete, now the same queries take 2-3 min on the SQL 2005 (and even 2008 - tested it there also).
Have looked at the execution plans and the overall percent matches or are alike on each server.
You need to go back to the 2000 server, run a full backup and then restore that to 2005.
Without any information about the schema, the query or the size of the tables we really cannot guess anything. If you can post the output of the SET STATISTICS TIME ON and SET STATISTICS IO ON on 2005 vs. 2000 we could start a discussion...
The database upgrade from 2000 to 2005 occurs as soon as the database is placed online on the 2005 system, is the same no matter the actual operation (restore, attach).
I'd also look into the options that are set on connections on the two servers. This can cause performance problems because these details affect query plan re-use. Run a Profiler trace on both servers and compare the existing connections details. A login with a different language from the rest, for example, can't use the same query plans. There's stuff in Books Online about which options affect this.
Just to follow up on this, the "solution" to us was a SQL2000 instance installed along with the SQL 2005.
We acctually also tried attaching the SQL 2000 database to a nother server running the SQL 2008, with exact same bad result as the SQL 2005 :-(
So as far as connection, hardware, IO and that kind of issues, the SQL2000 instance runs perfect on the same server as the SQL 2005. I would guess that rules out the hardware questions?
A few years back, I had a client that was having some performance issues with an application. A colleague of mine and I isolated a poorly performing database instance in their staging environment as one of the causes.
Their production environment was using a Sql Server 2000 instance with a database that had been around for 4 or 5 years; their staging database server was running Sql Server 2005 on which a backup of their production database was restored. Our testing showed the staging server was consistently performing significantly worse than production.
We eliminated hardware differences by installing instances of Sql Server 2000 and 2005 on a new machine and restoring the same backup to both. Having verified that a similar performance disparity existed between the instances on the new machine, we performed consistency checks and rebuilt indexes to no effect. We found that creating a new database in the 2005 instance with an identical schema and migrating only the data eliminated the performance disparity. Based on circumstances, we decided there was no need to pursue the issue further.