SQL Server Performance

Reindex after a db restore to a different server

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by WingSzeto, Jan 6, 2009.

  1. WingSzeto Member

    We recently needed to move a production db to a new server and used backup and retore to do the move. The master db and msdb are new, we didn't move them. After the move, is it necessary to rebuild all the indexes or just doing the update statistics be good enough for the production db? If I need to rebuild the indexes, can I just based on the fragmentation of the indexes and build the heavily fragmented ones only?
    wingman
  2. ndinakar Member

    The statistics, I think, are stored in physical files so when you backup and restore the stats get transferred too. If you change the compatibility mode then you would want to reindex. If you are a little skeptical, you can run the update stats and should be fine. If the restored db is going to be used for critical apps you could reindex depending on the size of the database.. or you could just reindex few important tables..there is no one correct answer.. its more of what you feel comfortable with depending on the nature of your data and the app that needs the data.
  3. satya Moderator

    IN my experience it is better to perform REINDEX & REBUILD for all the databases...indexes in order to cache the relevant plans perfectly, once it is completely then you can choose required tables that are fragmented/updated heavily during the day.
  4. WingSzeto Member

    Ok, I think I will reindex the entire db. Would the DBReindex command be ok for it? I think DBreindex is kept in SQL 2005 for backward compatibility. What is the correct command in SQL 2005 that allows me to reindex all tables in a db at once.?
  5. satya Moderator

Share This Page