Updated August 29, 2021.
Purpose
To see the missing indexes information that the SQL server reports.
Sources
This feature queries the 3 DMV’s sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_details which together returns the details about the indexes that the SQL server believes it would benefit for if they were added.
Be aware that the missing indexes reported by the DMV’s are not at all something you should just use.
Treat them as indicators to where there might by something that could be optimized. And then use your DBA skills and knowledge to decide how the indexes really should look like.
Reports
The report page Missing Indexes has a table which shows the indexes that the SQL server might benefit from.
When hovering over the table a tooltip is showing the number of user seeks in the past where the SQL server might have used the suggested index if it was implemented.
Model
The model for the report uses the fact [Missing indexes] and the dimensions [Date] and [Object].
The fact view [Missing indexes] 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
There is no configuration for this feature.
Scheduling
The default schedule for the feature is once every hour throughout the entire day.
Retention
The default retention for the table dbo.fhsmMissingIndexes is 90 days.
