How To: Reindex databases in a specific order


You can choose the order in which databases will be maintained. For example, let’s say that you want your databases to be indexed in this order: 

  1. [YourDatabase] (it’s the most important database on your system)
  2. [Semi]
  3. [Lame]
  4. [Unused]

In this case, we would insert a row into the Minion.IndexSettingsDB for each one of the databases, specifying either ReindexGroupOrder, ReindexOrder, or both, as needed. 

NOTE: For ReindexGroupOrder and ReindexOrder, higher numbers have a greater "weight" - they have a higher priority - and will be indexed earlier than lower numbers. Note also that these columns are TINYINT, so weighted values must fall between 0 and 255.

NOTE: When you insert a row for a database, the settings in that row override all of the default index maintenance settings for that database. So, inserting a row for [YourDatabase] means that ONLY index settings from that row will be used for [YourDatabase]; none of the default settings will apply to [YourDatabase].

NOTE: Any databases that rely on the default system-wide settings (represented by the row where DBName=’MinionDefault’) will be indexed according to the values in the MinionDefault columns ReindexGroupOrder and ReindexOrder. By default, these are both 0 (lowest priority), and so non-specified databases would be maintained last. 

Because we have so few databases in this example, the simplest method is to assign the heaviest "weight" to YourDatabase, and lesser weights to the other databases, in decreasing order. In our example, we would insert four rows: 


-- Insert IndexSettingsDB row for [YourDatabase], ReindexOrder=255 (first)

INSERT  INTO [Minion].[IndexSettingsDB]
    ( DBName ,
      [Exclude] ,
      [ReindexGroupOrder] ,
      [ReindexOrder] ,
      [ReorgThreshold] ,
      [RebuildThreshold] ,
      [FILLFACTORopt] ,
      [PadIndex] ,
      [SortInTempDB] ,
      [GetRowCT] ,
      [GetPostFragLevel] ,
      [UpdateStatsOnDefrag] ,
      [LogIndexPhysicalStats] ,
      [IndexScanMode] ,
      [LogProgress] ,
      [LogRetDays] ,
      [LogLoc] ,
      [MinionTriggerPath] ,
      [IncludeUsageDetails] 
    )
VALUES  ( 'YourDatabase' ,  --  DBName ,
      0 ,    --  Exclude ,
      0 ,    --  ReindexGroupOrder ,
      255 ,    --  ReindexOrder ,
      10 ,    --  ReorgThreshold ,
      20 ,    --  RebuildThreshold ,
      80 ,    --  FILLFACTORopt ,
      'ON' ,    --  PadIndex ,
      'OFF' ,    --  SortInTempDB ,
      1 ,    --  GetRowCT ,
      1 ,    --  GetPostFragLevel ,
      1 ,    --  UpdateStatsOnDefrag ,
      0 ,    --  LogIndexPhysicalStats ,
      'Limited' ,  --  IndexScanMode ,
      1 ,    --  LogProgress ,
      60 ,    --  LogRetDays ,
      'Local' ,  --  LogLoc ,
      '\\minioncon\c$' ,  --  MinionTriggerPath ,
      1    --  IncludeUsageDetails 
    );


-- Insert IndexSettingsDB row for "Semi", ReindexOrder=150 (after [YourDatabase])
INSERT  INTO [Minion].[IndexSettingsDB]
    ( DBName ,
      [Exclude] ,
      [ReindexGroupOrder] ,
      [ReindexOrder] ,
      [ReorgThreshold] ,
      [RebuildThreshold] ,
      [FILLFACTORopt] ,
      [PadIndex] ,
      [SortInTempDB] ,
      [GetRowCT] ,
      [GetPostFragLevel] ,
      [UpdateStatsOnDefrag] ,
      [LogIndexPhysicalStats] ,
      [IndexScanMode] ,
      [LogProgress] ,
      [LogRetDays] ,
      [LogLoc] ,
      [MinionTriggerPath] ,
      [IncludeUsageDetails] 
    )
