This blog is part of the blog series named SQL Server Monitoring.
Purpose
To see the missing indexes information that the SQL server reports.
Sources
This feature queries the 3 DMV’s, for SQL2019+ sys.dm_db_missing_index_group_stats_query and for pre-SQL2019 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.
For SQL2019+ the DMV sys.dm_db_missing_index_group_stats_query returns a handle to the last SQL statement executed that the missing index request relates to.
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.
Report
The report has a table which shows the indexes that the SQL server might benefit from.
When right-clicking on a row there is the possibility to go to the drill-though page “Index details”.
Index details – drill-through page
The Index details page shows in the upper half all Missing indexes asked for on the table in question, where the lower half shows the index usage of the indexes already configured on the table.
Model
The model for the report uses the fact [Missing indexes] and the dimensions [Date] and [Object].
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.
