sqlserver2008 查看数据库自带的索引建议

时间:2023-03-08 16:51:37
sqlserver2008 查看数据库自带的索引建议
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