SQLServer索引维护（1）如何获取索引使用情况_MySQL

前言：

在前面一文中，已经提到了三类常见的索引问题，那么问题来了，当系统出现这些问题时，该如何应对？ 简单而言，需要分析现有系统的行为，然后针对性地对索引进行处理：

对于索引不足的情况：检查缺少索引的情况，也需要检查现有索引定义是否有问题。对于索引过多的情况：分析每一个索引的使用情况，判断是否有存在的必要或者可合并、可修改的可能。对于索引不合理的情况：也要分析每个索引的定义，及其使用情况，确定索引是否有存在必要，如果有，是否能很好地支持查询并且对现有系统的影响也不大。

从上面描述可知，我们的步骤是：

 

 

注：这个步骤并不是必须的，也不是固定的，视实际情况而定才是最佳方案。下面来介绍整个流程。

起因：

我们为什么要维护索引？大家都知道——因为性能有问题了。为什么性能有问题呢？索引不合理了呗！绝大部分系统和IT从业人员都很难在一开始就做好性能规划。特别在国内这种赶项目进度，上线了再说的国情下，即使你知道这个功能有性能问题，但是修改会带来严重的项目延期的前提下，所有人都不会允许你做改动的。所以大部分性能问题都在系统运行到一定程度或者数据量突发增长或持续增长时才出现。甚至很多领导层认为：系统能用是最重要的，性能问题可以推一下。在这一些背景下，对开发、设计人员过多地指责他们没有做好前期工作是没必要的，大家将心比心，多点理解，对后面优化工作也有帮助，毕竟别人不会那么抵触。

那么在系统运行一段时间后出现性能问题或者运维压力时，你就要介入进行性能优化。性能优化的第一步并不是盲足乱搞，而是找瓶颈，找到瓶颈才能做相应的处理，否则只能听天由命，误打误撞的几率其实很小。下面我们假定系统的性能问题已经是索引引起的，那么我们就从定位瓶颈着手。

收集系统行为：

我们知道，除非硬件BUG，否则一个静止的系统不会出现性能问题。所以系统的性能问题本质上是因为系统的行为导致的。因此，我们首先需要收集系统行为来定位瓶颈。

系统行为各式各样，又彼此关联，我们很难轻易地定位所有问题。但是Windows、SQL Server作为成熟的软件，在使用了十几年之后，业界已经有了一套比较成熟和现成的方案，所以我们不妨根据这些方案来收集。大概流程如下：

 

 

由于本文不是专门讨论如何侦测和处理系统性能问题的文章，所以非数据库部分只简略介绍。

 

首先，我们要做的是对基础的检测：服务器及操作系统的检查。服务器和操作系统是软件系统的支撑部分，并且一个软件系统的实际运行离不开对它们的准确、高效运作。上图中列出了“服务器型号”的检查，因为论坛上曾经有这么个帖子，一个新服务器安装SQL Server之后，服务一启动内存马上占满，期间没有任何操作。最后发现IBM x3650这款型号的服务器对SQL Server存在问题，换了其他型号之后就消失了。另外，对服务器特别是硬件的检查也是必要的，刚接手系统时，老是说卡，用性能计数器检测之后发现服务器的个别盘IO问题很严重，检查数据库文件存放路径之后发现，虽然服务器上有SSD盘，但是数据库依旧运行在服务器自带的SAS盘上，后来把用户库、TempDB移到SSD之后，虽然没有突飞猛涨的性能提升，但是再次检查可以得知大部分盘的IO使用情况已经趋向正常。我们知道，服务器对数据库性能影响最大的不是内存大小，而是IO。由于数据库不直接操作磁盘，而是把数据从磁盘加载到内存，所以磁盘的IO应该越快越好。简单来说，在操作系统和硬件配置合理的前提下，数据库文件应该按照各自行为存放在尽可能快的硬盘中。由于本文的主题在索引上，所以这里不多说。

对于操作系统配置，有几个点需要注意：

 

