Why does SQL Server 2005 Dynamic Management View report a missing index when it is not?

Question

I'm using SQL Server 2005 and the the Dynamic Management View sys.dm_db_missing_index_details. It continues to tell me that Table1 really needs an index on ColumnX and ColumnY, but that index already exists! I've even dropped and re-created it a couple times to no avail.

More specifics: The view lists Column1 under equality_columns. Column2 is listed under inequality_columns, so the index I have created is:

create index IndexA on Table1 (Column1 asc, Column2 asc)

Isn't this exactly the index sys.dm_db_missing_index_details is telling me I need?

Answer 1

If you have dropped & created the index, sp_update_stats shouldn't affect it (problem is not with statistics)

Perhaps it's because of the DMV data is obsolete already Does Actual Execution Plan suggest the Missing Index in SSMS as well?

From Books Online

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.

To determine which missing index groups a particular missing index is part of, you can query the sys.dm_db_missing_index_groups dynamic management view by equijoining it with sys.dm_db_missing_index_details based on the index_handle column.

Answer 2

Random thought: What if one of the columns is better declared "DESC"?

This is useful for ORDER BY clauses and I've seen logical IO reduce by half.

Answer 3

Though this is quite an old post, I found a useful article on this here http://www.simple-talk.com/sql/database-administration/fine-tuning-your-database-design-in-sql-2005/ which references a good Microsoft article on the limitations of DMVs http://msdn.microsoft.com/en-us/library/ms345485.aspx