My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/SQL 2005 Upgrade - Rebuild your indexes
The new RSS feed is here
I can't say how many times I've read or been told that before you complain about performance issues with SQL2005 you should rebuild your indexes and update your statistics.
Well we've just upgraded our search boxes and was having performance problems, timeouts galore. The reason was the plan being produced was far from optimal. It was estimating it would get 1 row from a very large table, which meant the nested loops it was doing made sense. However the estimation was way off, in fact it was getting ~8,000 rows. This meant the nested loops weren't very effiecient. Even after 90s of running the query hadn't finished and performed more than 20 million reads. (on 2000 it was doing 100,000, this is a complex search involving full text).
... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/SQL 2005 Upgrade - Rebuild your indexes