From "SQL Server DMV Starter Pack" by Louis Davidson and Tim Ford.
When this script runs, it identifies potentially missing indexes.
This script combines three closely-related DMVs to get a very useful missing index query. It uses the various statistics stored regarding data access patterns to calculate the possible 'advantage' of adding a certain index; The higher the index_advantage, the bigger the impact.
The results are only as good as the statistics, so they may be spurious if you have recently added new indexes - which resets all the statistics for that table.
Beware that this script may not recommend the best column <i>order</i>, it does not consider filtered indexes, and is very eager to suggest new indexes.
Always examine the results of these recommendations and consider their impact before applying them; is there already an index that will suffice? Is the query that caused the recommendation rarely called? (check the last_user_seek column) What impact would adding another index have on your (potentially write heavy) table?
