Minion.IndexTableFrag
Holds index fragmentation information on a short-term basis, to be used by the currently-running index maintenance process. Minion.IndexTableFrag also holds fragmentation data for prepped operations (created with Minion.IndexMaintMaster with @PrepOnly = 1). PrepOnly data is marked with Prepped = 1 in this table, so Minion Reindex knows the difference between a current process and a prepped process.
For more information on these columns, see Minion.IndexMaintDB, Minion.IndexMaintTable, and/or the MSDN article on sys.dm_db_index_physical_stats at http://msdn.microsoft.com/en-us/library/ms188917.aspx
| Name | Type | Description |
| ExecutionDateTime | Datetime | The execution date and time, common to the entire run of a database index maintenance event. |
| DBName | varchar | Database name. |
| DBID | int | Database ID. |
| TableID | bigint | Table ID. |
| SchemaName | varchar | Schema name. |
| TableName | varchar | Table name. |
| IndexName | varchar | Index name. |
| IndexID | bigint | Index ID. |
| IndexType | tinyint | Index type number, e.g. 0 = HEAP, etc. See http://msdn.microsoft.com/en-us/library/ms173760.aspx |
| IndexTypeDesc | nvarchar | Description of index type, e.g. HEAP, CLUSTERED, NONCLUSTERED, etc. See http://msdn.microsoft.com/en-us/library/ms173760.aspx |
| IsDisabled | bit | See http://msdn.microsoft.com/en-us/library/ms173760.aspx |
| IsHypothetical | bit | See http://msdn.microsoft.com/en-us/library/ms173760.aspx |
| avg_fragmentation_in_percent | float | See http://msdn.microsoft.com/en-us/library/ms188917.aspx |
| 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 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 | See http://msdn.microsoft.com/en-us/library/ms188388.aspx |
| AllowPageLocks | varchar | See http://msdn.microsoft.com/en-us/library/ms188388.aspx |
| WaitAtLowPriority | bit | See http://msdn.microsoft.com/en-us/library/ms188388.aspx |
| MaxDurationInMins | int | See http://msdn.microsoft.com/en-us/library/ms188388.aspx |
| AbortAfterWait | varchar | See http://msdn.microsoft.com/en-us/library/ms188388.aspx |
| 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 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. |
| 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 (Minion.IndexPhysicalStats). |
| IndexScanMode | varchar | Valid inputs: Detailed Limited NULL A value of NULL indicates that reindexing should use the default (in this case, “LIMITED”). |
| TablePreCode | nvarchar | Code to run for a table, before the index maintenance operations begin for that table. For more on this topic, see “How To: Run code before or after index maintenance”. |
| TablePostCode | nvarchar | Code to run for a table, after the index maintenance operations complete for that table. For more on this topic, see “How To: Run code before or after index maintenance”. |
| Prepped | bit | If Prepped=1, this data was entered into the table as a result of running the Minion.IndexMaintMaster stored procedure with @PrepOnly = 1. It is then necessary to run the reindexing routine with @RunPrepped = 1 to use this data. For more on this topic, see “How To: Gather index fragmentation statistics on a different schedule from the reindex routine”. NOTE: There can only be one set of prepared data per database at any given time. When you run @PrepOnly = 1, it enters the data into this table, and deletes any previous prep runs for the database in question. So while you can have as many databases as you like prepped in this table, each database can only have a single prep run. |
| GroupOrder | Int | Group to which this database belongs. Used solely for determining the order in which databases should be processed for index maintenance. By default, all tables have a value of 0, which means they’ll be processes 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 tables have a value of 0, which means they’ll be processes 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”. |
| StmtPrefix | nvarchar | The code that will prefix every reindex statement with a statement of your own. For more information, see “How To: Run code before or after index maintenance” |
| StmtSuffix | nvarchar | The code that will suffix every reindex statement with a statement of your own. For more information, see “How To: Run code before or after index maintenance” |