This blog is part of the blog series named SQL Server Monitoring.
Purpose
To get a list of how the tables and indexes are configured, if they are heap or clustered, are they primary key, unique, filtered, …
The report shows the number of rows per table if the report is not drilled down to the index level, but if drilled down to the index level it is the number of rows pr. index that are shown, and this will be different to the number of rows in the table if the index is filtered.
Report
The report has a table matrix showing all indexes and their configuration.
The following settings will be highlighted with green or yellow background depending on their settings:
| Setting | Description | Background color |
|---|---|---|
| Pri. | Index is a primary key index | Green |
| Unq. | Index is a unique index | Green if unique. Yellow if index is clustered and not unique. |
| Dis. | Index is disabled | Yellow |
| Hyp. | Index is hypothetical | Yellow |
Sources
This feature queries the DMV sys.indexes which returns information about the configuration of the indexes.
Model
The model for the report uses the fact [Index configuration] and the dimensions [Index], [Table type] and [Object].
As a auxiliary fact [Table size] is used to provide information about number of rows and data and index size usage.
Configuration
The report uses the same data as used by the Index usage report.
