Settings (like rebuild threshold) are stored in two separate tables, one for database-level defaults, and another for table-level defaults. Index fragmentation statistics are stored long term in one table, and in the short term (to aid the index maintenance operations) in another table. Index maintenance activities are logged at a high ("master") level, and also at a per-operation level. So, for example, you can see how long the entire maintenance operation took, or how long an individual index rebuild lasted.
Reindex settings:
- Minion.IndexSettingsDB – This table holds index maintenance default settings at the database level. You may insert rows to define index maintenance settings per database, or you can rely on the system-wide default settings (defined in the "MinionDefault" row), or a combination of both.
- Minion.IndexSettingsTable - This table holds index maintenance default settings at the table level. You may insert rows to override the default index maintenance settings for individual tables. Any table that does not have a value in this table gets its settings from the appropriate entry in the Minion.IndexSettingsDB table.
- Minion.DBMaintRegexLookup – Allows you to exclude databases from index maintenance (or all maintenance), based off of regular expressions.
Index fragmentation stats:
- Minion.IndexPhysicalStats – This table holds index size and fragmentation information when the @currLogIndexPhysicalStats parameter is enabled. You can use this data after an index maintenance to investigate the raw fragmentation data, to estimate the next time a table will need to be reindexed, and more. Currently, this table must be manually deleted; the large amount of data here means we don’t recommend leaving this setting on for long. Only turn it on when you need to diagnose something.
- Minion.IndexTableFrag - Holds index fragmentation information during the index maintenance process. If you run the index maintenance process with @PrepOnly = 1, this table stores that data; a subsequent run of index maintenance with @RunPrepped = 1 will make use of this prepared data, instead of gathering statistics at the same time.
Logs:
- Minion.IndexMaintLog – Holds a database-level summary of the whole maintenance operation. Each row contains the database name, operation status, the start and end time of the index maintenance event, and much more. This is updated as each operation occurs, so that you have Live Insight into active index operations.
- Minion.IndexMaintLogDetails - Keeps a record of individual index maintenance activities. It contains one time-stamped row for each individual index operation (e.g., a single index rebuild). This is updated as each operation occurs, so that you have Live Insight into active index operations.