CODEWITHSUNDEEP
sql-server-2008-r2
georged
georged

Reputation: 1358

Missing indexes statistics is missing on SQL Server 2008 R2

I have an installation of SQL Server 2008 R2 (one of a few) and it's reasonably busy system. I'm trying to optimise some of the indexes like I've done in the past by using information from missing indexes tables.

What seems to be strange is that sys.dm_db_missing_index_group_stats table is empty?!

Now I don't believe for a second that none of the databases on that server misses a beat and other tables like sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_group_stats etc contain plenty of records.

Just now I've seen 1 (one) record briefly appearing in that table and it's gone since then.

I wonder if something is seriously wrong with that server or if I'm missing a trivial

Upvotes: 0

Views: 822

Answers (1)

Milan
Milan

Reputation: 340

Seems to be a bug in SQL 2008 R2. Search google for "sys.dm_db_missing_index_group_stats empty" (regards to Mike West).

I`ll try to explain what happens.

There are 4 DMOs (Dynamics Management Objects) which are used in the Missing Indexes Feature of SQL Server.
Missing index details are held in these DMOs, until purged. The purge is triggered when you actually create the missing index (or restart the server).

But, there is a limit of 600 rows that can be held within these DMOs.

This, in conjuction with the fact that the sys.dm_db_missing_index_group_stats DMO gathers data only for executed queries, wheres the other three gather data for both compiled and for executed queries introduces this "bug".

The workaround suggests that the sys.dm_db_missing_index_details dmv be queried and all suggested indexes be created with "STATISTICS_ONLY = -1" and then dropped. This would restart the index recommendations gathering.

More explanation here, and a workaround here.

Upvotes: 1

Related Questions