学步园

set nocount on
go

select DB_NAME() as db_name
    ,d.name as object_name
    ,e.rowcnt
    ,str(c.avg_total_user_cost*(c.user_scans+c.user_seeks),16,0) as total_user_cost  --总成本
    ,str(c.avg_total_user_cost,16,0) as avg_total_user_cost --平均成本
    ,c.avg_user_impact --平均收益（%）
    --,c.user_scans --受益 的 scan 次数
    ,c.user_seeks --受益 的 seek 次数
    ,c.unique_compiles as compiles --涉及到该索引的 compile 次数
    ,a.equality_columns,a.inequality_columns
    --,a.included_columns
    ,case when a.included_columns is not null then 'yes' else '' end as [has_include]
    ,d.name as object_name2
    ,'CREATE INDEX [IX_' + d.name + '_' 
        + replace(replace(replace(replace(isnull(a.equality_columns,a.inequality_columns),'[',''),']',''),',','_'),char(32),'')
        + ']'
        + ' ON ' + a.statement 
        + ' ('+ case when a.equality_columns is not null then a.equality_columns else a.inequality_columns end + ')'
        + case when a.included_columns is not null then ' INCLUDE (' + a.included_columns + ')'  else '' end 
        as create_index_stmt
    --,str(c.avg_total_system_cost,16,0) as avg_total_system_cost,c.avg_system_impact
    --,c.system_scans,c.system_seeks
    --,c.last_user_scan,c.last_system_seek
    --,c.last_system_scan,c.last_system_seek
from sys.dm_db_missing_index_details a
    inner join sys.dm_db_missing_index_groups b on b.index_handle=a.index_handle
    inner join sys.dm_db_missing_index_group_stats c on c.group_handle=b.index_group_handle
    inner join sys.objects d on d.object_id=a.object_id
    inner join sysindexes e on e.indid in (0,1) and e.id=a.object_id
where database_id=DB_ID()
    --and d.name in ('WFPUSER_A0120_READY','','')
order by total_user_cost desc ,c.avg_total_user_cost desc,c.user_seeks desc,c.user_scans desc
--order by c.avg_total_user_cost desc
--order by d.name,c.avg_user_impact desc
--order by c.avg_user_impact desc