Identify Missing Indexes

Running the statement below will bring up a list of indexes which the SQL Server query optimizer believes would have produced a better query plan.  The list is ordered based on total cumulative benefit (for example a frequently run query with and index that would have improved performance by 25% would show up before an index for a one time query that would have improved performance by 50%).

-- get the missing indexes that would be beneficial for speeding up above queries

SELECT

      (avg_total_user_cost * (avg_user_impact)) * (user_seeks + user_scans) as total_cost_savings,

      [statement] AS full_object_name,

      d.equality_columns,

      d.inequality_columns,

      unique_compiles,

      avg_user_impact,

      avg_total_user_cost,

      user_scans,

      user_seeks

FROM  

      sys.dm_db_missing_index_groups G

      inner JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle

      inner JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle

order by (avg_total_user_cost * (avg_user_impact)) * (user_seeks + user_scans) desc