Procedures Detail


Minion.IndexMaintMaster

The Minion.IndexMaintMaster procedure makes all the decisions on which databases to reindex, and what order they should be in. This stored procedure calls the Minion.IndexSettingsDB stored procedure once per each database specified in the parameters; or, if “All” is specified, per each eligible database in sys.databases.

Minion Reindex 1.2 supports SQL Server databases that are part of an Availability Group (AG). Reindex will run for databases that are not part of an AG, and for AG primaries, but not for databases that act as a secondary in an A scenario. (AG secondary databases do not require index maintenance.)

Name
Type
Description
@IndexOption
varchar
Perform maintenance only for indexes marked for online operations; only for those marked for offline operations; or for all indexes.
Valid inputs:
ONLINE
OFFLINE
ALL
For more information, see “How To: Reindex only indexes that are marked ONLINE = ON (or, only ONLINE = OFF)”
@ReorgMode
varchar
Perform maintenance only for indexes that meet the REORG threshold; only for those that meet the REBUILD threshold; or for all indexes that meet either threshold (when this is set to “All”).
Note that for REORG mode, only REORG statements will be generated, even for indexes that are over the rebuild threshold. For REBUILD, only REBUILD statements will be generated.
Valid inputs:
All
REORG
REBUILD
@RunPrepped
bit
If you've collected index fragmentation stats ahead of time by running with @PrepOnly = 1, then you can use this option. It causes the index maintenance to use the saved frag stats.
For more information, see “How To: Gather index fragmentation statistics on a different schedule from the reindex routine”.
@PrepOnly
Bit
Only gets index fragmentation stats, and saves to a table. This prepares the databases to be reindexed.
If @PrepOnly = 1, then @RunPrepped must be set to 0.
For more information, see “How To: Gather index fragmentation statistics on a different schedule from the reindex routine”.
@StmtOnly
Bit
Only prints reindex statements. This is an excellent choice for running statements manually; it allows you to pick and choose which indexes you want to do, or just see how many are over the thresholds.
For more information, see “How To: Generate reindex statements only”.
@Include
varchar
Use @Include to run index maintenance on a specific list of databases, or databases that match a LIKE expression. Alternately, set @Include=’All’ or @Include=NULL to run maintenance on all databases.
Examples of valid inputs include:
All
NULL
DBname
DBName1, DBname2, etc.
DBName%, YourDatabase, Archive%
@Exclude
varchar
Use @Exclude to skip index maintenance for a specific list of databases, or databases that match a LIKE expression.
Examples of valid inputs include:
DBname
DBName1, DBname2, etc.
DBName%, YourDatabase, Archive%
For more information, see “How To: Exclude databases from index maintenance”.
@LogProgress
Bit
Track the progress of index operations for this database.
The overall status is tracked in the Minion.IndexMaintLog table, while specific operations are tracked in the Status column Minion.IndexMaintLogDetails.


Discussion: 

Minion.IndexMaintMaster is the heart and brain of Minion Reindex; it decides what needs to be done and pushes out orders to get it done. A few things you can do with Minion.IndexMaintMaster include:

· Maintain only indexes that can be done online, only those that can be done offline, or all.

· Generate and execute only reorganize statements, only rebuild statements, or both.

· Run the procedure to gather index fragmentation stats, and save them to a table. This prepares the database to be reindexed.

· Run the procedure without gathering index fragmentation stats. This requires that the index fragmentation data has already been collected.

· Choose to maintain a specific set of databases, via the @Include parameter. (E.g., @Include=’DB1, DB2, DB3’…)

· Choose to maintain all databases

· Choose to maintain all databases, with specific exclusions, via the @Exclude parameter.

· Only print reindex statements, do not run. This is an excellent choice for running statements manually; it allows you to pick and choose which indexes you want to maintain, or just see how many indexes are over the thresholds.

· Have every step of the run printed in the log so you can watch the progress (called Live Insight). This option is on by default.


Minion.IndexMaintDB

The Minion.IndexMaintDB stored procedure performs index maintenance for a single database. Minion.IndexMaintDB is the procedure that creates and runs the actual reindex statements for tables that meet the criteria stored in the settings tables (Minion.IndexSettingsDB and Minion.IndexSettingsTable).


IMPORTANT: 

We HIGHLY recommend using Minion.IndexMaintMaster for all of your reindex operations, even when reindexing a single database. Do not call Minion.IndexMaintDB to perform index maintenance.


The Minion.IndexMaintMaster procedure makes all the decisions on which databases to reindex, and what order they should be in. It’s certainly possible to call Minion.IndexMaintDB manually, to run an individual database, but we instead recommend using the Minion.IndexMaintMaster procedure (and just include the single database using the @Include parameter). First, it unifies your code, and therefore minimizes your effort. By calling the same procedure every time you reduce your learning curve and cut down on mistakes. Second, future functionality may move to the Minion.IndexMaintMaster procedure; if you get used to using Minion.IndexMaintMaster now, then things will always work as intended.

Name
Type
Description
@DBName
Varchar
Database name to perform maintenance on.
@IndexOption
varchar
Perform maintenance only for indexes marked for online operations; only for those marked for offline operations; or for all indexes.
Valid inputs:
ONLINE
OFFLINE
ALL
For more information, see “How To: Reindex only indexes that are marked ONLINE = ON (or, only ONLINE = OFF)”
@ReorgMode
varchar
Perform maintenance only for indexes that meet the REORG threshold; only for those that meet the REBUILD threshold; or for all indexes that meet either threshold (when this is set to “All”).
Note that for REORG mode, only REORG statements will be generated, even for indexes that are over the rebuild threshold. For REBUILD, only REBUILD statements will be generated.
Valid inputs:
All
REORG
REBUILD
@RunPrepped
bit
If you've collected index fragmentation stats ahead of time by running with @PrepOnly = 1, then you can use this option. It causes the index maintenance to use the saved frag stats.
For more information, see “How To: Gather index fragmentation statistics on a different schedule from the reindex routine”.
@PrepOnly
bit
Only gets index fragmentation stats, and saves to a table. This prepares the database to be reindexed.
If @PrepOnly = 1, then @RunPrepped must be set to 0.
For more information, see “How To: Gather index fragmentation statistics on a different schedule from the reindex routine”.
@StmtOnly
bit
Only prints reindex statements. This is an excellent choice for running statements manually; it allows you to pick and choose which indexes you want to do, or just see how many are over the thresholds.
For more information, see “How To: Generate reindex statements only”.
@LogProgress
Bit
Track the progress of index operations for this database.
The overall status is tracked in the Minion.IndexMaintLog table, while specific operations are tracked in the Status column Minion.IndexMaintLogDetails.

Minion.HELP

Use this stored procedure to get help on any Minion Reindex object without leaving Management Studio.

Name
Type
Description
@Module
Varchar
The name of the module to retrieve help for.
Valid inputs include:
NULL
Reindex
@Name
varchar
The name of the topic for which you would like help.
If you run Minion.HELP by itself, or with a @Module specified, it will return a list of available topics.


Examples:

For introductory help, run:

EXEC Minion.HELP;


For introductory help on Minion Reindex, run:

EXEC Minion.HELP 'Reindex';


For help on a particular topic – in this case, the Top 10 Features – run:

EXEC Minion.HELP 'Reindex', 'Top 10 Features';