I have a question to the missig index dmv.
Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling.
I basically do this using the following (slihghtly simplified) query:
SELECT <SomeFieldsAndCalculations> FROM sys.dm_db_missing_index_details AS d
INNER JOIN sys.dm_db_missing_index_groups AS g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s ON g.index_group_handle = s.group_handle
INNER JOIN Database1.sys.objects o on d.[object_id] = o.[object_id]
INNER JOIN Database1.sys.schemas sch on sch.[schema_id] = o.[schema_id]
The results are written to a table once per day from ten sql servers (with correct grouping options…) .
Looking at this table I see that the number of missing indexes varies a lot.
These are the total number of missing indexes (over multiple instances) in a row:
3091 3133 3090 3135 3131 2506 2750 2028 2032 2195 2274 2269 2272 2373
The services have not been restarted.
From the introduction from BOL I could understand a very slight variation from one day to another. Assuming a missing index popping up a day should still be in the dmv the next day, how can the total count ever vary that much? For example decreasing from 3131 to 2506 wihtin 24 hours? That's a change of about 60 per instance… Same with the raising number from 2272 to 2373 – suddenly 10 additional indexes missing per instance wihthin 24 hours…?
No indexes have been created during that period.
But unused indexes have been removed during that period.
I can't see a relation between removing unsused and missing indexes…
Questions:
-
How long will missing indexes information be returned from the above query if the Sql Service is runnung without interruption?
-
What could have influenced the number of missing indexes?
Best Answer
Missing index info will be cleared from the view if the referenced table has changed. Added columns, new indexes would clear out this data. When you remove indexes, any missing index info for that table will also be removed.
Index rebuilds also clears out this info, so if you are doing nightly index maintenance, this may contribute to the change in numbers. (edit: rebuilds only clear missing index info on 2012+)