Find missing indices using dynamic management views in SQL Server

For SQL Server performance tuning, besides rebuild index and update statistics, often we also need to identify missing indexes. This can be done from SQL Tuning Advisor, but I personally feel it's quicker to directly query the dynamic management views. Here is a sample query I normally use to identify and generate index creation scripts from DM views.

Please feel free to comment, if there are better ways.

SELECT 
     migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 
     CAST('
             CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '' + CONVERT (varchar, mid.index_handle)              + '' + LEFT (PARSENAME(mid.statement, 1), 32) + ']' 
             + ' ON ' + mid.statement 
             + ' (' + ISNULL (mid.equality_columns,'')  
             + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
             + ISNULL (mid.inequality_columns, '') 
             + ')' 
             + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') 
          AS VARCHAR(MAX)) AS create_index_statement, 
     DB_Name(mid.database_id) DatabaseName, 
     Object_Name(mid.object_id,mid.database_id) TableName, 
     migs.group_handle, 
     migs.unique_compiles, 
     migs.user_seeks, 
     migs.user_scans, 
     migs.last_user_seek, 
     migs.last_user_scan, 
     migs.avg_total_user_cost, 
     migs.avg_user_impact, 
     migs.system_seeks, 
     migs.system_scans, 
     migs.last_system_seek, 
     migs.last_system_scan, 
     migs.avg_total_system_cost, 
     migs.avg_system_impact
  FROM sys.dm_db_missing_index_groups mig 
  INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle 
  INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle 
  WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 
  AND db_name(mid.database_id) in ('SECURITY', 'PERFORM', 'HOLDING','RULES')
  AND avg_user_impact > 50 -- Estimated performance impact 
  AND last_user_seek > DATEADD(hour,-48,GETDATE())
  ORDER BY 
        migs.avg_total_user_cost DESC, migs.avg_user_impact DESC

Written by:

Yeek