2

I'm confused by the results that I'm getting from sys.dm_db_missing_index_stats ...

The following query returns missing indexes that are actually created on the tables.

select * from sys.dm_db_missing_index_details where database_id = DB_ID('DatabaseName');

Has anyone else seen this issue??

Also, I noticed that the duplicate indexes that exist on the database are showing 0 for usage stats.

Any help would be greatly appreciated.

Jon Seigel
  • 468
  • 3
  • 18
electricsk8
  • 65
  • 5

2 Answers2

2

There is a bug related to this DMV, please see Paul Randal's blog post http://www.sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx

NicCain
  • 136
  • 1
0

Have you validated that the existing indexes are identical in every aspect with the recommendation, and they are enabled?

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
  • yep, pounded my head against this a few times. indexes are identical in every aspect. i believe that paul randal's blog post referenced above clears things up. i actually blindly applied this index based on the recommendation. i monitor usage stats to ensure that the index supports queries after having deployed. in this case, this appears to be one that is recommended but not used. – electricsk8 Jan 25 '11 at 20:02