This table holds index maintenance default settings at the table level. You may insert rows for individual tables to override the default index maintenance settings (per table).
Any table that does not have a value in this table will get all of its index maintenance settings from the Minion.IndexSettingsDB table. For example, if FillFactorOpt is set at 90 in Minion.IndexSettingsDB, but a row for Table1 here has FillFactorOpt at 95, then the 95 value is used. (If FillFactorOpt is left at NULL in the Minion.IndexSettingsTable row, the database level setting is still not used. Instead, the current index setting in sys.indexes will be used.)
Note that many shops will have no values in this table, if there is no need for ordering the tables for reindex, or for setting options for specific tables.
Use: Insert a new row for each individual table that requires specific table-level values for index maintenance.
Name | Type | Description |
ID | int | Primary key row identifier. |
DBName | Varchar | Database name. |
SchemaName | varchar | Schema name. |
TableName | Varchar | Table name. |
Exclude | bit | Exclude table from index maintenance. For more on this topic, see "How To: Exclude Databases from Index Maintenance". |
GroupOrder | int | Group to which this table belongs. Used solely for determining the order in which tables should be processed for index maintenance. By default, all tables 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. 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 tables should be processed for index maintenance. By default, all tables 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 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 |
GetRowCT | Bit | Get a rowcount for this table. |
GetPostFragLevel | bit | Get the level of fragmentation for each index, after the index maintenance operations are complete. |
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 table 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. |
IndexScanMode | Varchar | Valid inputs: Detailed Limited NULL A value of NULL indicates that reindexing should use the default (in this case, "LIMITED"). |
TablePreCode | varchar | Code to run for this table, before the index maintenance operations begin for that table. Note: To run precode once before each and every individual table in a database, use the TablePreCode column in Minion.IndexSettingsDB. For more on this topic, see "How To: Run code before or after index maintenance". |
TablePostCode | varchar | Code to run for this table, after the index maintenance operations complete for that table. Note: To run postcode once after each and every individual table in a database, use the TablePreCode column in Minion.IndexSettingsDB. 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 table. The overall index maintenance 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 table. 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. |
PartitionReindex | bit | Future use. |
isLOB | bit | Internal use. |
TableType | char | Internal use. |
IncludeUsageDetails | bit | Save index usage details from sys.dm_db_index_usage_stats, to Minion.IndexMaintLogDetails. |
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 a single table in [YourDatabase], if you wish to specify different default values for the reorg threshold, rebuild threshold, fill factor, and so on.
Important: Any row inserted for an individual table overrides only ALL of the values for that table, whether or not they are specified. Refer to the following for an example:
ID DBName SchemaName TableName Exclude ReorgThreshold
1 YourDatabase dbo Table1 0 15
The first row specifies values for Table1 in YourDatabase. This row completely overrides all other values for that table.
When index operations are performed for Table1, only the values from the Table1 row will be used. Even though the ReorgThreshold value may be specified in Minion.IndexSettingsDB for [YourDatabase] – and there is most definitely a default value specified there - Table1 will not use that database-level value.
Usage examples:
Example 1: Set custom thresholds, fill factor, and PadIndex for Table1.
INSERT INTO [Minion].[IndexSettingsTable]
( DBName
, SchemaName
, TableName
, Exclude
, ReorgThreshold
, RebuildThreshold
, FILLFACTORopt
, PadIndex )
VALUES
('YourDatabase' -- DBName
, 'dbo' -- SchemaName
, 'Table1' -- TableName
, 0 -- Exclude
, 15 -- ReorgThreshold
, 25 -- RebuildThreshold
, 90 -- FILLFACTORopt
, 'ON' -- PadIndex
); NOTE: To ensure a table is reindexed at every run, set the ReorgThreshold at 0%.