SELECT
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
SELECT c.name AS databasename, c.equality_columns, c.inequality_columns, c.included_columns, c.statement AS tablename, c.avg_total_user_cost AS ReducingTheAverageCost, c.avg_user_impact AS PercentageOfRevenue, c.last_user_seek AS TheLastTimeTheEffectAfterUse, c.unique_compiles FROM (SELECT a.name, b.* FROM (SELECT d.*, s.avg_total_user_cost, s.avg_user_impact, s.last_user_seek, s.unique_compiles FROM sys.dm_db_missing_index_group_stats s, sys.dm_db_missing_index_groups g, sys.dm_db_missing_index_details d WHERE s.group_handle = g.index_group_handle AND d.index_handle = g.index_handle AND s.avg_user_impact > 90 --AND s.unique_compiles > 10 --order by s.avg_user_impact desc ) b, sys.databases a WHERE a.database_id = b.database_id) c WHERE c.name = 'xxxxx' ORDER BY PercentageOfRevenue DESC, unique_compiles DESC当然，按照上面的规则建立索引，不过还是要建立尽量少的索引，因为索引建多了，会导致insert、update等操作的开销增大，降低性能。
还有，在建立了索引后，可以通过如下的查询，来查询索引的使用的情况：
select *
from sys.dm_db_index_usage_stats如果，对index_seek的次数，很小，那么可以考虑删除这个索引，再尝试建立其他的索引，如此多次，就能建立真正让查询使用的索引，让这些索引都能发挥作用，同时尽量减少索引的数量
查看未被使用过的索引
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id = <dbid> )
order by object_name(object_id) asc
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。