Updated August 29, 2021.
Purpose
To get a baseline of the index usage and to see how the usage changes when indexes are added or modified.
Sources
This feature queries the DMV sys.dm_index_usage_stats which returns counts of different types of index operations and the time each type of operation was last performed.
Reports
The report page Index Usage has a line chart showing the number of User lookups, User scans, User seeks and User updates plus a table showing the detailed data.
The x-axis for the charts is at the lowest granularity which by default is 4 hours interval.
The report has 4 buttons to select the level where the tooltips are showing data. For this feature Database, Schema, Object and Index can be selected.
When hovering over the chart a tooltip is showing detailed insight by showing the top 5 databases, schemas, objects or indexes (depending on the tooltip button selections) with regards of user lookups, user scans, user seeks and user updates. The top 5 ranking is based on the sum of the 4 operations.
Database
Schema
Object
Index
Model
The model for the report uses the fact [Index usage] and the dimensions [Date] and [Index].
The fact view [Index usage] in the database contains all values from the DMV, but as good practice dictates it, it is only the values used that are loaded into Power BI report.
Configuration
The feature accept the parameter @Databases and the syntax is the same that is used in the SQL Server Index and Statistics Management solution made by Ola Hallengren.
The default parameter for the feature is @Databases = ‘USER_DATABASES, msdb'”
| Value | Description |
|---|---|
| SYSTEM_DATABASES | All system databases (master, msdb, model, and tempdb) |
| USER_DATABASES | All user databases |
| ALL_DATABASES | All databases |
| Db1 | The database Db1 |
| Db1, Db2 | The databases Db1 and Db2 |
| USER_DATABASES, -Db1 | All user databases, except Db1 |
| %Db% | All databases that have “Db” in the name |
| %Db%, -Db1 | All databases that have “Db” in the name, except Db1 |
| ALL_DATABASES, -%Db% | All databases that do not have “Db” in the name |
Scheduling
The default schedule for the feature is every 4 hours between 06:00 and 24:00
Retention
The default retention for the table dbo.fhsmIndexUsage is 90 days.
