#Optimization TIP: Finding the Missing Indexes in your Sql Database.

Following Query will help you find the missing indexes in your database along with Total Costing of that Index. 

PS: Adding new Indexes is a tricky job. It may vary from scenario to scenario. You can add Indexes having highest Total costing and having least or none included columns. Complex Composite Indexes should be avoided.

SELECT *
FROM (
 SELECT OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [TableName]
  ,ISNULL(dm_mid.equality_columns, '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(dm_mid.inequality_columns, '') [Column]
  ,dm_mid.included_columns
  ,ROUND(dm_migs.avg_total_user_cost * dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans), 0) AS [Total Cost]
  ,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN '_'
   ELSE ''
   END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
 FROM sys.dm_db_missing_index_groups dm_mig
 INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
 WHERE dm_mid.database_ID = DB_ID()
 ) x
ORDER BY x.[Total Cost] DESC
GO