SQL Server 2005, 2008 performance

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

Hello

After migrating our database from SQL Server 2000 to SQL Server 2008, we have noticed a major decline in performance. Running a query on a database on SQL Server 2008 takes twice as much time as that running on an exact database on SQL Server 2000.

Are there any configuration setups we need to perform after the migration to help improve our performance? Can anyone advise?

Thanks a lot.

Posted 23-Nov-11 0:18am

deadwood88

Updated 23-Nov-11 0:20am

v2

Add a Solution

Comments

Dylan Morley 23-Nov-11 6:47am

Does the machine running 2008 have any significant hardware differences to the machine running 2000?

RAM \ CPU's \ DISK etc

Have you changed the disk RAID at all? Data \ Log files \ tempdb disk locations?

If the database is *exactly* the same (e.g. restored from full backup) , you need to consider these kind of environment details

deadwood88 23-Nov-11 6:51am

No hardware differences whatsoever.

I havent changed disk RAID or the locations of Data\Log files\tempdb. should i do something about them?

any help would be highly appreciated.

Dylan Morley 23-Nov-11 9:25am

How have you done this upgrade? Is it on 2 different machines?

e.g MACHINE1 running SQL2000, MACHINE2 running SQL2008... -> restored all database across from machine 1 to 2?

Or have you done some sort of side-by-side installation?

On the machine running 2008, what sort of CPU stats are you seeing when running your query. Is it maxing out?

How much RAM is the SQL2008 process configured to use? The default option should be dynamic memory allocation, which means it will initially allocate as much memory to the SQL process as possible

http://msdn.microsoft.com/en-us/library/ms178145.aspx

Can you confirm the SQL2008 process has enough RAM allocated? If this is a side-by-side installation, there might be some RAM issues ?

deadwood88 23-Nov-11 9:43am

Both SQL2000 and SQL2008 are running on the same server and have the exact same database. When we want to insert data, SQL2008 shows much slower performance. This observation was noticed in our production facility.

I'm currently trying to simulate the same scenario on a local server here with 2GB of RAMs. I have both SQL2000 and SQL2005 installed on the same server and having the exact database. Queries on SQL2005 are also slower than those running on SQL2000. (im working with sql2005 rather than 2008 locally because it also showed slower performance in the production facility compared to 2000, and because i dont have 2008 available).

I havent configured memory allocation so its safe to presume that it is dynamic.

Dylan Morley 23-Nov-11 10:11am

Did you perform the database upgrade process as per MSDN? (Detach from 2000, copy files to new location (2008 specific), perform CREATE DATABASE using copied files)

http://msdn.microsoft.com/en-us/library/ms189625.aspx

deadwood88 23-Nov-11 10:21am

No i havent. I will try the instructions in the link and report back with the results should any improvement occur.

Thanks a lot for your efforts.

Mehdi Gholam 23-Nov-11 7:19am

Usually you get a performance boost of up to 20% upgrading.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

FastEvo8 · Answer 1 · 2011-12-06T06:05:00

Solution 1

Did you update the statistics?
How many temp files do you have?

Posted 6-Dec-11 6:05am

FastEvo8