Are there performance benefits when upgrading SQL2000 to SQL2005?

Question

I've had a look at this question but there are no responses regarding performance.

Are there any performance benefits from doing a simple upgrade from SQL2000 to SQL2005?

I am thinking a line of business OLTP datbase. I am not using OLAP or FTI.

Answer 1

Not in my experience.

If you want to improve your database performance, I tend to throw more hardware at it in the form of more RAM and faster disks.

Answer 2

We found yes.

The query optimiser is updated and better. We found a lot of query plans were different with absolutely no other changes.

Even our end users commented on the speed improvement and general responsiveness. I have the email to prove it :-D

At the same time, we re-wrote a very few stored procs because they were worse. However, the same tweaks also improved response on SQL 2000 and was simply poor code/ideas.

Answer 3

I still haven't found much on this but here is a load of marketing stuff that essentially says SQL2005 is a good thing:

http://www.microsoft.com/sqlserver/2005/en/us/white-papers.aspx#gen

and in this white paper "Why upgrade to SQLSERVER2005"(.doc)

it states

Faster Response Times for End Users

Database query response times have improved 70−80 percent for many applications converted from SQL Server 2000 to SQL Server 2005. Quicker response times to queries help business users react faster and enable the database to be used for real-time decision making. Queries that might have previously taken several hours can now be completed in a matter of minutes. This provides a whole new level of functionality for end-users because analysis can be done on an ad hoc basis rather than exclusively in predefined reports. Companies can use this new accessibility to information to dynamically monitor processes.

Answer 4

On the flip side to benefits there seem to be few problems when upgrading.

http://stackoverflow.com/questions/489137/why-would-upgrading-from-sql-server-2000-to-2005-result-in-slower-queries

http://www.google.co.uk/url?sa=t&source=web&ct=res&cd=4&url=http%3A%2F%2Fwww.eggheadcafe.com%2Fforumarchives%2FSQLServersetup%2FFeb2006%2Fpost26068566.asp&ei=76XNSpHWJ5bWmQPDwdSMAw&usg=AFQjCNGY15FipRCEzPq2OVcomXqys08hTA&sig2=UN5mcW6T7es0D%5FUUCcuizQ