SQL Server Performance

How to speed up reindexing on a large database?

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

  1. DBADave New Member

    We have a 100+GB database that eventually with grow to 500GB+. Reindexing takes a long time and the database is on a SAN. What things can be done to speed up reindexing and update statistics on very large databases?
    Data and Log files are on their own RAID 10 SAN drives. We are seeing disk bottlenecks on the data drive so we will be separating the large database from all other database for this particular application. We are also considering moving indexes to a dedicated RAID 10 drive.
    What else can be done to spped things up?
    Thanks, Dave
  2. Elisabeth Redei New Member

    Hi Dave,
    First of all, if you rebuild your index the statistics will be updated (with fullscan) so there is no need to do both (at the same time at least).In fact, by updating the statistics immediately after an index rebuild you are likely to lower the quality of the statistics if you use a default sample rate and do not specify WITH FULLSCAN.
    Secondly, you could switch to BULK LOGGED recovery mode during your index rebuild since most index operations are minimally logged in BULK LOGGED mode. See "Choosing a Recovery Model for Index Operations" in BoL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f397ffe5-7869-45bf-8640-b3e7badbd299.htm).
    The other thing is, if your indexes are not heavily fragmented, ALTER INDEX REORGANIZE might be an option for some of your indexes. Having said that, REORGANIZE is a fully logged operation and you can't get around that.
    Maybe for some indexes it is enough to UPDATE STATISTICS WITH FULLSCAN rather than rebuilding the entire index?
    /Elisabeth
  3. satya Moderator

    In my case we have identified the important tables that are having huge inserts/deletes/updates during the day and performing UPDATE STATS overnight to ensure the statistics are updated for the next show.
    As you are already on the move to differentiate the larger indexes to a seperate drive this should show some betterment in terms of performance within the platform, if not you could even move all the READONLY or STATIC data tables (if any) to a seperate filegroup to keep them aside from the usual tables that are managed heavily in a day.

Share This Page