DB Reindex Question

• aruram

  SSCrazy

  Points: 2379

  More actions

  February 7, 2008 at 11:59 am

  #203159

  Hello

  I have a couple of questions related to reindexing a fragmented tables/indexes:

  1) Is it sufficient to rebuild just the clustered indexes on the fragmented tables (all clustered indexes are unique), or is it necessary to rebuild the non-clustered indexes also using DBCC DBREINDEX?

  2) Is it beneficial to additionaly run the DBCC UPDATE STATS with FULL SCAN option to gain full performance improvements?

  Thanks

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  February 7, 2008 at 12:23 pm

  #776142

  Hello,

  You are no need to manually issue DBCC DBREINDEX when you rebuild the clustered indexes. SQL Server automatically does a rebuild of the non-clustered indexes whenever the clustered indexes are rebuilt.

  I don't think that the FULL SCAN option will give any performance benefit. In BOL it is stated

  UPDATE STATISTICS WITH RESAMPLE updates all the statistics on a table at the current sampling rate. This means that statistics tied to indexes, which are created with full scan when the index is built, require the whole table scan to be refreshed. This potentially can be a very time consuming operation, especially when it involves large partitioned tables with many indexes. Refreshing each statistic requires reading lots of data. To avoid this problem, consider using sp_updatestats (Transact-SQL). This updates statistics only when they are required.

  Hope I'm clear.

  Thanks

  
  Lucky

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  February 8, 2008 at 12:04 am

  #776292

  in SQL 2000, I believe that rebuilding the clustred index rebuilds the nonclustered indxes. In SQL 2005, that's no longer the case.

  You can just use DBCC DBReindex to rebuild all the indexes on the table.

  There's no point in doing a stats update after an indx rebuild. Index rebuilds always update the stats as if you'd done an update stats with full scan.

  Running a sampled stats update after an indx rebuild may lead to less accurate stats than you would otherwise have had.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass