This table holds index maintenance default settings at the default and database levels. You may insert rows for individual databases to override the default index maintenance settings (per database).
Minion.IndexSettingsDB is installed with default settings already in place, via the system-wide default row (identified by DBName = "MinionDefault"). If you do not need to fine tune the reindexing process at all, no action is required, and all maintenance will use this default configuration.
Important: Do not delete the MinionDefault row, or rename the DBName column for this row!
To override these default settings for a specific database, insert a new row for the individual database with the desired settings. Note that any database with its own entry in Minion.IndexSettingsDB retrieves ALL its configuration data from that row. For example, if you enter a row for [YourDatabase] and leave the FILLFACTORopt at NULL, Minion Reindex does not retrieve that value from the "MinionDefault" row; in this case, fill factor for YourDatabase would default to the current index setting (viewable for that index in sys.indexes).
Name | Type | Description |
ID | int | Primary key row identifier. |
DBName | varchar | Database name. |
Exclude | Bit | Exclude database from index maintenance. For more on this topic, see "How To: Exclude Databases from Index Maintenance". |
ReindexGroupOrder | Tinyint | Group to which this database belongs. Used solely for determining the order in which databases should be processed for index maintenance. By default, all databases have a value of 0, which means they’ll be processed in the order they’re queried from sysobjects. Higher numbers have a greater "weight" (they have a higher priority), and will be indexed earlier than lower numbers. The range of ReindexGroupOrder weight numbers is 0-255. For more information, see "How To: Reindex databases in a specific order". |
ReindexOrder | Int | The index maintenance order within a group. Used solely for determining the order in which databases should be processed for index maintenance. By default, all databases have a value of 0, which means they’ll be processed in the order they’re queried from sysobjects. Higher numbers have a greater "weight" (they have a higher priority), and will be indexed earlier than lower numbers. We recommend leaving some space between assigned reindex order numbers (e.g., 10, 20, 30) so there is room to move or insert rows in the ordering. For more information, see "How To: Reindex databases in a specific order". |
ReorgThreshold | tinyint | The percentage threshold at which Index Maintenance should reorganize an index. For example, if ReorgThreshold is set to 10 and the RebuildThreshold is 20, then a reorg will be done for all indexes between 10 and 19. And a rebuild will be done for all indexes 20 and above. |
RebuildThreshold | tinyint | The percentage threshold at which Index Maintenance should rebuild an index. For example, if ReorgThreshold is set to 10 and the RebuildThreshold is 20, then a reorg will be done for all indexes between 10 and 19. And a rebuild will be done for all indexes 20 and above. |
FILLFACTORopt | Tinyint | Specify how full a reindex maintenance should make each page when it rebuilds an index. For example, a value of 85 would leave each data page 85% full of data. A value of NULL indicates that reindexing should use the current index setting (viewable for that index in sys.indexes). |
PadIndex | varchar | Turn PAD_INDEX on or off. Valid inputs: ON OFF NULL A value of NULL indicates that reindexing should use the current index setting (viewable for that index in sys.indexes). |
ONLINEopt | Varchar | Perform ONLINE index maintenance for indexes in this database. Valid inputs: ON OFF NULL A value of NULL indicates that reindexing should use the system setting (in this case, "OFF", meaning the index maintenance will be done offline). Note that ONLINE index operations may not be possible for certain editions of SQL Server, and only for indexes that are eligible for ONLINE index operations. If you specify ONLINE when it is not possible, the routine will change it to OFFLINE. |
SortInTempDB | Varchar | Direct index maintenance to use TempDB to store the intermediate sort results that are used to build the index. Valid inputs: ON OFF NULL A value of NULL indicates that reindexing should use the system setting (in this case, "OFF"). |
MAXDOPopt | Tinyint | Specify the max degree of parallelism ("MAXDOP", the number of CPUs to use) for the index maintenance operations. If specified, this overrides the MAXDOP configuration option for the duration of the index operation. |
DataCompression | Varchar | The data compression option. The options are as follows: Valid inputs: NONE ROW PAGE COLUMNSTORE COLUMNSTORE_ARCHIVE A NULL value here would indicate DataCompression=’NONE’. |
GetRowCT | Bit | Get a rowcount for each table. |
GetPostFragLevel | Bit | Get the fragmentation level for each index, after the index maintenance operations are complete. This is done on a per index basis as soon as the reindex operation is complete for each index. |
UpdateStatsOnDefrag | Bit | Update statistics after defragmenting. This should always be on, but Minion provides the option just in case your stats are handled in some other way. |
StatScanOption | varchar | Options available for the UPDATE STATISTICS statement (that is, anything that would go in the "WITH" statement). Valid inputs include any of the following options, as a comma-delimited list: FULLSCAN SAMPLE … RESAMPLE ON PARTITIONS ... STATS_STREAM ROWCOUNT PAGECOUNT For example, StatScanOption could be set to "SAMPLE 50 PERCENT", or "FULLSCAN, NORECOMPUTE". |
IgnoreDupKey | varchar | Change the option so that for this index, inserts that add (normally illegal) duplicates generate a warning instead of an error. Applies to inserts that occur any time after the index operation. The default is OFF. Valid inputs: ON OFF |
StatsNoRecompute | Varchar | Disable the automatic statistics update option, AUTO_UPDATE_STATISTICS. Valid inputs: ON OFF |
AllowRowLocks | Varchar | Enable or disable the ALLOW_ROW_LOCKS option of ALTER INDEX. See http://msdn.microsoft.com/en-us/library/ms188388.aspx Valid inputs: ON OFF |
AllowPageLocks | varchar | Enable or disable the ALLOW_PAGE_LOCKS option of ALTER INDEX. See http://msdn.microsoft.com/en-us/library/ms188388.aspx Valid inputs: ON OFF |
WaitAtLowPriority | Bit | Enable or disable the WAIT_AT_LOW_PRIORITY option of ALTER INDEX. See http://msdn.microsoft.com/en-us/library/ms188388.aspx |
MaxDurationInMins | int | Set the MAX_DURATION option of ALTER INDEX. See http://msdn.microsoft.com/en-us/library/ms188388.aspx |
AbortAfterWait | Varchar | Enable or disable the ABORT_AFTER_WAIT option of ALTER INDEX. See http://msdn.microsoft.com/en-us/library/ms188388.aspx Valid inputs: NONE SELF BLOCKERS |
PushToMinion | Bit | Save these values to the central Minion server, if it exists. Modifies values for this particular database on the central Minion server. A value of NULL indicates that this feature is off. Functionality not yet supported. |
LogIndexPhysicalStats | Bit | Save the current index physical stats to a table (Minion.IndexPhysicalStats). |
IndexScanMode | Varchar | Valid inputs: Detailed Limited NULL A value of NULL indicates that reindexing should use the default (in this case, "LIMITED"). |
DBPreCode | Nvarchar | Code to run for a database, before the index maintenance operations begin for that database. For more on this topic, see "How To: Run code before or after index maintenance". |
DBPostCode | nvarchar | Code to run for a database, after the index maintenance operations complete for that database. For more on this topic, see "How To: Run code before or after index maintenance". |
TablePreCode | Nvarchar | Code to run for each and every table, before the index maintenance operations begin for that table. Note: To run precode just once, before maintenance for the database begins, use the DBPreCode column. For more on this topic, see "How To: Run code before or after index maintenance". |
TablePostCode | Nvarchar | Code to run for each and every table, after the index maintenance operations end for that table. For more on this topic, see "How To: Run code before or after 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. |
LogRetDays | Smallint | Number of days to retain index maintenance log data, for this database. Just like any setting, if a table-specific row exists (in Minion.IndexSettingTable), those settings take precedence over database level settings. That is, if DB1.Table1 has an entry for LogRetDays=50, and DB1 has an entry for LogRetDays=40, the log will keep 50 days for DB1.Table1. When first implemented, Minion Reindex defaults to 60 days of log retention. |
LogLoc | Varchar | Determines whether log data is only stored on the local (client) server, or on both the local server and the central Minion (repository) server. Valid inputs: Local Repo |
MinionTriggerPath | varchar | UNC path where the Minion logging trigger file is located. Not applicable for a standalone Minion Reindex instance. |
RecoveryModel | Varchar | Change the recovery model of the database for the duration of the index maintenance operation. After index maintenance operations, the database will be set back to its original recovery model. Valid inputs: FULL BULK_LOGGED SIMPLE WARNING: While we have done extensive testing and checking for this feature, it may still be possible for the process to fail in such a way that a database changed (for example) from FULL to SIMPLE may not switch back. Therefore, we advise that if you’re in FULL you switch to BULK_LOGGED instead. It won’t break your log chain and it has the same effect as switching to SIMPLE. |
IncludeUsageDetails | Bit | Save index usage details from sys.dm_db_index_usage_stats, to Minion.IndexMaintLogDetails. This feature is useful for tracking which indexes are being used the most over time. |
StmtPrefix | nvarchar | This column allows you to prefix every reindex statement with a statement of your own. This is different from the table precode and postcode, because it is run in the same batch. Whereas, precode and postcode are run as completely separate statements, in different contexts. Code entered in this column MUST end in a semicolon. For more information, see "How To: Run code before or after index maintenance". |
StmtSuffix | nvarchar | This column allows you to suffix every reindex statement with a statement of your own. This is different from the table precode and postcode, because it is run in the same batch. Whereas, precode and postcode are run as completely separate statements, in different contexts. Code entered in this column MUST end in a semicolon. For more information, see "How To: Run code before or after index maintenance". |
Discussion:
Insert a new row for [YourDatabase], if you wish to specify different default values for the reorg threshold, rebuild threshold, fill factor, and so on.
Discussion:
The Minion.IndexSettingsDB table comes with a row with "MinionDefault" as the DBName value. This row defines the system-wide defaults.
Important: Any row inserted for an individual database overrides only ALL of the values, whether or not they are specified. Refer to the following for an example:
ID DBName Exclude ReorgThreshold RebuildThreshold FillFactorOpt
1 MinionDefault 0 10 20 90
2 YourDatabase 0 15 25 NULL
The first row, "MinionDefault", is the set of default values to use for all the databases in the SQL Server instance. These values will be used for index maintenance for all databases that do not have an additional row in this table.
The second row, [YourDatabase], specifies some values for YourDatabase. This row completely overrides the "DefaultMinion" values for YourDatabase.
When index operations are performed for YourDatabase, only the values from the YourDatabase row will be used. So, even though the system-wide default (as specified in the MinionDefault row) for Fill Factor is 90%, YourDatabase will not use that default value. Because Fill Factor is NULL for YourDatabase, index maintenance will use the current value specified for the index.
You can find the current value for a specific index by running the following query:
SELECT * FROM sys.indexes WHERE name = 'nonMyIndex'
Likewise, you can also specify table-level override settings in the Minion.IndexSettingsTable table, which will override any settings for that particular table (and ignore the settings in Minion.IndexSettingsDB).
NOTE: While it is possible to exclude a single database from reindexing, by setting both the ReorgThreshold and RebuildThreshold above 100% for that database, we do not recommend this approach. This would cause Minion Reindex to gather fragmentation stats that will never be used. Instead, set the Exclude column to 1 for that database.
Likewise, we do not recommend setting the thresholds at 0%. While this would guarantee that every index in the database would be reorganized at every maintenance execution, it would likely be an unnecessary waste of resources.
Usage examples:
Example 1: Set custom thresholds, fill factor, and PadIndex for database ‘YourDatabase’.
INSERT INTO [Minion].[IndexSettingsDB]
( DBName
, Exclude
, ReorgThreshold
, RebuildThreshold
, FILLFACTORopt
, PadIndex )
VALUES
('YourDatabase' -- DBName
, 0 -- Exclude
, 15 -- ReorgThreshold
, 25 -- RebuildThreshold
, 90 -- FILLFACTORopt
, 'ON' -- PadIndex
);Example 2: Set custom reindex settings, and enable additional logging options for database ‘YourDatabase’.
INSERT INTO [Minion].[IndexSettingsDB]
( DBName
, Exclude
, ReorgThreshold
, RebuildThreshold
, FILLFACTORopt
, PadIndex
, SortInTempDB
, UpdateStatsOnDefrag
-- Logging options:
, GetRowCT
, GetPostFragLevel
, LogIndexPhysicalStats
, LogProgress
, LogRetDays
, LogLoc)
VALUES
('YourDatabase' -- DBName
, 0 -- Exclude
, 15 -- ReorgThreshold
, 25 -- RebuildThreshold
, 90 -- FILLFACTORopt
, 'ON' -- PadIndex
, 'ON' -- SortInTempDB
, 1 -- UpdateStatsOnDefrag
, 1 -- GetRowCT
, 1 -- GetPostFragLevel
, 1 -- LogIndexPhysicalStats
, 1 -- LogProgress
, 90 -- LogRetDays
, 'Local' -- LogLoc
);