Link to home
Create AccountLog in
Avatar of bbg57
bbg57

asked on

SQL 2005 Database performing very slow

I am running a custom application that uses an MS SQL database. This application has been running for several years now on MS SQL 2000. They are considering upgrading to SQL 2005, and have set up a test server with an evaluation copy of SQL 2005. On the SQL 2005 server I imported the tables from the 2000 server. Running the application utilizing the SQL 2005 server now runs but on several data lookups, it does not return the data. After 10 minutes I have aborted the lookup. On the SQL 2000 server the same lookup takes 3 seconds. Many other lookups work fine, just slower, but manageable. The SQL 2000 server is a much better computer, but the SQL 2005 machine is good also. The SQL 2005 is a dual core 2.2, with scsi drives and 3gb of ram. I expected slower performance, but at least something manageable.  I know have 3 lookups that do not work at all, after 10 minutes I abort them.  

Any help or insight would be grateful. I am not familiar at all with SQL 2005 and what may be needed in place of the SQL 2000 server.

Thanks
Bill
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

I guess you might want to disable parallel query execution on the sql 2005 box.
When you import data, the indexes are not moved over.  Make sure the same indexes exist on the 2005 machine that exist on the 2000 machine.
Avatar of bbg57
bbg57

ASKER

Sorry, can you please tell me where this is done? I am not able to access the server until morning, and I will try this then. I just tried accessing the sevrer and it is off line.

Thanks for a quick response,
Bill
I think you're actually going to be better off doing a backup of your 2000 machine and restoring that to your 2005 box.  Once you do that, just clear out all of the data.  That will ensure that everything you had on your 2000 machine is also present on your 2005 machine.  Otherwise, it is going to be a pain to go script out all of your indexes and recreate them on the 2005 box.

HTH,
Tim
Avatar of bbg57

ASKER

Thank You, this makes sense. I will try this first thing in the morning and let you know. Where is parallel query execution set?

Thanks
Bill
I would hold off on changing something like that until you are sure your indexes are in place.  I would guess the reason angelll suggested it was that he was concerned that your machine wasn't able to use all of the CPUs to process the queries effeciently.  While this may be the case, I would make sure you don't have an index problem before you throttle your CPU usage.
Avatar of bbg57

ASKER

Thank you this did resolve the problem, restoring the database instead of importing.

Thanks
Bill
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account