VALUES  ( 'Semi' ,  --  DBName ,
      0 ,    --  Exclude ,
      0 ,    --  ReindexGroupOrder ,
      150 ,    --  ReindexOrder ,
      10 ,    --  ReorgThreshold ,
      20 ,    --  RebuildThreshold ,
      80 ,    --  FILLFACTORopt ,
      'ON' ,    --  PadIndex ,
      'OFF' ,    --  SortInTempDB ,
      1 ,    --  GetRowCT ,
      1 ,    --  GetPostFragLevel ,
      1 ,    --  UpdateStatsOnDefrag ,
      0 ,    --  LogIndexPhysicalStats ,
      'Limited' ,  --  IndexScanMode ,
      1 ,    --  LogProgress ,
      60 ,    --  LogRetDays ,
      'Local' ,  --  LogLoc ,
      '\\minioncon\c$' ,  --  MinionTriggerPath ,
      1    --  IncludeUsageDetails 
    );


-- Insert IndexSettingsDB row for "Lame", ReindexOrder=100 (after "Semi")
INSERT  INTO [Minion].[IndexSettingsDB]
    ( DBName ,
      [Exclude] ,
      [ReindexGroupOrder] ,
      [ReindexOrder] ,
      [ReorgThreshold] ,
      [RebuildThreshold] ,
      [FILLFACTORopt] ,
      [PadIndex] ,
      [SortInTempDB] ,
      [GetRowCT] ,
      [GetPostFragLevel] ,
      [UpdateStatsOnDefrag] ,
      [LogIndexPhysicalStats] ,
      [IndexScanMode] ,
      [LogProgress] ,
      [LogRetDays] ,
      [LogLoc] ,
      [MinionTriggerPath] ,
      [IncludeUsageDetails] 
    )
VALUES  ( 'Lame' ,  --  DBName ,
      0 ,    --  Exclude ,
      0 ,    --  ReindexGroupOrder ,
      100 ,    --  ReindexOrder ,
      10 ,    --  ReorgThreshold ,
      20 ,    --  RebuildThreshold ,
      80 ,    --  FILLFACTORopt ,
      'ON' ,    --  PadIndex ,
      'OFF' ,    --  SortInTempDB ,
      1 ,    --  GetRowCT ,
      1 ,    --  GetPostFragLevel ,
      1 ,    --  UpdateStatsOnDefrag ,
      0 ,    --  LogIndexPhysicalStats ,
      'Limited' ,  --  IndexScanMode ,
      1 ,    --  LogProgress ,
      60 ,    --  LogRetDays ,
      'Local' ,  --  LogLoc ,
      '\\minioncon\c$' ,  --  MinionTriggerPath ,
      1    --  IncludeUsageDetails 
    );


-- Insert IndexSettingsDB row for "Unused", ReindexOrder=50 (after [Lame])
INSERT  INTO [Minion].[IndexSettingsDB]
    ( DBName ,
      [Exclude] ,
      [ReindexGroupOrder] ,
      [ReindexOrder] ,
      [ReorgThreshold] ,
      [RebuildThreshold] ,
      [FILLFACTORopt] ,
      [PadIndex] ,
      [SortInTempDB] ,
      [GetRowCT] ,
      [GetPostFragLevel] ,
      [UpdateStatsOnDefrag] ,
      [LogIndexPhysicalStats] ,
      [IndexScanMode] ,
      [LogProgress] ,
      [LogRetDays] ,
      [LogLoc] ,
      [MinionTriggerPath] ,
      [IncludeUsageDetails] 
    )
VALUES  ( 'Unused' ,  --  DBName ,
      0 ,    --  Exclude ,
      0 ,    --  ReindexGroupOrder ,
      50 ,    --  ReindexOrder ,
      10 ,    --  ReorgThreshold ,
      20 ,    --  RebuildThreshold ,
      80 ,    --  FILLFACTORopt ,
      'ON' ,    --  PadIndex ,
      'OFF' ,    --  SortInTempDB ,
      1 ,    --  GetRowCT ,
      1 ,    --  GetPostFragLevel ,
      1 ,    --  UpdateStatsOnDefrag ,
      0 ,    --  LogIndexPhysicalStats ,
      'Limited' ,  --  IndexScanMode ,
      1 ,    --  LogProgress ,
      60 ,    --  LogRetDays ,
      'Local' ,  --  LogLoc ,
      '\\minioncon\c$' ,  --  MinionTriggerPath ,
      1    --  IncludeUsageDetails 
    );