SQL Server 索引维护（1）——如何获取索引使用情况

前言：

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

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

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

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

 

起因：

我们为什么要维护索引？大家都知道——因为性能有问题了。为什么性能有问题呢？索引不合理了呗！绝大部分系统和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('servername')  <>   @@servername 
    begin 
   declare   @server   sysname 
   set   @server   =  @@servername  
   exec   sp_dropserver   @server  =   @server 
   set   @server   =  cast(serverproperty('servername')  as   sysname) 
   exec   sp_addserver   @server  =   @server   ,  @local   =   'LOCAL' 
end
 
/***************************************
说明：其实就是删除旧的服务器名servername，再添加新的服务器名
sp_dropserver '旧的服务器名'
sp_addserver '新的服务器名' , 'LOCAL'
3、重启SQL SERVER
4、再运行以下脚本验证一下。
***************************************/
 
use master
go
select @@servername
select serverproperty('servername')

 

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

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

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

 

需要收集的信息：

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

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

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

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

 

实操：

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

既然xEvents不可用，那么还是来点传统方式吧——计划缓存（Plan cache）和DMO（DMVs 和DMFs，动态管理对