This blog is part of the blog series named SQL Server Monitoring.
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.
Report
The report are split in 3 parts.
In the upper part a line chart showing the number of seeks, updates, lookups and scans over time.
In the middle part a table shows the index usage per database and with the possibility to drill down to schema, object and index level.
In the lower part a table are showing the indexes that has not been used at all when looking at the collected data.
Model
The model for the report uses the facts [Index usage], [Index not used] and [Table size] and the dimensions [Date], [Time], [Index] and [Object].
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.
