3

I have a question to the missig index dmv.

BOL says:

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?

  • 2
    What is the max memory configured on the server and how much total RAM your server has ? Is this the only instance running on this server or there are other instances running on the same server ? Remember that memory pressure can flush out data from this DMV. – Kin Shah Oct 13 '15 at 16:07
  • It is about 28 g of 32 in total. I did not know. TY – Magier Oct 13 '15 at 20:17
6

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+)

|improve this answer|||||
  • 1
    This is an interesting thing to me, so I just did some testing (2008 system) and rebuilding indexes did not clear this out. However removing an index from the same table or adding a new index (even when completely unrelated) did remove those records from the table. Obviously the counts can go up if new queries are run that go against other tables that require new indexes. I would be very interested in seeing officially what it is that removes records from this table, outside of those two that we know for sure do (structural change or index add/drop clearly do). – mskinner Oct 13 '15 at 19:50
  • 3
    I did my original test of index rebuild in a 2014 instance where the missing index info is removed after a rebuild(reorg does not remove the info). In 2008R2 index rebuilds do not remove the missing index info. – Bob Klimes Oct 13 '15 at 21:00
  • 1
    That is really good to know. Thank you for the response. – mskinner Oct 13 '15 at 21:25
  • I can confirm that the statistics for a table gets cleared as soon a new index was added (sql 2008 r2 sp2) – Magier Oct 21 '15 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.