盘符划分、RAID配置、命名规则等问题。需要提供一个本地管理员组的帐号用于SQL Server服务的启动帐号，否则使用不到SQL 2005开始引入的“即时文件初始化”功能，该功能的具体描述可见：https://msdn.microsoft.com/zh-cn/library/ms175935(SQL.105).aspx网络配置、机器命名：本人维护的系统中，供应商在安装好OS后马上安装SQL Server，送货到机房之后，运维人员根据内部需要重命名机器名，导致SQL Server某些功能无法识别administrator，比如复制功能。如有可能，建议先完成操作系统的配置再安装SQL Server，若无法实现，可以用以下脚本，修改SQL Server的配置，但本脚本不能完全处理这类问题：

 

 

--检查是否一致
use master
go
select @@servername
select serverproperty('servername')
 
--如果不一致，执行下面的语句
if  serverproperty(&#39;servername&#39;)  <>   @@servername 
    begin 
   declare   @server   sysname 
   set   @server   =  @@servername  
   exec   sp_dropserver   @server  =   @server 
   set   @server   =  cast(serverproperty(&#39;servername&#39;)  as   sysname) 
   exec   sp_addserver   @server  =   @server   ,  @local   =   &#39;LOCAL&#39; 
end
 
/***************************************
说明：其实就是删除旧的服务器名servername，再添加新的服务器名
sp_dropserver &#39;旧的服务器名&#39;
sp_addserver &#39;新的服务器名&#39; , &#39;LOCAL&#39;
3、重启SQL SERVER
4、再运行以下脚本验证一下。
***************************************/
 
use master
go
select @@servername
select serverproperty(&#39;servername&#39;)

下面进入重点部分，也就是对数据库系统的侦测。收集系统行为信息还有一个重要的原因就是了解系统读写行为，读多还是写多。读写比例直接影响表设计、数据类型特别是定长和变长的选择，也影响索引填充因子的配置等。

但是本文集中在索引行为上，所以不打算花费太大篇幅在上面，后续再整理专题。从大范围来说，服务器行为可以通过分析应用程序的结构、性能计数器、服务器端SQL Trace、存储过程、函数、视图读写次数及索引的使用情况来综合分析，但是无论哪一种方式，要做充分的分析都是耗时、工作量大的体力和脑力活。

可是我们没有必要总是全部收集，一个一个分析。我们可以使用“大胆假设，小心求证”的方式去应对。下面来点干货：

需要收集的信息：

在实操之前，需要先了解我们的操作对象——本系列中的索引。简单而言，就是要对表上的索引进行信息收集，索引的信息很多，比如有多少数据页、叶子节点包含了什么数据、索引层级、锁升级等等，但是大部分对处理常规问题而言并不必要，所以我们可以重点针对索引的某些明显指标进行收集：

索引的读、写次数。索引定义索引被使用的具体情况（本文的重点）索引碎片缺少索引（missingindex）的相关信息

需要注意的是，你要收集的系统应该运行了足够长的时间，比如数周甚至数月，除了让缓存能充分表现系统行为之外，也可以加大覆盖系统行为的可能性，因为某些功能确实只在特定时间（如月结及其报表）才会发生，或者在异常时才会触发，如果系统运行了几个小时就开始收集信息，那么信息的准确度可能不足以支撑系统分析。

网上有类似的文章，但是我觉得个人的方法也不错，所以这里我不打算根据网上的方法来介绍，而是介绍本人自己的方法，如有不妥或者漏洞，欢迎指出和分享你们的方法。

实操：

对于索引问题，我要思考的是：现在的索引是否合理？如果合理，那么性能问题可能是别的地方，当然，写这篇文章证明是不合理的，那么如何发现和定义呢？需要监控和分析。由于本人负责的系统是SQL 2008 R2，虽然已经支持扩展事件（Extent Events，xEvents），但是由于从SQL 2012开始才有图形化界面，而且2008听说还存在一定的bug，所以在这里并没有使用，个人还是挺看好这个功能，后续我会尝试使用，也欢迎大家分享。

既然xEvents不可用，那么还是来点传统方式吧——计划缓存（Plan cache）和DMO（DMVs 和DMFs，动态管理对象）。需要注意的是计划缓存存储的是预估执行计划，有些程序的实际行为是不同的。所以预估执行计划只能作为入门。

在确定工具之后，接下来就要思考如何使用。前面提到的指标中，除了“索引被使用的具体情况”之外，其他都能用各种DMO获取。但是基于连贯性原因，我边描述操作边简要介绍各种DMO。

通常来说，一个系统有大量的对象（存储过程、动态SQL、函数、视图等），除非问题非常特殊，一眼就能定位，否则我会按照下面原则来检查：

1、 从SSMS中的报表获取LongRunning 。

2、 用语句获取LongRunning对象。（1、2两个我将单独起文介绍）

3、 通过与开发人员的沟通获取可能的性能瓶颈。

4、 对大表和索引很多的表进行优先分析。

5、 当然还有其他，不过这些多多少少跟运气有关，说不定误打正着碰对了瓶颈。

在本次Troubleshooting中，我按上面顺序进行操作，最后发现第四个原则的效果明显，所以我重点讨论第四个原则。

查找索引定义：

在这次维护索引中，我选择了对大表进行优先分析，当然对于很多系统来说，这些表一点都不大，不过别在意细节。首先我从最大的表开始，逐个分析每个表的索引。找表的行数太容易了，这里就不说了。当我找到最大表时，我们可以很轻易地从SSMS中找到表上有多少索引，然后呢？

在SQL 2000时代，很多sp_xxxx系统存储过程都能获取一定的信息，比如sp_helpindex 表名这种方式可以获取表上索引的定义，但是这个系统存储过程并不支持SQL 2005及后续版本出现的新功能，如包含索引的描述，所以你只能看到索引名、定义在INCLUDE关键字前的那些列（假设它们是包含索引），对于包含索引中的包含列，却没有显示。这种方式有一个风险，很多人通过这个存储过程看到某些索引的前面几列完全相同，就直接删除其中重复索引，其实这些索引是包含索引，对某些程序的支持有作用，这种鲁莽行为可能导致系统性能突然猛降，所以要“大胆假设、小心求证！！！”。

我们可以使用DMO来实现这种需要，注意替换表名：

DECLARE @tblnvarchar(265)
SELECT @tbl = '表名'
 
SELECT o.name,i.index_id, i.name, i.type_desc,
       substring(ikey.cols, 3, len(ikey.cols))AS key_cols,
       substring(inc.cols, 3, len(inc.cols)) ASincluded_cols,
       stats_date(o.object_id, i.index_id) ASstats_date,
       i.filter_definition
FROM   sys.objects o
JOIN   sys.indexes i ON i.object_id = o.object_id
CROSS  APPLY (SELECT ', ' + c.name +
                     CASE ic.is_descending_key
                          WHEN 1 THEN ' DESC'
                          ELSE ''
                     END
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.object_id = c.object_id
                                  ANDic.column_id = c.column_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id = i.index_id
                AND  ic.is_included_column = 0
              ORDER BY ic.key_ordinal
              FOR XML PATH('')) AS ikey(cols)
OUTER  APPLY (SELECT ', ' + c.name
              FROM   sys.index_columns ic
              JOIN   sys.columns c ON ic.object_id = c.object_id
                                  ANDic.column_id = c.column_id
              WHERE  ic.object_id = i.object_id
                AND  ic.index_id = i.index_id
                AND  ic.is_included_column = 1
              ORDER  BY ic.index_column_id
              FOR XML PATH('')) AS inc(cols)
WHERE  o.name = @tbl
  AND i.type IN (1, 2)
ORDER  BY o.name, i.index_id

结果如下：

可以比较直观地看到索引定义及其统计信息更新时间（这个极其重要，但是不是本文的重点，所以也不详细描述）。获取索引定义是为了分析设计是否合理、是否可修改，如果不知道你要操作的对象是什么样子的，也就不可能有下面的步骤。

每个表上索引的使用情况：

当你知道有多少个索引，索引是怎样的时候，就可以开始收集索引使用情况，这里分两步，但是可以同时进行：

1．获取索引的读写情况：

2．获取索引的被使用信息，这里的被使用是指：从服务器启动开始（这个很重要，因为你读的是缓存），这个索引在系统中被什么对象（动态SQL、存储过程、函数等，包含了对象的文本信息）使用过，使用了多少次，对应的计划缓存是怎样的。

对于第一步，我们可以用简单的DMV来得到：

SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
        i.name AS [Index Name] ,
        i.index_id ,
        user_updatesAS [Total Writes],
        user_seeks+ user_scans + user_lookups AS [Total Reads] ,
        user_updates-( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM    sys.dm_db_index_usage_stats ASddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]
                                                      AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
        AND OBJECT_NAME(ddius.[object_id])='表名'
        AND i.index_id > 1  --非聚集索引
ORDER BY [Difference] DESC ,
        [Total Writes]DESC ,
        [Total Reads]ASC;

我们这里主要关注非聚集索引，因为绝大部分情况下，聚集索引是主键，在系统运行了一段时间后，你能修改主键的可能已经大大降低，并且主键一般问题不大。下面是语句执行的大概样子：

得到了这些信息之后，就开始做初步分析，对于大部分系统而言，读操作远大于写操作，所以如果你的系统也是类似的，那么可以选出上图中【Total Reads】远小于【Total Writes】的那些索引进行优先分析对象，如上图的第五个索引。

某个索引被使用的具体情况：

再次说明，很多方法可以实现这种分析，在不需要深入研究的情况下，够用就好。本人通过改写国外大牛的一个关于查找“并行执行语句”的脚本，实现获取某个索引自实例启动依赖被使用的具体情况。原脚本如下：

--执行计划中使用了并行操作的语句：
SET TRANSACTION ISOLATIONLEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT COALESCE(DB_NAME(p.dbid)
    , p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)'))
     AS database_name
  ,DB_NAME(p.dbid) + '.' + OBJECT_SCHEMA_NAME(p.objectid, p.dbid)
    + '.' + OBJECT_NAME(p.objectid, p.dbid) AS object_name
  ,cp.objtype
  ,p.query_plan
  ,cp.UseCounts AS use_counts
  ,cp.plan_handle
  ,CAST(&#39;<!--?query --' + CHAR(13) + q.text + CHAR(13) + '--?-->&#39; AS XML) AS sql_text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE cp.cacheobjtype = &#39;Compiled Plan&#39;
AND p.query_plan.exist(&#39;//RelOp[@Parallel = "1"]&#39;) = 1
ORDER BY COALESCE(DB_NAME(p.dbid), p.query_plan.value(&#39;(//RelOp/OutputList/ColumnReference/@
Database)[1]&#39;,&#39;nvarchar(128)&#39;)), UseCountsDESC

下面是本人改写后的脚本：

--获取某个索引被使用的情况
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT COALESCE(DB_NAME(p.dbid)
    ,p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)'))
     ASdatabase_name
  ,DB_NAME(p.dbid) + '.' + OBJECT_SCHEMA_NAME(p.objectid, p.dbid)
    +'.' + OBJECT_NAME(p.objectid, p.dbid) AS OBJECT_NAME,
    cast ('索引名' as varchar(64)) AS IndexName
  ,cp.objtype
  ,p.query_plan
  ,cp.UseCounts AS use_counts
  ,cp.plan_handle
  ,CAST(&#39;<!--?query --' + CHAR(13) + q.text + CHAR(13) + '--?-->&#39; AS XML) AS sql_text INTO xxx.xxx.xxx表
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q
WHERE cp.cacheobjtype = &#39;Compiled Plan&#39;
AND p.query_plan.exist(&#39;//Object[@Index = "[索引名]"]&#39;) = 1
ORDER BY UseCounts DESC,COALESCE(DB_NAME(p.dbid), p.query_plan.value(&#39;(//RelOp/OutputList/ColumnReference/@
Database)[1]&#39;,&#39;nvarchar(128)&#39;))

脚本有两个注意的地方：

1．需要手动替换语句中的“索引名”三个字，共两处地方。另外要注意的是，这个语句查询的是XML格式的执行计划，XML是大小写区分的，所以要严格按照索引名（在SSMS中查到的名字）来替换，否则查询不出来。另外针对索引名，本系统就出现过不同的表使用了相同的索引名（如index1），这种极其乱来的命名规则应该避免。这种情况也导致了你分析的索引可能是另外一个表的，所以需要再次检查。

2．脚本中有INTO xxx.xxx.xxx表的部分。这个是本人的监控习惯，在同一个服务器上，若有空间和条件，建议创建一个独立的数据库（简单模式即可），存储所有你感兴趣的数据库运维和性能信息，以便后续之用。这个如果不需要存储，记得注释掉。

下面是脚本结果的示例：

接上图

现在来解释一下这个结果：

Database_name：指使用这个索引的对象（如存储过程）所在的数据库，本系统存在跨库操作的行为，所以一个索引可能被多个数据库的对象使用。这个可以用于找到对象所在的数据库，如果有必要可以对这个数据库做进一步分析，如这个数据库的配置情况等。OBJECT_NAME：这个是使用索引的对象名，如果是数据库内存储的对象（如存储过程、函数、视图等），这里会有结果，如果是动态SQL，此处为NULL。IndexName：索引名字，一般仅用来后续统计之用。Objtype：使用索引的对象类型：Proc为存储过程，Prepared为预定义语句，详细类型说明可以查看联机丛书关于sys.dm_exec_cached_plans的说明中的objtype。Query_plan：使用索引的对象的执行计划，点开后是图形化执行计划。这是语句中最重要的信息之一。Use_counts：对象的执行次数。Plan_handle：在这里它的作用不大。Sql_text：这也是XML片段，它是引用到索引的那部分代码，特别是对动态SQL，我们可以一下子就找到它。

以上图为例，下面来看看如何使用这些结果。

首先，我们抓重点，看use_counts列，对于那些运行了几个月的系统，这列还是2、3次的，其实没有多大关注必要，除非你要做极限优化。所以我们的切入点是这个列的数据，先挑执行次数最多的来看。注意脚本中已经对use_counts做了排序操作，读者可以按需要修改排序。

找到需要分析的对象之后，点一下最后一列，看看语句情况：注意由于某些存储过程可能多个地方引用或者存储过程本身比较小，所以这个并不是必要步骤，不过看一下大概语句也没坏处，毕竟语句的写法直接影响性能。

如果语句看不出什么问题，再点开执行计划，有些存储过程执行计划的内容很大、很多步骤，所以直接读也不见得是高效。基于本主题，我们希望找到的是索引使用不合理的地方，所以我们还是直接定位索引使用情况。如何定位？查XML。

右键图形化存储过程，选择【显示执行计划XML(X)】，会在新窗口打开执行计划的XML文本。

第一次打开XML格式的执行计划时可能会被吓一跳，不过不要紧，我们并不是做深入研究，此时只要用普通的查找文本方法找到索引出现的地方即可。使用CTRL+F快捷键，然后把索引名贴进去就可以收缩到索引所在的地方：

如果你还是读不懂XML执行计划，那就返回图形化里面找：

把鼠标移到这个图标上即可看到一些我们所需的信息。

按照上面的方式把需要分析的索引分析一遍（分析办法下面会介绍），就可以知道这个索引是否合理，是否可以删除，是否可以合并。

提醒：有些核心表的核心索引可能被几千个对象应用，这些对象主要是动态SQL，只是不同参数而已，在研究参数嗅探时是有价值的，关于这部分另起文章讨论，文章完成后会加上链接。对于使用上面脚本查出来的结果中，若有成百上千行时，一个一个分析显然不合理，此时use_counts又起了一定的作用——找次数足够多的来研究，同时结合sql_text列，找出语句几乎一样仅参数不一样的那些，可以只挑一个研究。一个索引往往就被几个单独的对象应用。如果有大量应用，考虑是否要按业务拆分（所谓的垂直拆分表和业务）。

其他信息收集：

下面给出几个收集其他信息的脚本：

缺少索引：

--丢失索引
SELECT  user_seeks * avg_total_user_cost *( avg_user_impact *0.01 ) AS [index_advantage] ,
        dbmigs.last_user_seek ,
        dbmid.[statement] AS [Database.Schema.Table],
        dbmid.equality_columns ,
        dbmid.inequality_columns ,
        dbmid.included_columns ,
        dbmigs.unique_compiles ,
        dbmigs.user_seeks ,
        dbmigs.avg_total_user_cost ,
        dbmigs.avg_user_impact
FROM    sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
        INNER JOIN sys.dm_db_missing_index_groupsAS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_detailsAS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle
WHERE   dbmid.[database_id] = DB_ID()
ORDER BY index_advantage DESC;

索引碎片：

--索引上的碎片超过%并且索引体积较大（超过页）的索引。
SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],
                                                    DB_ID())+ '].['
        + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddips.[index_type_desc] ,
        ddips.[partition_number] ,
        ddips.[alloc_unit_type_desc],
        ddips.[index_depth] ,
        ddips.[index_level] ,
        CAST(ddips.[avg_fragmentation_in_percent]AS SMALLINT) AS [avg_frag_%] ,
        CAST(ddips.[avg_fragment_size_in_pages]AS SMALLINT) AS [avg_frag_size_in_pages] ,
        ddips.[fragment_count] ,
        ddips.[page_count]
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips
        INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
                                      AND ddips.[index_id] = i.[index_id]
WHERE   ddips.[avg_fragmentation_in_percent] > 15
        AND ddips.[page_count] > 500
ORDER BY ddips.[avg_fragmentation_in_percent] ,
        OBJECT_NAME(ddips.[object_id], DB_ID()) ,
        i.[name]

注意：检查碎片的前提是表有一定的规模，对于那些小表，即使99%的碎片也不影响什么，还是那句：挑重点。另外本脚本针对碎片率15%的索引做检索，这个比例没有绝对值，但是作为建议，10%~15%以上的碎片率就需要开始重视。

索引分析：

根据前文所述，我把索引问题主要拆分为三类：索引不合理、索引不足、索引过多。通过上面的信息收集，我们已经得到了足够的信息。

索引不合理：

首先我们检查索引定义，如下图：

从定义中，我们发现几个问题：

1．索引个数很多：加上聚集索引总共有15个！！！！

2．索引命名：这索引命名足够让人奔溃。不多说。

3．看key_cols列中红圈和黄圈部分，我们一般集中注意力在索引的首列，我们可以看到这里有四个索引是可以列入“可合并”的范畴。对于这个列表，我们需要挑出三类索引：

a)第一种是上图所示首列相同的索引（甚至好几列相同）。

b)第二种是从名字上看来可能是选择度很低的列，假设某个索引用了Gender（中国人大多用Sex）作为首列，我们知道性别通常只有两个值（最多三个：保密或…），这种列做索引的首列是很低效的，所以也应该列出来作为重点研究对象。

c)第三种有点难度，比较费精力，找出key_cols和Included_cols（包含列）中反复出现的列，这些索引可能对相同的列做索引，但是列的顺序不同而已，也需要研究是否有修改的必要。

例子演示：

如下面图中这两个索引，我们看到首列相同，并且索引列只有一列，但是第二个索引的包含列为NULL，经过下一步检查读写次数之后，基本上可以得知index_id=37的那个是否可以删除了。这种是典型的“重复索引”，可以归纳到索引过多的范畴。

下面来看看索引读写情况，为了减少篇幅，这里我们只查这两个索引的情况：

可以看到index_id=31的的索引的读的次数比37的索引接近6倍之多。如无意外index_id=37的索引是可以删除的，但是作为严谨考虑，我们需要再检查这两个索引的具体使用情况。其实经验表明，这种索引定义中，index_id=37的索引存在的唯一优点是“索引体积较小”，但是我们只要研究一下include列的数据类型就会知道会不会大很多，而且索引维护是一个权衡的过程，没有既提高读性能，又提高写性能的索引，这一点要时刻记住。

接下来就是抓索引被使用的对象情况。为了节省时间，我们可以用两个窗口，分别、同时获取两个索引的信息。下面是id=31的索引的信息：

对于database_name为null的数据，我们不必在意太多，毕竟这不是深入研究，脚本也并不是微软官方提供的，从图中可以看到这个索引被1708个对象使用过。

下图是id=37的索引的信息：

这个索引被772个对象使用了。

对比两个结果，id=37的索引前四行可以看看，后面的只使用了几次的索引不看也行。先点开第一个的执行计划看看，貌似有点多，那么看XML，并搜索索引被引用的地方：

整个执行计划里面就这个地方使用了这个索引，同时留意红框地方，UPDATE/Clustered Index Update。这是一个典型的“无用索引”，可以同时纳入“索引过多”和“索引不合理”的范畴。从这里看出，这个索引并没有实际被用到，仅仅因为UPDATE语句，触发了聚集索引更新，从而连带引发表上非聚集索引的更新。简单来说，它没有为性能带来好处，反而因为更新时多了这个索引，所以更新速度和开销更慢。并且我们回顾再上一个图，它被执行了2080825次，这种频率所带来的影响不可忽视。那么我门先标注一下：这个索引可以被删除。建议读者开一个excel，列出这些信息，并且包含删除、修改、保留等理由，也可以再加一列：删除可能性，每检查一个存储过程/SQL语句，如果可删除，那么加1，最后对比这个值是否最高，就可以判断是否优先删除。因为有些索引可能不合理，但是也不见得完全没用。这些索引是可以短时保留的。

用同样方法检查第二、第三个，情况一样，再检查第四个：

我们看到这次它被一个索引查找使用了，证明它可以协助查询，然后我们分析一下这个操作符，结合语句，发现它在查询中，WHERE条件的用到了这个索引的首列，同时输出列表中的列是主键，这里即聚集索引，而非聚集索引叶节点是包含了聚集索引键值，所以这个非聚集索引包含了WHERE和SELECT中所需的数据，所以优化器使用这个索引来协助查询。但是由于这个索引和id=31的那个几乎一样，所以我们完全可以认为这个索引是可以删除的，然优化器使用id=31的那个索引，只是查询所需的资源会略微增加而已。

使用同样的方法检查id=31的索引，以便验证我们的想法，这里就不一一截图。

通过这个方法，我们可以判断索引是否可以删除。并且如果你足够细心，可以在分析的过程中连带发现其他表的索引问题、语句是否合理等一些列的问题。当然，会很累。

上面的例子可以用于研究索引过多、索引不合理的情况。索引不合理主要是通过定义是否重复或者可合并、执行计划中是否出现了索引/聚集索引扫描或者其他需要注意的操作符（说明：每个操作符的出现有其原因和特定背景，并没有哪个操作符好，哪个不好。需要具体问题具体分析）。但是有些情况是很明显有问题的，比如：一个百万行的表，我只需要查询10条数据，并且SELECT中只用到少数几列，经过查询索引定义，某个索引包含了SELECT/WHERE/JOIN ON中的这些列。那么按道理来说，通常会进行查找操作，可以你在执行计划中发现它使用扫描操作。那么这就值得注意，这种情况通常是有问题的。常见的几种原因是：

统计信息过时：统计信息过时会导致优化器错误选择索引和索引的访问方式，可以通过上面查看索引定义语句中的stats_date列发现是否离现在很遥远。如果是，不妨更新一下统计信息。具体语句可以查看联机丛书的说明。索引定义不合理：上一篇文章已经演示过，如果首列定义错误，本来可以进行索引查找的操作会变成索引扫描。非SARG写法：如果WHERE条件中的列使用了标量函数、隐式转换等非SARG写法，也会导致“索引无效”。何为非SARG写法，这里不累赘，读者可以自行搜索。索引碎片过高：通常碎片问题会导致优化器不选择一个本来很好的索引，当索引碎片足够高时，假设表上只有这个索引可用，那么本来的查找操作会变成扫描操作。

建议：如果性能问题不是非常严重，在做完上面步骤后，可以先不删除索引，把信息记录到一个表，过一段时间后再重新操作一次，看看信息的变化情况。若确实不合理，那么把索引的定义保存起来，然后删了吧。

索引不足：

在三大索引问题中，现在已经解决了两个，剩下一个——索引不足。简单来说，这个问题主要就是找出哪些列需要建索引，为什么要建。但是建议这一步要在最后操作，因为索引过多和索引不合理的处理结果可能就是经过调整后，索引已经能满足性能要求，不需要再增加索引了。如果你把顺序反过来，那么可能在没有研究是否有多余索引之前，又加了一系列的索引，增加了研究的工作量和复杂度，从上面可以看出其实上面两步非常耗时。

这一步同样需要像上面那样收集同样的信息，所以建议用一些excel或者实体表存储过程信息。在个人经验中，查找哪些列需要加索引，有两类手段，建议同时使用：

1． SQL Server自带的缺少索引功能：从SQL 2005开始引入，但是2005的图形化执行计划并没显示缺少索引的提示。

2． 和上面步骤不一样，上面步骤研究的是非聚集索引，那么我们是否要研究一下聚集索引呢？因为没有索引的列，如果查询中使用到，那么除非是堆表，否则会访问聚集索引，所以从聚集索引的使用情况可以粗略得出哪些列需要索引化。

缺少索引：

缺少索引的文章也有很多，本人的书《SQLServer性能优化与管理的艺术》中也有描述，下面挑出一些重点来介绍。

缺少索引是有SQL Server在运行查询过程中，根据统计信息和索引情况记录在一系列DMO中的信息。这部分的DMOs是一些列的对象，由查询引擎在执行过程中收集的数据，当优化器编译一个执行计划是，会决定用什么索引及如何使用，如果索引不存在，会把这部分的信息存放到DMOs中。

这部分的DMOs和其他性能监控工具不一样，无法通过配置去管理，它是自动运作的。在使用这些信息时，有下面的注意事项：

1、队列的大小，这一点很多人都忽略了，SQLServer只会收集最多500个缺失索引组，一旦到达这个限制，就会停止收集新的缺失索引信息。缺失索引组其实是一系列的缺失索引信息，后面会提到。对于这个限制，只能通过周期性监控并尽快处理，让优化器能够报告更多的缺失索引信息。

2、分析深度，SQLServer会报告缺失索引的信息，并给出它的建议，但是要注意分析的深度，这些建议仅针对当前的执行计划，有时候根据建议添加索引后，会出现新的缺失索引信息，而且给出的建议中，可能不会考虑列的顺序，所以当查看这些信息时，需要做足够的测试。

3、准确度，当查询使用不等于这种限定词，比如在where中使用了A<>B这样的写法，缺失索引给出的信息准确度就没有使用等于这种限定词高。

4、索引类型，缺失索引对聚集索引、XML、空间或者列存储索引不可用。

除此之外，当表的元数据改变时，缺失索引的信息也会消失，比如增加新列这些操作。这部分的DMOs包含：sys.dm_db_missing_index_details、sys.dm_db_missing_index_columns、sys.dm_db_missing_index_group_stats、sys.dm_db_missing_index_groups

但是需要说明的是：别看到SQLServer提示了就加，这是导致索引过多的主要原因，要分析、要测试！

分析聚集索引：

现在换一个环境，用AdventureWorks2008R2作为演示。使用前文创建的dbo.person表，并删除除主键外的所有索引。

现在在表上没有任何非聚集索引。那么我们还是使用前文中的查询语句：

select Title,FirstName,MiddleName,LastName
from dbo.Person
where FirstName like 'o%'

 

 

为了让SQL Server能收集足够的信息，我们重复执行这个语句10次。然后用前面检查索引被使用情况的语句检查这个聚集索引：

 

 

点开第一个执行计划可以看到：

 

结合语句可知，它仅仅使用了四列，由于FirstName不是主键且上面没索引，所以走的是聚集索引扫描。如果聚集索引建在FirstName，可以看到走的是聚集索引查找，因为鼠标移到箭头上可以看到实际上返回了164行，而全表有19972行数据，这种比例是可以进行查找操作的。不过主键毕竟需要唯一非NULL，所以这里就不演示了。

通过分析，我们初步判断可以通过对这四列进行索引化，并且以FirstName为索引首列来提高性能，但是为了验证想法，我们用上面给出的缺少索引的语句来验证，也可以用DTA来检验，记住，DTA有很多限制和不足的地方，不要盲目相信：

 

很多时候缺少索引的DMOs记录的恰恰就是DTA的提示，但是这些DMOs更加完善，由于这里是演示环境，系统并没有收集足够的信息，所以DMOs没有显示。

这种分析确实比较累人，建议结合缺少索引的提示和DTA来分析。切记要分析和测试。

 

总结：

本系列文章通过一些工具，粗暴但不失有效地检测和分析系统常见的索引问题。另外，本方法确实不是什么精密的、能覆盖所有可能的方法，如果需要精密严谨地分析，需要借助很多工具、长时间收集和反复监控。但是作为实践所得，本人觉得这两篇文章还是有很重要的用处。

处理过索引相关问题之后，有这么一个深刻体会：建一个索引很容易，也很快。说不好听，可以说完全不需要负责任。但是证明一个索引可以删除、可以合并，其实你需要花费大量的精力和时间，并且毫不夸张地说，你需要勇气。像本人管理的系统中，有500多个索引，抛开接近200个主键（聚集索引），如果要每个都检查，没有个把月专门做这事情是不现实的。