CONCEPT: Automated Database Index Maintenance execution order

RockSolid will automatically adopt one of two approaches to database index maintenance execution order depending on:

  • The size of the database
  • The time allocated to database index maintenance

Approach 1: Rebuild in order of factor of fragmentation
In order to rebuild indexes in order of level of fragmentation, first the fragmentation information has to be gather. This is achieved using internal SQL Server functions. However this doesn’t occur without cost and in general the large the database the longer the process of collecting fragmentation statistics can take. In some case this may range from minutes to several hours for VLDB’s.
Therefore, in order for RockSolid to use the time allocated to the index maintenance window effectively it has to determine if it is efficient to collect fragmentation statistics in advance. This approach will be used if:

  • The database is less than 10GB in used space
  • The database is greater than 10GB and it is estimated that the process of collecting fragmentation statistics will take less than 20% of the allocated maintenance window.

If these conditions are met, the RockSolid will order its reindexing strategy for that particular database based on the level of fragmentation within the index as a factor of the number of range scans recorded within the internal index usage statistics.
If these conditions are not met, then approach 2 is used.

Approach 2: Rebuild in sequential order
If it is deemed inefficient use of the allocated reindex window to collect fragmentation statistics, then RockSolid will instead use a sequential rebuild methodology. Under this approach RockSolid will rebuild indexes in order based on their last rebuild date. Fragmentation statistics of each index is still captured, but this is instead captured on an index by index approach during execution rather than capturing for all indexes prior to beginning the rebuild process.

Have more questions? Submit a request


Please sign in to leave a comment.