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

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

• Make sure you're running the latest SP of SS 2005.
• I'm surprised that you make no mention of having tested all your SPs and UDFs in SS 2005 to prove that they succeed in the same ways and fail predictably throughout. This may take some time to do, but gives you a great chance to dramatically upgrade your system's robustness.

Answer 2

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).

Answer 3

Addition to your list CheckDB before the database leaves SQL 2000 - you want to be as sure as possible no corruption from 2000 is brought over, if anyone started deallocating stuff in the system tables instead of using the proper commands will give you a mare once migrated.

If you rebuild the indexes then exec sp_updatestats ‘resample’ will give you worse statistics for your indexes since they would of already been updated by the rebuilds. Additional stats added might well need to be updated, but do them individually, don't kill the index stats for them.