Slow (none) performance on SQL 2005 after attaching a SQL 2000 database
Q: Using a detach / attach SQL database from a SQL 2000 SP4 instance to a much stronger SQL Server 2005 SP2.
Running reindex, reorganizing and updating statistics a couple of times, but without any success.
SQL 2000 queries took about 1-2 seconds. to complete, now the same queries take 2-3 minutes on SQL 2005 (and even 2008 - tested it there too).
Looked at execution plans and overall percentage matches or similar for each server.
source to share
Without any information about the schema, query, or table size, we can't guess anything. If you can post the conclusion SET STATISTICS TIME ON
and SET STATISTICS IO ON
in 2005 versus 2000 we could start a discussion ...
Updating the database from 2000 to 2005 occurs as soon as the database is placed on the Internet in the 2005 system, the same regardless of the actual operation (restore, attach).
source to share
I will also look at the parameters set for connections on the two servers. This can cause performance issues because this data affects query plan reuse. Run Profiler trace on both servers and compare existing connection data. For example, a login with a different language from the rest cannot use the same query plans. Books Online has material on which options affect this.
source to share
To keep track of this, the "solution" for us was the SQL2000 instance installed with SQL 2005.
We also tried to bind the SQL 2000 database to a narrower server running SQL 2008 with the same bad result as SQL 2005: - (
So, in terms of connectivity, hardware, I / O and such issues, the SQL2000 instance runs fine on the same server as SQL 2005. I would guess that eliminates hardware issues?
source to share
Several years ago, I had a client who had performance issues with an application. My colleague and I have isolated the underperforming database instance in our staging environment as one of the reasons.
Their production environment was running 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 and a backup of their production database was restored. Our testing has shown that the staging server consistently performs significantly worse than production.
We fixed the hardware differences by installing Sql Server 2000 and 2005 instances on a new computer and restoring the same backup for both. After confirming that there was a similar performance mismatch between the new machine instances, we ran consistency checks and rebuilt the indexes. We found that creating a new database in instance 2005 with the same schema and migrating only the data removed the performance inconsistency. Based on the circumstances, we decided that there was no need to continue this problem.
source to share