专家出诊：SQL Server 高CPU系列之索引诊断

正文

引发CPU过高的最常见的两类索引问题是索引缺失和索引碎片。首先我们来分析索引缺失。

一、索引缺失

场景分析

关系型数据库（RDBMS）系统中，索引缺失最为常见会导致I/O读取很高，进而导致CPU使用率很高。这是因为当查询优化器在执行计划评估过程中，发现没有合适的索引可以使用时，不得不选择走全表扫描（Table Scan）或者近似于全表扫描的操作（Clustered Index Scan）来获取所需要的数据。这种大面积的数据扫面会导致I/O子系统读取操作频繁，SQL Server需要读取大量的数据并加载到内存中，这些操作最后都会使得CPU使用率飙高。这种场景中，解决CPU高使用率的问题，其实就变成了解决索引缺失的问题。我们可以从下面的例子中来看看如何发现和解决索引缺失的问题。

解决方法

在这里，我们将这个例子详细分解为五个小步骤：

? 测试环境：搭建简单的测试环境。

? 执行查询：创建缺失索引前后用于做性能对比的查询语句

? 缺失索引：查找缺失索引的方法

? 解决问题：创建缺失的索引

? 效率对比：创建缺失索引前后的性能对比

测试环境

创建测试环境包括：创建测试数据库、测试表对象和初始化200万条记录。

Create testing database

create demo table SalesOrder

data init for 2 M records.

初始化了200万条数据，如下：

执行查询

查询用户10057在近一个月内的商品购买情况（为了获取性能对比信息，我打开了Time和I/O统计），建议在执行语句之前打开实际执行计划获取选项。打开实际执行计划，方法是点击SSMS中的下图方框中图标，或者使用快捷键CTRL + M：

执行查询语句：

执行查询语句的I/O，CPU和时间消耗，其中，逻辑I/O读取消耗32295，CPU消耗451 ms，执行时间消耗648 ms，如下图展示：

执行计划走Clustered Index Scan（性能消耗几乎于Table Scan相近），索引缺失警告信息，如下图绿色字体，右键点击，然后选择Missing Indexes Details...可以打开缺失索引的详细信息：

除了上面事例讲解的执行计划查看缺失索引的方法以外，我们还可以使用三个重要的系统动态视图来查看缺失索引（每个视图具体含义，请自行查看帮助文档）：

? sys.dm_db_missing_index_group_stats

? sys.dm_db_missing_index_groups

? sys.dm_db_missing_index_details

利用三个系统动态视图来查找缺失的索引，方法如下：

执行后的查询结果如下图所示：

解决问题

无论是通过执行计划查看索引缺失，还是通过三个动态视图获取缺失索引，最终的目的就是解决问题，让我们创建这个缺失的索引：

效率对比

创建了这个缺失索引以后，再次上面执行上面“执行查询”中的查询语句，执行计划和性能消耗对比。

执行计划，已经走到了更加高效的Index Seek上来了，如下图所示：

I/O读逻辑取消耗为126、CPU消耗为16 ms和执行时间消耗为198 ms，截图如下：

创建索引后，执行时间消耗，CPU消耗，I/O读取消耗，分别提高了3.27倍，28.19倍和256.3倍，平均性能提高了95.92倍。对比情况做图如下：

从最终的测试结果来看，创建索引后，对于特定查询性能在CPU使用率、时间消耗和I/O读取三个方面都有很大提升，尤其是I/O读取操作提高了256.3倍，平均的性能提升达到了95.92倍，效果十分明显。

二、索引碎片

解决索引碎片问题是解决SQL Server服务响应缓慢，查询超时的又一利器

索引碎片是什么

索引碎片既指索引文件页中的空白空间；又指被Page Split的索引页；还指索引失序的数据页。前面两种我们称之为索引内部碎片，后面一种我们叫着索引外部碎片。

前面是干瘪瘪的理论描述，下面举一个关于电话簿实例就比较好理解了：假如电话簿按照城市名称、姓名和电话号码组合排序的方式来存储所有人员的联系电话。

以下几种情况都需要更新电话簿：

? 当一个人更换电话号码的时候或者改名字的时候（西方国家女子结婚后，会修改姓氏为老公的姓，比如：奥巴马的老婆叫米歇尔-奥巴马）：需要更新操作

? 当一个人从一个城市搬迁到另一个城市的时候：需要删除原来的记录，在相应的城市插入新记录

? 当有新人办理了电话业务的时候：需要在相应城市插入记录

? 当作废电话号码的时候：需要删除对应条目

这些操作带来的后果是：更新操作可能导致失序（out of order）；删除操作导致空白条目（empty space）；插入操作导致分页（page split）。结果就是最终形成电话簿（类似于索引）的碎片外部碎片和内部碎片。

索引碎片的危害

清楚了索引是什么的问题，我们来看看索引碎片的危害。

假如mssql重建索引，一本完整存放（没有任何碎片）的电话簿刚好1000页，而由于前面讲的种种操作，导致了10%的碎片，那么最终我们需要1100页来存放。我们每一本书将浪费100页的纸张来印刷，也将浪费掉每个人10%的查询和阅读时间。放在SQL Server索引碎片的角度，原理是相通的：由于SQL Server读取数据的最小单位是数据页，而不是单条记录，所以，相同的查询语句需要SQL Server读取更多的磁盘宽度，加之索引碎片会浪费更多的内存资源来存放读取到的数据。因此，碎片化程度越高意味着更高的内存使用浪费和更低的查询性能。微软建议索引碎片率在5%到30%之间，做索引重组；碎片率超过30%，做索引重建工作。

解决方法

我们从以下几个方面来描述解决方法：

? 模拟产生索引碎片

? 获取索引碎片信息

? 重建索引

? 前后对比

模拟产生索引碎片

我假设需要变更100万条数据记录，这些变更包括UPDATE、DELETE和INSERT操作。在前一篇文章创建的表dbo.SalesOrder基础上，我们使用如下方法变更数据：

获取索引碎片信息

我们可以使用系统函数sys.dm_db_index_physical_stats来获取索引碎片信息。查询索引碎片的方法如下：

查询结果展示如下图所示：

重建索引

找准了解决问题的方向，处理起来就变得轻车熟路了，从查询结果我发现主键碎片率达到了92.2%，已经是非常之高了。重建索引的方法：

再次执行索引碎片查询，结果如下：

前后对比

重整索引碎片以后，主键碎片率从92.2%降低到0.1%；索引空间、数据空间和总空间大小分别减少了4.3%、52.8%和72.4%，平均空间减少达43.17%。

注意事项

在产品环境中重建索引需要十分小心，原因是：

? 重建索引会消耗大量的系统I/O读写资源。

? 重建索引会导致查询进程的死锁或者锁等待，尤其是非企业版SQL Server（企业版可以使用ONLINE选项来最大限度规避这个问题）。

? 重建索引会导致数据库日志文件暴涨，而因此会给Database Mirroring、Log Shipping和Backup带来压力。

所以，请选择业务低谷期进行索引碎片重整的操作。

总结

这篇文章从索引碎片是什么，有哪些危害，如何解决碎片问题和需要注意的事项等方面，详细探讨了导致高CPU使用率的又一常见原因--索引碎片。

加入"云和恩墨大讲堂"，参与讨论学习

（编辑：站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!