Get detailed index information from the Index Stats module. It collects all data from sys.indexes, plus the list of indexed columns (and included columns), and more. This allows you to perform detailed, enterprise-wide index analysis. Check the Collector.IndexStatsCurrent view for the latest set of index information.

NOTE: The Report.IndexFragmentation% stored procedures (for example, Report.IndexFragmentationByAll) are not a part of the Index Stats Module, but instead rely on information collected as part of integrating Minion Reindex. See the Index Maintenance Module section for information about these other stored procedures.

NOTE: The Index Stats module does not retrieve index fragmentation information. This is the domain of the Index Maintenance Module, which integrates Minion Reindex with this enterprise solution.

Table:

  • Collector.IndexStats – Stores the collections of index data.

Stored procedures:

  • GUIDs: The GUID reports detail those tables with clustered UNIQUEIDENTIFIERS. The report includes: the instance informantion, database name, schema name, table name, index name, index column name(s), and the primary key default constraint (if any; e.g., newsequentialid()).
    • Report.IndexClusteredGUIDsByAll
    • Report.IndexClusteredGUIDsByApp
    • Report.IndexClusteredGUIDsByID
    • Report.IndexClusteredGUIDsByServerName
    • Report.IndexClusteredGUIDsBySLA
  • Duplicate indexes: The duplicate indexes report detail those tables with indexes that are exact duplicates of one another. The report includes: the instance informantion, database name, schema name, table name, index name, name of the duplicate index, index column name(s) for both indexes, include column name(s) for both instances, and a DROP INDEX statement for the duplicate index.
    • Report.IndexDuplicateIndexesByAll
    • Report.IndexDuplicateIndexesByApp
    • Report.IndexDuplicateIndexesByID
    • Report.IndexDuplicateIndexesByServerName
    • Report.IndexDuplicateIndexesBySLA
  • Heaps: The heaps report details those tables with no clustered index. The report includes: the instance informantion, database name, schema name, table name, the name of the IDENTITY column (if one exists), and the statement to create a clustered primay key on the IDENTITY column (if one exists).
    • Report.IndexHeapsByAll
    • Report.IndexHeapsByApp
    • Report.IndexHeapsByID
    • Report.IndexHeapsByServerName
    • Report.IndexHeapsBySLA
  • Low use: The low use report details those indexes that are very low use. These indexes are sometimes good candidates to be dropped; however, be careful not to drop indexes that are seasonally critical (e.g., for quarterly reports). The report includes: the instance informantion, database name, schema name, table name, index ID, index name, index type, index columns (and included columns), use information, and the statement to drop the index.
    • Report.IndexLowUseIndexesByAll
    • Report.IndexLowUseIndexesByApp
    • Report.IndexLowUseIndexesByID
    • Report.IndexLowUseIndexesByServerName
    • Report.IndexLowUseIndexesBySLA
  • Missing indexes: The missing indexes report details missing index information, pulled from SQL Server management views. These indexes are sometimes good candidates to create, but be sure to evaluate each suggestion against need and existing indexes. The report includes: the instance informantion, database name, object id, schema name, table name, index ID, index name, index type, index columns (and included columns), potential use information (missing user scans, seeks, etc.), and the statement to create the index.
    • Report.IndexMissingIndexesByAll
    • Report.IndexMissingIndexesByApp
    • Report.IndexMissingIndexesByID
    • Report.IndexMissingIndexesByServerName
    • Report.IndexMissingIndexesBySLA

Views:

  • Collector.IndexStatsCurrent – Provides the most recent collection of index data.
  • Collector.IndexStatsPrevious – Provides the next-to-most recent collection of index data.

Jobs:

  • CollectorIndexStats-GOLD – Calls the execucolumn IndexStatsGET.exe for all servers with ServiceLevel = ‘Gold’, to retrieve index information.
  • CollectorIndexStats-SILVER – Calls the execucolumn IndexStatsGET.exe for all servers with ServiceLevel = ‘Silver’, to retrieve index information.
  • CollectorIndexStats-BRONZE – Calls the execucolumn IndexStatsGET.exe for all servers with ServiceLevel = ‘Bronze’, to retrieve index information.

Executable:

  • IndexStatsGET.exe – Perform the index data collection for the servers in the specified service level. Log the results to Collector.IndexStats via bulk load.