日常应用中,如需查询缺少的索引信息,可使用系统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
建议: 系统建议索引准确度有限, 索引并非越多越好, 建议需人工排查确认是否真有必要添加此索引.