Minion Reindex is made up of SQL Server stored procedures, tables, and jobs. There is an optional PowerShell script for installation. The tables store configuration and log information; stored procedures perform reindex operations; and the jobs execute those index operations on a schedule.
Note: Minion is installed in the master database by default. You certainly can install Minion in another database (like a DBAdmin database), but when you do, you must also change the database that the jobs point to.
Configuration Settings Hierarchy
As much as possible, configuration for reindex is stored in tables: Minion.IndexSettingsDB and Minion.IndexSettingsTable. A default row in Minion.IndexSettingsDB (DBName=’MinionDefault’) provides settings for any database that doesn’t have its own specific settings. This is a hierarchy of granularity, where more specific configuration levels completely override the less specific levels. That is:
- Insert a row for a specific database into Minion.IndexSettingsDB, and that row will override ALL of the default settings for that database.
- Insert a row for a specific table in Minion.IndexSettingsTable, and that row will override ALL of the default (or, if available, database-specific) settings for that table.
Note a value left at NULL in one of these tables means that Minion will use the setting that the SQL Server instance itself uses.
The main Minion Reindex stored procedure – Minion.IndexMaintMaster – takes a number of parameters that are specific to the current maintenance run. For example:
- Use @IndexOption to run index maintenance on only tables marked for ONLINE index maintenance.
- Use @PrepOnly to only gather index fragmentation stats. These are saved to a table, so that later you can run Minion.IndexMaintMaster using @RunPrepped, and the procedure will used the saved fragmentation stats (instead of gathering them anew).
- 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.
As a Minion Reindex routine runs, it keeps logs of all activity in two tables:
- Minion.IndexMaintLog – a log of activity at the database level.
- Minion.IndexMaintLogDetail – a log of activity at the index level.
The Status column for the current run is updated continually in each of these tables. This way, status information (Live Insight) is available to you while index maintenance is still running, and historical data is available after the fact for help in planning future operations, reporting, troubleshooting, and more.
Minion Enterprise Hint
Minion Reindex doesn’t include an alerting mechanism, though you can write one easily using the log tables. Minion Enterprise provides central reporting and alerting for backups and maintenance. The ME alert for all databases includes the reasons why any manitenance fails, across the entire enterprise. Further, you can set customized alerting thresholds at various levels (server, database, etc.).
See www.MinionWare.net for more information, or
email us today at Support@MidnightDBA.com for a demo!