摘要

上一篇文章分析了高CPU使用率的原因之一是索引缺失，接下来本系列文章之二的“索引碎片”是CPU高使用率的又一常见的原因。解决索引碎片问题是解决SQL Server服务响应缓慢，查询超时的又一利器。

问题引入

“鸟哥，我上一篇文章分享了因为索引缺失导致CPU高使用率的话题，反响不错。接下来，我打算分享索引碎片导致CPU高使用率的话题。”，菜鸟主动找到老鸟汇报工作。
上一篇文章详情参见链接：RDS SQL Server - ***实践 - 高CPU使用率系列之索引缺失

索引碎片是什么

索引碎片既指索引文件页中的空白空间；又指被Page Split的索引页；还指索引失序的数据页。前面两种我们称之为索引内部碎片，后面一种我们叫着索引外部碎片。
前面是干瘪瘪的理论描述，下面举一个关于电话簿实例就比较好理解了：假如电话簿按照城市名称、姓名和电话号码组合排序的方式来存储所有人员的联系电话。以下几种情况都需要更新电话簿：
 当一个人更换电话号码的时候或者改名字的时候（西方国家女子结婚后，会修改姓氏为老公的姓，比如：奥巴马的老婆叫米歇尔-奥巴马）：需要更新操作
 当一个人从一个城市搬迁到另一个城市的时候：需要删除原来的记录，在相应的城市插入新记录
 当有新人办理了电话业务的时候：需要在相应城市插入记录
 当作废电话号码的时候：需要删除对应条目
这些操作带来的后果是：更新操作可能导致失序（out of order）；删除操作导致空白条目（empty space）；插入操作导致分页（page split）。结果就是最终形成电话簿（类似于索引）的碎片外部碎片和内部碎片。

索引碎片的危害

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

解决方法

我们从以下几个方面来描述解决方法：
 模拟产生索引碎片
 获取索引碎片信息
 重建索引
 前后对比

模拟产生索引碎片

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

USE TestDb
GO

SET NOCOUNT ON

DECLARE
    @do INT = 0
    , @loop INT = 1000000
    , @sql NVARCHAR(MAX) = N''
;

WHILE @do < @loop
BEGIN
        SET @sql = 
        CASE  
            WHEN @do % 3 = 0
                THEN N'DELETE TOP(1) FROM dbo.SalesOrder;'
            WHEN @do % 3 = 1
                THEN N'UPDATE TOP(1) A SET OrderQty = OrderQty + 1, Price = Price - 1, OrderDate = GETDATE() FROM dbo.SalesOrder AS A'
            WHEN @do % 3 = 2
                THEN N'INSERT INTO dbo.SalesOrder(OrderID, ItemID, UserID, OrderQty, Price, OrderDate, LastUpdateTime, OrderComment)
                            SELECT TOP 1 NEWID(),ItemID, UserID, OrderQty, Price, OrderDate, LastUpdateTime, OrderComment
                            FROM dbo.SalesOrder
                '
            ELSE N''
        END

        EXEC sys.sp_executesql @sql

        IF @do % 2000 = 0
        BEGIN
            RAISERROR('%d rows', 10, 1, @do) WITH NOWAIT
        END

        SET @do = @do + 1;
END
GO

获取索引碎片信息

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

USE TestDb
GO

SELECT 
        db_name(database_id) AS db_name
        ,object_name(ix.object_id) AS object_name
        ,ix.name
        ,avg_fragmentation_in_percent
        ,*
FROM sys.dm_db_index_physical_stats(db_id(),object_id('dbo.SalesOrder','U'),NULL,NULL,'LIMITED') AS fra
    CROSS APPLY sys.indexes AS ix WITH (NOLOCK) 
WHERE ix.object_id = fra.object_id
    and ix.index_id = fra.index_id

EXEC sys.sp_spaceused SalesOrder

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

重建索引

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

USE TestDb
GO

ALTER INDEX ALL
ON dbo.SalesOrder REBUILD
WITH (ONLINE = ON, FILLFACTOR = 90)
;

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

前后对比

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

注意事项

在产品环境中重建索引需要十分小心，原因是：
 重建索引会消耗大量的系统I/O读写资源。
 重建索引会导致查询进程的死锁或者锁等待，尤其是非企业版SQL Server（企业版可以使用ONLINE选项来最大限度规避这个问题）。
 重建索引会导致数据库日志文件暴涨，而因此会给Database Mirroring、Log Shipping和Backup带来压力。
所以，请选择业务低谷期进行索引碎片重整的操作。

总结

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

引用

Stop Worrying About SQL Server Fragmentation
Reorganize and Rebuild Indexes