Thursday, June 5, 2008

Update all index statistics after upgrading a db from SQL Server 2000 to 2005

After upgrading a database from SQL Server 2000 to SQL Server 2005, our application's performance degraded significantly. It was eventually resolved by creating two new indexes in the database.

However, I could not understand how database performance could be impacted negatively by an upgrade. All the indexes which were in the SQL 2000 db exist in the upgraded database.

After googling the topic for a while I came across the following article: Interview with Greg Linwood. Below is an important tip from the article.

Most upgrades I have been involved with have been fairly smooth but some degree of tuning has been required in most cases. One tip worth remembering is to update all index statistics after an upgrade (or simply rebuild all indexes) so that SQL Server’s cost optimizer has the most recent information possible by which to make the best query execution decisions. SQL Server 2005’s cost optimizer is significantly more sophisticated and will generally make the same or better decisions in most cases than it’s predecessor. These improved decisions rarely get noticed (as no-one complains when things improve!) but those which degrade performance sometimes cause the upgrade process to come under un-warranted criticism. Hence, I recommend closely monitoring performance during and after upgrades, just in case the query optimizer makes decisions which “appear” better to it but end up not working out.

No comments: