Index Statistics

  • Updated on Apr 8, 2025
  • Published on Mar 19, 2025
  • 2 minute(s) read
Prev Next

Index Statistics - Details

The Index Statistics - Details screen does not have an Overview version, so an Instance and Database or a Database and Table must be selected from the filters at the top of the screen before any data is displayed.

This data is collected via a weekly delta of the sys.dm_db_index_usage_stats DMV. As a result, no data will be displayed for instances that have been in WISdom for less than a week.

This data, over time, becomes an excellent way to find unused or underused indexes.

  • Use the drop-down menus to further filter the list by Table, Compressed, Unique, and Tag.

Index Details

This card displays the Index Statistics for the selected Instance and Database or Database and Table in a table format with sortable columns, as follows:

  • The Instance column displays the name of the instance.
  • The Database column displays the name of the database.
  • The Table column displays the name of the table.
  • The Index column displays the name of the index.
  • The Seeks/day column displays the average number of user seeks against that index per day.
  • The Scans/day column displays the average number of user scans against that index per day.
  • The Lookups/day column displays the average number of user lookups against that index per day.
    • Note that high numbers here might indicate an opportunity for optimization of that index.
  • The Updates/day column displays the average number of user updates against that index per day.
  • The Index Size displays the size of that index as of the last collection.
  • The Yearly Cost displays the cost in dollars for the storage of that index.
    • This can be customized via Cost Management in the Admin Console.

Note: Click on column headings to sort the list in ascending or descending order.

Click on a row to display the Index Details flyout for more detailed information.

Index Details Flyout

This flyout displays more details for the selected index and is made up of two tabs: Details and SQL Statement.

Details Tab

The Details tab is made up of four cards:

The Index Name card displays the name of the selected index in its header, along with fields for Instance, Database, Table, Index ID, Compressed, and Unique.

The SQL Statement card displays the preview of the SQL statements to create and disable the index. This card includes buttons to view full-screen and copy to clipboard.

The Index Statistics card displays the Last Used date and time, Fill Factor, Index Size, and Yearly Cost.

The Index Columns card displays the columns that make up the index. Columns are in order, and members of the key are shown in green and included columns are shown in orange.

SQL Statement

The SQL Statement tab displays a full-flyout view of the statements to disable and create the selected index and includes a copy to clipboard button at the top-right.