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:
- Open the Idera SQL Admin Toolset Launchpad, and then click Index Analyzer.
- Specify the SQL Server instance whose indexes you want to check.
- Check Load Selectivity while loading Statistics to load selectivity statistics now.
- Click Load Statistics.
- View the index data displayed on the Index Statistics tab. To sort the indexes, use the View Options in the right pane.
- To view details about the index columns, click the Column Statistics tab.
- To view index data for a specific table, expand the instance node in the tree pane, and then click the appropriate table.
- 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).
- 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 > >