SQL Server查询系统建议的索引信息

 日常应用中,如需查询缺少的索引信息,可使用系统DMV视图: sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details等, 相关TSQL查询语句如下:

-- 查询缺少的索引
select top 100
           [AvgImpact]=b.avg_user_impact*(b.user_seeks+b.user_scans),
           b.avg_user_impact,
           'Total_Seek&Scan'=b.user_seeks+b.user_scans,
           [DatabaseName]=e.name,
           [TableName]=d.name,
           g.EqualityColumnList,
           g.InEqualityColumnList,
           g.IncludeColumnList,
           [TSQL]='create index [ix_'+d.name+'_'+coalesce(replace(g.EqualityColumnList,',','_'),replace(g.InEqualityColumnList,',','_'))+']'
                       +' on dbo.'+d.name+'('+g.EqualityColumnList+') '
                       +case when g.IncludeColumnList<>'' then ' include('+g.IncludeColumnList+') ' else '' end
from sys.dm_db_missing_index_groups a
inner join sys.dm_db_missing_index_group_stats b on a.index_group_handle=b.group_handle
inner join sys.dm_db_missing_index_details c on a.index_handle=c.index_handle
cross apply (select EqualityColumnList=isnull(stuff((select ','+f.column_name
                                                                                     from sys.dm_db_missing_index_columns(a.index_handle) f
                                                                                     where f.column_usage in('EQUALITY')
                                                                                     for xml path('')),1,1,''),''),
                                InEqualityColumnList=isnull(stuff((select ','+f.column_name
                                                                                        from sys.dm_db_missing_index_columns(a.index_handle) f
                                                                                        where f.column_usage in('INEQUALITY')
                                                                                        for xml path('')),1,1,''),''),
                                IncludeColumnList=isnull(stuff((select ','+f.column_name
                                                                                    from sys.dm_db_missing_index_columns(a.index_handle) f
                                                                                    where f.column_usage in('INCLUDE')
                                                                                    for xml path('')),1,1,''),'')) g
inner join sys.tables d on c.object_id=d.object_id
inner join sys.databases e on c.database_id=e.database_id
where c.database_ID=db_id()
order by [AvgImpact] desc

 

建议: 系统建议索引准确度有限, 索引并非越多越好, 建议需人工排查确认是否真有必要添加此索引.