Blog ala Vidar

In SQL Server 2005, Microsoft introduced Dynamic Management Views and Functions (DMV/DMFs). These provides real-time data about indexes, hardware, query usage and many many other things. Using this, you can actually get suggested indexes from the SQL Server itself, that in it’s mind it would gain performance if you implemented them. So, why isn’t these generated automatically? The simple answer is disk-usage. If you implemented all these, you’d end up with A LOT of indexes, and therefore a much bigger database. So, which should you implement?

SELECT
  DENSE_RANK() OVER (ORDER BY MIGS.avg_user_impact DESC, MIG.index_handle) AS IndexRank,
  RIGHT(MID.statement, CHARINDEX('[', REVERSE(MID.statement))) AS table_name,
  MIC.column_id, MIC.column_name, MIC.column_usage,
  MIGS.unique_compiles, MIGS.user_seeks,
  MIGS.avg_total_user_cost, MIGS.avg_user_impact
    FROM sys.dm_db_missing_index_details AS MID
    CROSS APPLY sys.dm_db_missing_index_columns (MID.index_handle) AS MIC
    INNER JOIN sys.dm_db_missing_index_groups AS MIG
        ON MIG.index_handle = MID.index_handle
    INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS
        ON MIG.index_group_handle = MIGS.group_handle
    WHERE statement LIKE '%atbl%'
      AND MIGS.unique_compiles > 10
    ORDER BY
      MIGS.avg_user_impact DESC,
      MIG.index_handle,
      CASE
        WHEN column_usage = 'EQUALITY' THEN 1
        WHEN column_usage = 'INEQUALITY' THEN 2
        ELSE 3
      END,
      column_id

Using this query, you get indexes, with it’s columns, ordered by the average user impact. You should consider those that have a relatively high avg_user_impact and/or avg_total_user_cost.