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).
| Name | Type | Description |
| ID | int | Primary key row identifier. |
| ExecutionDateTime | datetime | Date and time the entire reindex operation took place. If the job were started through IndexMaintMaster then all databases in that run have the same ExecutionDateTime. If the job was run manually from Minion.IndexMaintDB, then this value will only be for this database. It will still have a matching row in the Minion.IndexMaintLog table. |
| Status | varchar | Current status of the index operation. If Live Insight is being used the status updates will appear here. When finished, this column will either read ‘Complete’ or ‘FATAL ERROR: error message’. The one exception is when the job has been run with PrepOnly = 1. When running with PrepOnly = 1, this column is updated with the index fragmentation gather stats. For example, say that you were pulling fragmentation stats for 7 indexes with PrepOnly = 1. The final status message would look something like this: ‘7 of 7: GATHERING FRAG STATS: dbo.fragment.ix_fragment2’. This shows you that all 7 of the fragmentation stats were collected. |
| DBName | Varchar | Database name. |
| TableID | bigint | The table ID in sysobjects. |
| SchemaName | Varchar | Schema name. |
| TableName | Varchar | Table name. |
| IndexID | Int | The index ID from sys.indexes. |
| IndexName | varchar | The index name from sys.indexes. |
| IndexTypeDesc | varchar | The index type description from sys.indexes. |
| IndexScanMode | varchar | Either NULL, Limited, or Detailed. NULL means that nothing was entered into the column in either Minion.IndexSettingsDB or Minion.IndexSettingsTable and therefore the default (Limited) was used. |
| Op | varchar | Operation. Valid inputs are Reorg or Rebuild. This is the type of operation performed in the current index. |
| OnlineOpt | varchar | NULL, On, Off. If NULL, then nothing was entered into either the Minion.IndexSettingsDB or Minion.IndexSettingsTable tables, and the default (OFF) is used. So the operation was either done offline or online. |
| ReorgThreshold | Tinyint | The percentage threshold at which Index Maintenance should reorganize an index. |
| ReindexThreshold | Tinyint | The percentage threshold at which Index Maintenance should rebuild an index. |
| FragLevel | tinyint | The fragmentation level of the current index at the time the fragmentation stats were taken. If they were taken earlier in the day as part of a PrepOnly run, then they may not match current fragmentation stats. |
| Stmt | nvarchar | The reindex statement that was run. |
| GroupOrder | int | Group to which this table belongs. Used solely for determining the order in which tables should be processed for index maintenance. Most of the time this will be 0. However, if you choose to take advantage of this feature a row in Minion.IndexSettingsTable will get you there. This is a weighted list so higher numbers are more important and will be processed first. For more information, see “How To: Reindex databases in a specific order”. |
| ReindexOrder | int | The ordering of the tables within the previous group. Most of the time this will be 0. However, if you choose to take advantage of this feature a row in Minion.IndexSettingsTable will get you there. This is a weighted list so higher numbers are more important and will be processed first. For more information, see “How To: Reindex databases in a specific order”. |
| PreCode | varchar | Any precode run before the table is processed. If the table has multiple indexes the precode will only be run once. |
| PostCode | varchar | Any postcode run after the table is processed. If the table has multiple indexes the postcode will only be run once. |
| OpBeginDateTime | datetime | Date and time the reindex statement began running. |
| OpEndDateTime | datetime | Date and time the reindex statement finished running. |
| OpRunTimeInSecs | int | How many seconds the reindex statement took. |
| TableRowCTBeginDateTime | datetime | Internal use. |
| TableRowCTEndDateTime | datetime | Internal use. |
| TableRowCTTimeInSecs | int | Internal use. |
| TableRowCT | bigint | The count of rows in the table. Therefore, all indexes for a single table will have the exact same row counts. |
| PostFragBeginDateTime | datetime | Date and time the post fragmentation statement began. The post fragmentation level is explained above in the Minion.IndexSettingsDB and Minion.IndexSettingsTable tables. |
| PostFragEndDateTime | datetime | Date and time the post fragmentation statement finished. The post fragmentation level is explained above in the Minion.IndexSettingsDB and Minion.IndexSettingsTable tables. |
| PostFragTimeInSecs | int | How many seconds the post fragmentation stats collection took. |
| PostFragLevel | tinyint | The fragmentation level of the index immediately after the reindex operation finished. This is an excellent way to see the effectiveness of your routines and whether you need to adjust your threshold levels for individual tables. |
| UpdateStatsBeginDateTime | datetime | Date and time update statistics began. This will only be populated if the operation is a REORG and the UpdateStatsOnDefrag column in either Minion.IndexSettingsDB or Minion.IndexSettingsTable is set to 1. The value should always be set to 1 unless you have a specific reason not to. |
| UpdateStatsEndDateTime | datetime | Date and time update statistics finished. This will only be populated if the operation is a REORG and the UpdateStatsOnDefrag column in either Minion.IndexSettingsDB or Minion.IndexSettingsTable is set to 1. The value should always be set to 1 unless you have a specific reason not to. |
| UpdateStatsTimeInSecs | int | How many seconds the update statistics statement took. |
| UpdateStatsStmt | varchar | The exact update statistics statement that was run. |
| PreCodeBeginDateTime | datetime | Date and time the precode for the table began. |
| PreCodeEndDateTime | datetime | Date and time the precode for the table finished. |
| PreCodeRunTimeInSecs | int | How many seconds the table precode took. |
| PostCodeBeginDateTime | datetime | Date and time the postcode for the table began. |
| PostCodeEndDateTime | datetime | Date and time the postcode for the table finished. |
| PostCodeRunTimeInSecs | bigint | How many seconds the table postcode took. |
| UserSeeks | bigint | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| UserScans | bigint | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| UserLookups | bigint | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| UserUpdates | bigint | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| LastUserSeek | datetime | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| LastUserScan | datetime | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| LastUserLookup | datetime | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| LastUserUpdate | datetime | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| SystemSeeks | bigint | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| SystemScans | bigint | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| SystemLookups | bigint | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| SystemUpdates | bigint | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| LastSystemSeek | datetime | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| LastSystemScan | datetime | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| LastSystemLookup | datetime | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| LastSystemUpdate | datetime | See http://msdn.microsoft.com/en-us/library/ms188755.aspx |
| Warnings | Varchar(max) | Reserved for future use. |
Discussion:
The data available in this log includes the status of the operation, the object information, the statement used, operation type, reorg and rebuild thresholds, index usage information, and more.