SQL Server hold information about missing indexes in a couple of dmv's.
sys.dm_db_missing_index_group_stats: holds information about the possible performance improvement when implementing a group of indexes
sys.dm_db_missing_index_groups: holds information about possible groups of indexes
sys.dm_db_missing_index_details: holds details about the missing indexes
sys.dm_db_missing_index_columns: holds the list of columns that could use indexes
This is a great feature but as always it has some limitations.
From the BOL:
- It is not intended to fine tune an indexing configuration.
- It cannot gather statistics for more than 500 missing index groups.
- It does not specify an order for columns to be used in an index.
- For queries involving only inequality predicates, it returns less accurate cost information.
- It reports only include columns for some queries, so index key columns must
be manually selected.- It returns only raw information about columns on which
indexes might be missing.- It can return different costs for the same missing
index group that appears multiple times in XML Showplans.
Remember that it is not the holy grail but yet again a nice addition that guides you in the right direction. Performance remains the responsibility of the DBA that has to make the right decision for the specific workload and configuration available. Proper modeling and indexing remains a key factor in high performance applications that squeeze out the last drop of hardware-power.
