i keep experiencing that complex queries run vastly faster than 2000 (from
about 1 hour to 2 minutes, and 2006 server is an old slower server). Does
anyone have similar experience? Or could backup/restore (rebuild index,
etc...) cause such an improvement?

There are a lot of transparent, under-the-covers optimizations.  Upgrading
alone can cause performance boosts (though an hour to 2 minutes sounds a lot
more extreme than that).  Add to that the things you can do on purpose to
improve performance, such as a much better database engine tuning advisor
(the tool formerly known as the index tuning wizard).

However, several people here have reported their applications have slowed
down after the upgrade.  I suspect most of that is due to fragmentation, not
updating statistics, not rebuilding indexes, staying in 80 compatibility
mode, in-place upgrade due to backup/restore, or maybe it's the first time
the proc is updated and the cache is empty, etc.

A





Show quoteHide quote

Little bug in sql2005?
Need Consecutive Query
**SORT THE PHYSICAL ORDER ON A TABLE'S COLUMNS**
INSTEAD OF TRIGGERS DO NOT SUPPORT DIRECT RECURSION
SQL 2005 slower than 2000?