索引调优 第四篇：缺失索引

查询优化器（Query Optimizer）在执行查询时，如果检测到执行计划缺失索引，会把缺失索引的相关信息存储在缓存中，

一，索引组

缺失的索引都被分组，这意味着每一个缺失索引都被分配到一个特定的分组中，系统根据缺失索引的索引键把缺失索引分配到一个组中。在SQL Server中，一个索引组实际上只包含一个索引。

在实际的数据库系统中，缺失索引可能很多，但是，并不是所有的缺失索引都对查询性能的提升有同等重要的作用，这可以通过系统视图 sys.dm_db_missing_index_group_stats 来度量，通过该索引组来评估创建缺失索引的收益：

• user_scans 和 user_seeks 是指：如果分组中的索引被创建，用户的查询会引用索引做seek或scan操作的次数。
• avg_total_user_cost 是指：如果分组中的索引被创建，那么用户的查询能够减少的平均开销。
• avg_user_impact 是指：如果分组中的索引被创建，那么用户的查询能够获得的平均收益。

通过表达式 avg_total_user_cost * avg_user_impact * (user_scans+user_seeks) 计算索引的收益，根据收益来排序，获得评估的提升最高的前11个索引：

select top 111
    g.index_handle
    ,gs.unique_compiles
    ,gs.user_scans
    ,gs.user_seeks
    ,gs.avg_total_user_cost
    ,gs.avg_user_impact
    ,gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) benefit_weight
from sys.dm_db_missing_index_groups g
inner join sys.dm_db_missing_index_group_stats gs
    on g.index_group_handle=gs.group_handle
order by benefit_weight desc

在实际的数据库系统中，数据库管理员需要监控分组的统计数据，根据开销和收益来创建缺失的索引，以最大程序的提高系统查询性能。

二，缺失索引的信息

通过  sys.dm_db_missing_index_details 可以检测查询优化器推荐创建的缺失索引，该视图返回的缺失索引的索引键及包含列信息，在索引列的顺序上，通常来说，相等列（equality）应该排在不等列（inequality）之前，用户需要根据查询的条件来调整相等列和不等列的顺序，包含列（Included）应该添加到INCLUDE子句中，但是，该视图不会标识出相等列（equality）的排列顺序，需要根据查询语句和选择性来设置，索引键的第一列至关重要。而不等列（inequality）是指除等号（=）之外的比较符号，例如，table.cloumn>value。

select mid.index_handle
    ,db_name(mid.database_id) as db_name
    ,mid.object_id
    ,object_name(mid.object_id,mid.database_id) as object_name
    ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,mid.statement as underlying_table
    ,mic.column_id
    ,mic.column_name
    ,mic.column_usage    
from sys.dm_db_missing_index_details as mid
cross apply sys.dm_db_missing_index_columns(mid.index_handle) as mic
order by mid.object_id
    ,mid.index_handle

statement字段是缺失索引的表的名称，object_id字段是缺失索引的表的id，index_handle用于标识缺失的索引。

把缺失索引的收益和索引索引关联起来，得到以下的脚本：

select top 11
    g.index_handle
    ,gs.unique_compiles
    ,gs.user_scans
    ,gs.user_seeks
    ,gs.avg_total_user_cost
    ,gs.avg_user_impact
    ,gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) benefit_weight
    ,mid.index_handle
    ,db_name(mid.database_id) as db_name
    ,mid.object_id
    , schema_name(o.schema_id) + '.'+ object_name(mid.object_id,mid.database_id) as object_name
    ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,mid.statement as underlying_table
    ,mic.column_id
    ,mic.column_name
    ,mic.column_usage  
from sys.dm_db_missing_index_groups g
inner join sys.dm_db_missing_index_group_stats gs
    on g.index_group_handle=gs.group_handle
inner join sys.dm_db_missing_index_details  as mid
    on g.index_handle=mid.index_handle
inner join sys.objects o 
    on mid.object_id=o.object_id
cross apply sys.dm_db_missing_index_columns(mid.index_handle) as mic
order by benefit_weight desc
go

 

 

参考文档：