How To: Gather index fragmentation statistics on a different schedule from the reindex routine




Maintenance windows are never the wide open space we’d like them to be. So, we made sure you have the option to maximize it: you can schedule the gathering of fragmentation stats at a different time than your reindexing itself.  This way, you can use your entire maintenance window for processing indexes instead of finding out the fragmentation levels, which can take a very long time.


Let’s take the example of ReallyBigDB:

  1. Exclude ReallyBigDB from the job MinionReindexDBs-All-All (using @Exclude=’ReallyBigDB’).
  2. Create the job MinionReindexDBs-ReallyBigDB-FragStats, to run sometime before the reindex job. For the job step, run Minion.IndexMaintMaster with @Include=’ReallyBigDB’,  @PrepOnly=1, @RunPrepped=0, and other options as appropriate.
  3. Create the job MinionReindexDBs-ReallyBigDB-All. For the job step, run Minion.IndexMaintMaster with @Include=’ReallyBigDB’,  @PrepOnly=0, @RunPrepped=1  (which tells the SP to use the stats stored by the previous @PrepOnly=1 run), and other options as appropriate.


Note: There can only be one prep per database at a time.  When you run @PrepOnly  = 1, it enters the data into the table Minion.IndexTableFrag, and deletes any previous preparation runs for the database in question.  So, while you can have as many databases as you like prepped in this table, each database can only have a single prep run.  Even if the previous ones weren’t deleted, the reindex SP only looks at the last one.



Example code - The following code will gather the fragmentation stats for ReallyBigDB:

EXEC [Minion].[IndexMaintMaster] 
  @IndexOption = 'All',
  @ReorgMode = 'All',
  @RunPrepped = 0, 
  @PrepOnly = 1,
  @StmtOnly = 0,
  @Include = 'ReallyBigDB', 
  @Exclude = NULL, 
  @LogProgress = 1;
   


 
   


 

The following execution will reindex the [ReallyBigDB] database, using the fragmentation stats stored by the previous @PrepOnly=1 run (instead of gathering statistics at the same time):

EXEC [Minion].[IndexMaintMaster] 
  @IndexOption = 'All',
  @ReorgMode = 'All',
  @RunPrepped = 0, 
  @PrepOnly = 0,
  @StmtOnly = 1,
  @Include = 'ReallyBigDB', 
  @Exclude = NULL, 
  @LogProgress = 1;