Sql Server 2005 efficiency savings?

Question

Are there good efficiency savings using Sql Server 2005 over Sql Server 2000?

Or does it just have more services etc

Has anyone seen their system work any quicker after making the upgrade?

Answer 1

The surrounding tools such as Analysis Services were substantially rewritten and can get you a variety of wins depending on your requirements. However I don't see a lot of really fundamental changes from 2000 to 2005 in the core database engine.

There are some improvements that may get you better performance in certain situations. SQL2005 has much better support for 64-bit architectures and better table partitioning than SQL2000 (you can partition a table as opposed to making partitioned views). 64-bit support is the most likely to give you a performance win on a large system as it allows you to set up much larger caches.

Apart from those features I don't believe that there is really a large difference. There are probably minor performance tweaks.

The main reason to move from SQL2000 to SQL2005 will be when SQL2000 goes out of support. If you have a running application on SQL2000 there are not a lot of compelling reasons to switch to 2005 while 2000 is still supported by Microsoft.

Data Warehouse systems will get quite a few wins from moving to SQL2005. SSIS, SSAS2005 and SSRS2005 are much better than their SQL2000 counterparts.

Answer 2

2005 provides MVCC - row level versioning essentially - so as a developer there are some efficiencies: less locking to worry about.

Answer 3

I haven't migrated a system from 2000 to 2005 - I've either started with one or the other - so I don't have a comparison of my own. But there is a reasonable chance you will see a perf difference; if not by taking advantage of some of the new features like snapshot isolation, then at least by virtue of the fact that SQL2005's licensing model allows you to go multi-core at no additional licensing cost, and by the fact that SQL2005 has improved memory management.

Answer 4

Things will absolutely run faster with 2005. There were several improvements made to the query optimizer. And now you can create covering indexes so that the included columns only exist at the leaf level and don't have to get sorted. That alone is an enormous improvement and reason enough to upgrade.

Answer 5

SQL 2005 does a better job of working with caching. You used to have to poll SQL 2000 periodically to check for updates to a whole table. Now you can subscribe to a notification when something changes. It also works for queries, tables, and a few other elements.

Answer 6

I would say yes for all of the reasons listed by others, but even if your SQL skills are not that strong and your queries are not that great they will probably run faster on 2005. We moved from 2000 to 2005 and we had some complex queries that we could not get properly optimized in 2000. When we moved to 2005 it ate the queries up! Clearly the optimizer was making much better decisions out of the box.

I would strongly recommend moving to 2005 unless you have no issues with 2000.