How To: Reindex tables in a specific order

You can choose the order in which tables will be maintained. For example, let’s say that you want two tables in [YourDatabase] to be indexed before all other tables in that database, in this order: 

1. dbo.[Best] (it’s the most important or most badly fragmented table)
2. dbo.[Okay]
3. other tables

In this case, we would insert a row into the Minion.IndexSettingsTable 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 table, the settings in that row override all of the default index maintenance settings for that table. So, inserting a row for [YourDatabase].dbo.[Best] means that ONLY those specified index settings will be used for that table; no settings defined in Minion.IndexSettingsDB will apply to those specific tables.

NOTE:  Any non-specified tables will have a ReindexGroupOrder of 0, and a ReindexOrder of 0, by default. (Order settings at the database level have no effect on table-level ordering.)

Because we have so few tables in this example, the simplest method is to assign the heaviest "weight" to dbo.[Best], and lesser weights to dbo.[Okay]. In our example, we would insert two rows: 

-- Insert IndexSettingsDB row for dbo.[Best], ReindexOrder=255 (first)

INSERT  INTO [Minion].[IndexSettingsTable]
    ( [DBName] ,
      [SchemaName] ,
      [TableName] ,
      [Exclude] ,
      [ReindexGroupOrder] ,
      [ReindexOrder] ,
      [ReorgThreshold] ,
      [RebuildThreshold] ,
      [FILLFACTORopt] ,
      [PadIndex] ,
      [GetRowCT] ,
      [GetPostFragLevel] ,
      [UpdateStatsOnDefrag] ,
      [LogIndexPhysicalStats] ,
      [IndexScanMode] ,
      [LogProgress] ,
      [LogRetDays] ,
      [IncludeUsageDetails]

    )
VALUES  ( 'YourDatabase' , -- DBName
      'dbo' ,    -- SchemaName
      'Best' ,  -- TableName
      0 ,    -- Exclude
      0 ,    -- ReindexGroupOrder
      255 ,    -- ReindexOrder
      10 ,    -- ReorgThreshold
      20 ,    -- RebuildThreshold
      80 ,    -- FILLFACTORopt
      'ON' ,    -- PadIndex
      1 ,    -- GetRowCT
      1 ,    -- GetPostFragLevel
      1 ,    -- UpdateStatsOnDefrag
      0 ,    -- LogIndexPhysicalStats
      'Limited' ,  -- IndexScanMode
      1 ,    -- LogProgress
      60 ,    -- LogRetDays
      1    -- IncludeUsageDetails
     );

-- Insert IndexSettingsDB row for dbo.[Okay], ReindexOrder=200 (after [Best])

INSERT  INTO [Minion].[IndexSettingsTable]
    ( [DBName] ,
      [SchemaName] ,
      [TableName] ,
      [Exclude] ,
      [ReindexGroupOrder] ,
      [ReindexOrder] ,
      [ReorgThreshold] ,
      [RebuildThreshold] ,
      [FILLFACTORopt] ,
      [PadIndex] ,
      [GetRowCT] ,
      [GetPostFragLevel] ,
      [UpdateStatsOnDefrag] ,
      [LogIndexPhysicalStats] ,
      [IndexScanMode] ,
      [LogProgress] ,
      [LogRetDays] ,
      [IncludeUsageDetails]

    )
VALUES  ( 'YourDatabase' , -- DBName
      'dbo' ,    -- SchemaName
      'Okay' ,  -- TableName
      0 ,    -- Exclude
      0 ,    -- ReindexGroupOrder
      200 ,    -- ReindexOrder
      10 ,    -- ReorgThreshold
      20 ,    -- RebuildThreshold
      80 ,    -- FILLFACTORopt
      'ON' ,    -- PadIndex
      1 ,    -- GetRowCT
      1 ,    -- GetPostFragLevel
      1 ,    -- UpdateStatsOnDefrag
      0 ,    -- LogIndexPhysicalStats
      'Limited' ,  -- IndexScanMode
      1 ,    -- LogProgress
      60 ,    -- LogRetDays
      1    -- IncludeUsageDetails
     );

For a more complex ordering scheme, we could divide tables up into groups, and then order the reindexing both by group, and within each group. The pseudocode for this example might be:

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

GroupOrderGroupDBOrder

• Insert rows for databases YourDatabase and Semi, both with GroupOrder = 200
  • Row YourDatabase: GroupDBOrder = 255
  • Row Semi: GroupDBOrder = 100
• Insert rows for databases Lame and Unused, both with GroupOrder = 100
  • Row YourDatabase: Lame = 255
  • Row Semi: Unused = 100

The resulting backup order would be as follows:

1. YourDatabase 
2. Semi
3. Lame
4. Unused