Should I rebuild table indexes after a SQL Server 2000 to 2005 database migration

Question

I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration.

After restoring from a backup I plan to do the following:

ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL = 90;

DBCC CHECKDB(<database_name>) WITH NO_INFOMSGS

DBCC UPDATEUSAGE(<database_name>) WITH NO_INFOMSGS

exec sp_updatestats ‘resample’

Should I rebuild table indexes before using DBCC UPDATEUSAGE and sp_updatestats?

Have I missed anything obvious that should be executed after a migration?

All help would be warmly up-voted.

Thanks

Answer 1

There isn't much authoritative online material providing specifics on migration (beyond procedures aimed at merely ensuring the database structural integrity in the new/upgraded host). For this reason, and because this migration seems to be scheduled/planned event for you, I'd take the opportunity to rebuild all indexes, including clustered indexes.

To some, this may seem "overkill", but what better opportunity of re-balancing and re-packing indexes/tables, providing a fresh fill-factor that is commensurate with the expected CRUD usage, and to generally assert the database's health in its new host.

In practical terms, I would...

ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL = 90;

DBCC CHECKDB(<database_name>)
   -- WITH NO_INFOMSGS  (I'd take the messages, I'm curious by nature ;-)

Like you suggest, but then I'd rebuild all indexes on all/most tables even (maybe in particular...) on very big tables. To be sure, one should evaluate the time and relative risk involved with such an operation, but for most cases, even with databases in the 100+ million rows, the overall time overhead is in the order of a few hours, time well invested, for it may defer future index rebuilds. As to the risk factor, you seem to have a backup...

What goes without saying... When the underlying table has a clustered index, and if it desirable to rebuild it as well, do drop all other indexes before, lest a lot of time is wasted in updating the non-clustered index (without they being rebuilt in earnest), then of course recreate these non-clustered indexes.

Depending on the number of tables and indexes in question, it may be profitable to write a few small Stored Procedures to automate the index dropping (and re-creating, although it may also be important to individually review the fill-factors, recompute and other parameter).