In the Query plan properties of interest section of Chapter 4, Exploring Query Execution Plans, we discussed the MissingIndexes property. If this property exists in a query execution plan, it means that there is at least one index that SQL Server could have benefited from that does not exist.
The following query uses DMVs to list all the missing index suggestions on the server:
SELECT DB_NAME(d.database_id) as [database_name], OBJECT_NAME(d.object_id, d.database_id)ASobject_name, total_cost_savings = ROUND(s.avg_total_user_cost * s.avg_user_impact *(s.user_seeks + s.user_scans),0)/100, s.avg_total_user_cost, s.avg_user_impact, s.user_seeks, s.user_scans, d.equality_columns, d.inequality_columns, d.included_columns ...
