The Index Analyzer tool helps you evaluate SQL Server indexing. With this tool, you can view indexes contained in a specific table, or view index properties and performance statistics across a SQL Server instance.

By default, Index Analyzer connects to the specified SQL Server instances using the credentials of your currently logged on Windows account, and then collects information and performance statistics about each index.

Understand selectivity

Selectivity is a critical statistic for analyzing index performance. However, retrieving selectivity from SQL Server is a time-intensive operation. To analyze selectivity across all indexes on the specified instances, click Load Selectivity while loading Statistics before you begin. To view selectivity values on a few indexes, load the other index statistics first, then select the indexes you want to analyze and click Load Selectivity.

How the Index Analyzer tool works

The following index statistics are available for viewing in the Index Analyzer tool results:

% Rows Modified

Provides the percentage of rows modified since statistics were last updated.

If this percentage is high, then SQL Server is using outdated information to make optimization decisions. A high percentage can negatively impact system performance.
You can reset this value by updating the index statistics.

% Updates to Total Access

Percent of Updates to Total Accesses. If an index is updated often, then the usefulness as an index is offset by the overhead of keeping the index updated.

Columns

Indicates which columns were affected by the statistic.

Clustered

Indicates whether the index is clustered.

Database

Provides the name of the database containing the index.

Disabled

Indicates whether the index is disabled.

Fill Factor

Provides the Fill Factor used to create the index.

Index

Provides the name of the index.

Index Usefulness

Provides an estimate of how likely it is that SQL Server uses the index based on the following criteria: Selectivity, % Rows Modified, and % Updates to Total Accesses.

      • Low usefulness indicates that the index is disabled, or one or more of the criteria are at critical levels
      • Medium usefulness indicates that one or more of the criteria are at warning levels
      • High usefulness indicates that all criteria are at acceptable values

Last Statistics Update

Indicates when SQL Server last updated statistics for the index.

Lookups

Provides the number of lookups performed on the index.

Pages

Provides the number of 8K pages used by the index.

Rows

Provides the number of rows in the index.

Rows Modified Since Last Update

Provides the number of rows modified since SQL Server last updated these statistics.

Scans

Provides the number of scans performed on the index.

Seeks

Provides the number of seeks performed on the index.

Selectivity

Indicates the statistical uniqueness of each row of the index. Selectivity is calculated from the index density stored by SQL Server (Selectivity = 1 - Density). Low Selectivity may negatively impact system performance.

If no statistics are stored in SQL Server, the value for this column is "No Statistics Available".

Size

Provides the physical size of the index in bytes.

Table

Provides the name of the table containing the index.

Total Accesses

Sums the total number of times the index is accessed (Seeks + Scans + Lookups + Updates).

Updates

Provides the number of updates to the index.

The following column statistics are available:

Columns

Indicates which columns were affected by the statistic.

Database

Provides the name of the database from which the statistic was generated.

Modified Rows

Provides the number of rows that were updated, inserted, or deleted.

Selectivity

Indicates the statistical uniqueness of each row of the statistic. Low Selectivity may negatively impact system performance.

Statistic

Provides the name of the SQL Server statistic calculated for this column.

Table

Provides the name of the table from which the statistic was generated.

Use the Index Analyzer tool

To use the Index Analyzer:

  1. Open the Idera SQL Admin Toolset Launchpad, and then click Index Analyzer.
  2. Specify the SQL Server instance whose indexes you want to check.
  3. Check Load Selectivity while loading Statistics to load selectivity statistics now.
  4. Click Load Statistics.
  5. View the index data displayed on the Index Statistics tab. To sort the indexes, use the View Options in the right pane.
  6. To view details about the index columns, click the Column Statistics tab.
  7. To view index data for a specific table, expand the instance node in the tree pane, and then click the appropriate table.
  8. To recalculate the statistics using a sample percentage of rows, click Update Statistics (using Sampling). To recalculate statistics using all rows, click Update Statistics (using Full Scan).
  9. If you want to save the results, copy the list to clipboard, or save the results as an XML or CSV file.

 

SQL Admin Toolset delivers 24 essential administration tools. Learn more > >

 

 

  • No labels