Sometimes a user will complain about their query performance to which I'll copy the query to SSMS and then run the Estimated Execution Plan feature to see what the Optimizer is doing. Occasionally the Optimizer will recommend a missing index which does not exist in the sys.dm_db_missing_index_details DMV. I don't understand how these results can be different since the Optimizer is leveraged by the internal SQL engine when populating the DMV. Just to clarify the order of actions:

1) Restart the SQL instance

2) Run the user's problem query in SSMS until completion

3) Run the Display Estimated Execution Plan feature in SSMS for the same user query

4) Query the sys.dm_db_missing_index_details DMV

I would expect that any missing index suggested in #3 would also be returned in #4, but this is not the case. The same suggested index does not exist in #4. Any ideas as to what might cause this discrepancy?

Hello,

Reference:http://stackoverflow.com/questions/5467289/sys-dm-db-missing-index-details-returns-no-rows

Regards,

Summer

Thanks for responding, Summer. Unfortunately the information in the link (and sub-links) does not answer my question. From http://msdn.microsoft.com/en-us/library/ms345524(v=SQL.90).aspx Microsoft states:

The missing indexes feature consists of the following components:

•           A set of dynamic management objects that can be queried to return information about missing indexes.
•           The MissingIndexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing.

IMO the 2nd bullet is confirming that any missing index that shows up in an XML Showplan should also be present in sys.dm_db_missing_index_details. Do you agree? 

Just to further clarify...the situation I describe at the beginning of this post is not in violation of any stated Limitations for the missing indexes feature found at http://msdn.microsoft.com/en-us/library/ms345485(v=SQL.90).aspx:

1.  It is not intended to fine tune an indexing configuration.
2.         It cannot gather statistics for more than 500 missing index groups.
3.         It does not specify an order for columns to be used in an index.
4.         For queries involving only inequality predicates, it returns less accurate cost information.
5.         It reports only include columns for some queries, so index key columns must be manually selected.
6.         It returns only raw information about columns on which indexes might be missing.
7.         It can return different costs for the same missing index group that appears multiple times in XML Showplans.

Hello,

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.

Regards,
Fanny Liu

If you have any feedback on our support, please click here .

Hi,

It's because "Display Estimated Execution Plan" not executing query.

http://msdn.microsoft.com/en-us/library/ms191194%28v=sql.90%29.aspx
"When estimated execution plans are generated, the Transact-SQL queries or batches do not execute."

Reference:

- http://msdn.microsoft.com/en-US/library/ms345405%28v=sql.90%29.aspx