Performance issues after detach, move and attach

Question

Performance issues after detach, move and attach

Atat Rowcount

Default port

Points: 1453
More actions
January 12, 2009 at 4:28 am

#198133

Hi
Here's an unusual one. I have a SQL2000 database of about 100GB, which I have detached from Server1, copied the MDF and LDF to Server2, and then attached the DB on Server2. Server2 is significantly higher spec than Server1, which is a much older box.
The database crawls on Server2. A query that takes one second on Server1, takes almost two minutes on Server2. I have rebuilt all the indexes on Server2 and I have run sp_updatestats.
What have I missed?
Brian

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Do you have the same query plan in both servers? Are there problems with other databases on the second server? Is there any modification with the workload on the database that was moved to the new server? Do the server have the same service packs?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Atat Rowcount Default port Points: 1453 More actions · Answer 2

Hi

Do you have the same query plan in both servers? -> No, but I presumed that was because I had rebuilt the indexes and ran sp_updatestats.

Are there problems with other databases on the second server? -> No, they are fine.

Is there any modification with the workload on the database that was moved to the new server? -> No

Do the server have the same service packs? -> Yes

Brian

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Let’s try to figure out why there are different query plans. Are there any different set options that are being used when you query the database in the second server? Did you load more data after you attached the database to the second server? Also do you see this problem only with particular table\view or just a set of tables\views?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Atat Rowcount Default port Points: 1453 More actions · Answer 4

Aha - found it, and thank you very much. When checking the service packs, I accidentally ran the check in the same window twice 😉

So yes, the new server had sql 2000 with no service packs applied! Installed SP4 and problems went away.

Thanks for the help!

Brian

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

Thanks for the update and good information. I hadn't thought that the SP would improve performance (or lack of one ruin it), but that's a handy piece of information.