SELECT
migs.avg_total_user_cost*(migs.avg_user_impact/100.0)*(migs.user_seeks+migs.user_scans)ASimprovement_measure,
'CREATEINDEX[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,'')
+CASEWHENmid.equality_columnsISNOTNULLANDmid.inequality_columnsISNOTNULLTHEN','ELSE''END
+ISNULL(mid.inequality_columns,'')
+')'
+ISNULL('INCLUDE('+mid.included_columns+')','')AScreate_index_statement,
migs.*,mid.database_id,mid.[object_id]
FROMsys.dm_db_missing_index_groupsmig
INNERJOINsys.dm_db_missing_index_group_statsmigsONmigs.group_handle=mig.index_group_handle
INNERJOINsys.dm_db_missing_index_detailsmidONmig.index_handle=mid.index_handle
WHEREmigs.avg_total_user_cost*(migs.avg_user_impact/100.0)*(migs.user_seeks+migs.user_scans)>10
ORDERBYmigs.avg_total_user_cost*migs.avg_user_impact*(migs.user_seeks+migs.user_scans)DESC
