Sqlserver 2005+：数据库【推荐】的【丢失索引】信息

最新推荐文章于 2020-01-20 19:07:25 发布

weixin_34356310

最新推荐文章于 2020-01-20 19:07:25 发布

阅读量69

点赞数

文章标签：数据库

本文提供了一段SQL查询脚本，用于从数据库中获取缺失索引的详细信息，包括数据库名称、对象名称、总成本、平均成本等关键指标，并按总成本降序排列。此脚本有助于数据库管理员更好地理解索引使用情况并进行相应的优化。

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