第十章——维护索引（5）——查找丢失索引

前言：

在开发阶段，很难总是可以在合适的列上创建合适的索引。所以一开始创建的索引可能会无效，此时，需要找出这些无效的索引。

一般来说，当一个查询执行的时候，SQLServer优化器会选择最适合的索引进行执行，当没有找到合适的索引话，优化器会产生一个次优执行计划，并且把丢失索引的信息存放到DMVs上。

当SQLServer服务重启后，所以存储在DMVs上的信息都会丢失，所以最好在正常使用大概1周后收集这些信息，会比较有效。

 

准备工作：

关于索引相关的DMO，在第七章中已经说明，而本文介绍的DMO有：

1、 sys.dm_db_missing_index_details：返回关于丢失索引的详细信息。

2、 sys.dm_db_missing_index_group_stats：返回特定索引的汇总信息。

3、 sys.dm_db_missing_index_groups：返回关于哪个丢失索引包含在哪个丢失索引组的句柄信息。

4、 sys.dm_db_missing_index_columns(index_Handle)：返回在丢失索引中列的信息。

 

步骤：

1、 执行一下语句返回丢失索引的信息：

SELECT  avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS PossibleImprovement ,
        last_user_seek ,
        last_user_scan ,
        [statement] AS [Object] ,
        'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'
        + CONVERT(VARCHAR(32), D.index_handle) + '_'
        + REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')
        + ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')
        + CASE WHEN equality_columns IS NOT NULL
                    AND inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
                                                              + included_columns
                                                              + ')', '') AS Create_Index_Syntax
FROM    sys.dm_db_missing_index_groups AS G
        INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle
        INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle
ORDER BY PossibleImprovement DESC

 

分析：

在这里，查找可能的性能提高是很有必要的，这个逻辑：

avg_total_user_cost*avg_user_impact*(user_seeks+user_scans)

这个信息存放在sys.dm_db_missing_index_group_stats这个DMV上，在找到预估提高的程度之后，通过两个DMV：

sys.dm_db_missing_index_group_stats和sys.dm_db_missing_index_details组合出需要创建的索引语法。但是要记住，过多的索引对查询有效但是会对更新有影响。

这些DMV只能存放最多500个索引，且重启之后会丢失里面的数据。