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.
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.
The following index statistics are available for viewing in the Index Analyzer tool results:
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.
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.
Indicates which columns were affected by the statistic.
Indicates whether the index is clustered.
Provides the name of the database containing the index.
Indicates whether the index is disabled.
Provides the Fill Factor used to create the index.
Provides the name of the index.
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.
Indicates when SQL Server last updated statistics for the index.
Provides the number of lookups performed on the index.
Provides the number of 8K pages used by the index.
Provides the number of rows in the index.
Provides the number of rows modified since SQL Server last updated these statistics.
Provides the number of scans performed on the index.
Provides the number of seeks performed on the index.
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".
Provides the physical size of the index in bytes.
Provides the name of the table containing the index.
Sums the total number of times the index is accessed (Seeks + Scans + Lookups + Updates).
Provides the number of updates to the index.
The following column statistics are available:
Indicates which columns were affected by the statistic.
Provides the name of the database from which the statistic was generated.
Provides the number of rows that were updated, inserted, or deleted.
Indicates the statistical uniqueness of each row of the statistic. Low Selectivity may negatively impact system performance.
Provides the name of the SQL Server statistic calculated for this column.
Provides the name of the table from which the statistic was generated.
Idera Website | Products | Purchase | Support | Resources | Community | About Us | Legal |
---|