How to analyze the health of SQL Server database indexes

Peter V.
15 Dec 2020

To keep databases running smoothly, it is important to evaluate the properties and performance statistics of indexes of databases.

For this purpose, display these statistics of indexes:

  • The percentage of rows modified since statistics were last updated.
  • The percentage of updates to the total number of times the index is accessed.
  • The columns affected by the statistic.
  • Whether the index is clustered or disabled.
  • The name of the database and table containing the index.
  • The fill factor used to create the index.
  • The name of the index.
  • An estimate of the likelihood of using the index.
  • The date and time when the statistics for the index was last updated.
  • The number of lookups performed on the index.
  • The number of pages used by the index.
  • The number of rows in the index.
  • The number of rows modified since the statistics were last updated.
  • The number of scans and seeks performed on the index.
  • The statistical uniqueness of each row of the index.
  • The physical size of the index in bytes.
  • The total number of times the index is accessed.
  • The number of updates to the index.

Also, display these statistics of index columns:

  • The columns affected by the statistic.The name of the database from which the statistic was generated.
  • The name of the database from which the statistic was generated.
  • The number of updated, inserted or deleted rows.The statistical uniqueness of each row of the statistic.
  • The name of the statistic calculated for the column.The name of the table from which the statistic was generated.
  • The name of the table from which the statistic was generated.

The Index Analyzer tool of SQL Admin Toolset displays indexes contained in a specific table, and displays index properties and performance statistics across instances of SQL Server.

This is how easy it is:

  1. Connect one or more instances.
  2. Calculate the statistics using all rows or using sampling.
  3. Display the statistics for the indexes.
  4. Optionally retrieve the selectivity.
  5. Sort the indexes by using the view options.
  6. Display the statistics of the index columns.
  7. Select a table to view its index data.
  8. Copy the results to the Windows Clipboard, and save the results as an XML and CSV text file.

Watch the video for more details: