My application's response time has gone down from 20 sec to 2 sec after upgrading from SQL Server 2000 to SQL Server 2005.

Question

My application's response time has gone down from 20 sec to 2 sec after upgrading from SQL Server 2000 to SQL Server 2005.

sandeephughes

SSC Journeyman

Points: 78
More actions
November 18, 2008 at 3:57 am

#196158

Hi,
My application's response time has reduced form nearly 20 sec to around 2 sec after upgrading from SQL Server 2000 to SQL Server 2005.
Could anyone help me how such a big difference in perfomance is seen.
I monitored that full scans/sec has gone up from 1.5 per/sec to 230 per sec with SQL Server 2005.
Is there any settings in SQL Server 2000 to make it perform faster as SQL Server 2005 works.

Viewing 15 posts - 1 through 14 (of 14 total)

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

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 1

Hi,

If this is an upgrade database, you need to run DBCC UPDATEUSAGE and sp_updatestats (both are documented in Books Online) in all your database.

This doesn't necessarily solve your problem but this should be done on all upgraded databases and before you spend any more time troubleshooting.

HTH

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

noeld SSC Guru Points: 96590 More actions · Answer 2

Rebuild indexes, update stats (with fullscan) and update usage are recommended right after the DB upgrade.

* Noel

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

Heh... you guys are missing it... OP wants to know why performance IMPROVED so much after the upgrade so they can go back to the original database and make it run as well, too...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 4

Ah, well, that's easy then; because it is bigger and better 🙂

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

noeld SSC Guru Points: 96590 More actions · Answer 5

Jeff Moden (11/18/2008)
Heh... you guys are missing it... OP wants to know why performance IMPROVED so much after the upgrade so they can go back to the original database and make it run as well, too...

hehe .. you are RIGHT. I wasn't even thinking it was an automatic answer based on the "normal" issue...

Why would you complain when something is "BETTER" ? 😀

* Noel

HanShi SSC-Dedicated Points: 33508 More actions · Answer 6

I have the same IMPROVEMENT of performance from the same query running on SQL-2000 (25 minutes) and SQL-2005 (45 seconds). The query includes a DISTINCT and an ORDER BY. My conclusion was the SQL-Engine of 2005 is improved to handle these kind of statements in a better way.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

You're lucky... I've also seen it do just the opposite.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sandeephughes SSC Journeyman Points: 78 More actions · Answer 8

Thanks for everybody's reply for confirming that SQL Server 2005 has much better performance improvement than SQL Server 2000.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

sandeephughes (11/21/2008)
Thanks for everybody's reply for confirming that SQL Server 2005 has much better performance improvement than SQL Server 2000.

Mostly...;)

"Keep Trying"

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 10

arup chakraborty

Hall of Fame

Points: 3640

November 21, 2008 at 12:16 am

#901464

Is performance of SQL Server 2008 is better than 2005?

sandeephughes SSC Journeyman Points: 78 More actions · Answer 11

Havn't tested the performance difference of SQL 2005 and SQL 2008.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 12

sandeephughes (11/21/2008)
Thanks for everybody's reply for confirming that SQL Server 2005 has much better performance improvement than SQL Server 2000.

Nah... that's just not true. It may be true for your one proc because ya got lucky... I've also seen 2k5 do much worse on code.

To get the performance change that you mentioned, the 2k version must've had some really badly fragmented indexes, maybe a very badly fragmented hard drive, and may some really out of date statistics. All you did with the upgrade to 2k5 was make all that stuff nice and clean for a change.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sandeephughes SSC Journeyman Points: 78 More actions · Answer 13

Hi Jeff,

I havn't upgrade to 2005, but i uninstalled 2000 and installed 2005 and then restored backup to 2005.

Then i did performance testing using loadrunner.

To confirm i taken one more machine and installed 2000 with SP3, Sp4 and see the same poor perfromance.

But after uninstalling 2000 and installing 2005 and restoring the same DB did magic and perfomance is pretty good some stored proc's execution time reduced from 7 sec to 0.5 sec.

I observed that full scans/sec in 2005 were 120 times more as compare to SQL 2000.

Please let me know what s improved in 2005 as compare to 2000.

waiting for ur reply.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 14

Like I said... you got lucky. There have been dozens of posts where folks want to go back to 2k because 2k5 made the app slower.

Very glad it worked out for you, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)