SQL Server索引碎片怎么处理？自动维护与性能优化方法

什么是SQL Server索引碎片？

SQL Server中的B-Tree索引会随着INSERT、UPDATE、DELETE操作不断发生变化。当数据页的逻辑顺序与物理存储顺序出现偏差时，就形成了索引碎片。碎片对所有查询的影响并不相同，对于需要进行大量索引扫描或范围扫描的查询，碎片可能增加磁盘I/O。

因此，不能看到索引碎片率较高就立即执行重建。Microsoft目前也建议结合实际查询负载、页面密度、存储性能和维护操作本身的资源消耗判断是否需要维护。

如何查看SQL Server索引碎片？

可以使用sys.dm_db_index_physical_stats查看索引状态：

SELECT
    DB_NAME() AS DatabaseName,
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.page_count,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED'
) AS ips
INNER JOIN sys.indexes AS i
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.index_id > 0
  AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;

其中avg_fragmentation_in_percent表示逻辑碎片率，avg_page_space_used_in_percent反映页面使用率，page_count则可以帮助过滤掉规模很小的索引。

实际维护时，不建议只看碎片率。例如一个只有几十页的小索引，即使碎片率达到较高水平，维护它通常也没有明显价值。

REORGANIZE和REBUILD有什么区别？

SQL Server主要提供两种传统的索引维护方式。

REORGANIZE适合资源比较紧张或者希望在线完成维护的场景：

ALTER INDEX IX_User_Name
ON dbo.Users
REORGANIZE;

REORGANIZE主要重新整理索引叶级页面，资源消耗通常低于REBUILD，而且始终在线运行，维护期间查询和数据修改可以继续进行。它不会像REBUILD一样更新索引统计信息。

REBUILD则是重新创建整个索引：

ALTER INDEX IX_User_Name
ON dbo.Users
REBUILD;

REBUILD可以更彻底地消除碎片并重新压缩页面，同时会更新索引统计信息。在线重建可以降低阻塞，但仍然会消耗较多CPU、内存、I/O和日志空间。

如果数据库版本和版本授权支持，可以考虑：

ALTER INDEX IX_User_Name
ON dbo.Users
REBUILD WITH (ONLINE = ON);

对于大型数据库，还需要提前确认磁盘剩余空间，因为重建索引过程中可能需要同时保留原索引和新索引。

不要机械套用30%碎片率规则

以前很多SQL Server维护脚本喜欢采用类似碎片率低于10%不处理、10%到30%执行REORGANIZE、超过30%执行REBUILD的固定规则。

这种方法可以作为简单脚本的起点，但不应该当成绝对标准。Microsoft当前的索引维护建议明确强调，维护决策应该结合工作负载和维护成本进行判断，而不是单纯依赖固定碎片率阈值。

例如，一个主要执行点查询的索引，即使碎片率比较高，也可能几乎没有性能影响。而一个经常执行大范围扫描的索引，页面顺序和页面密度就可能更加重要。

如何实现自动索引维护？

可以使用SQL Server Agent建立定时任务，每天或每周扫描索引，根据碎片程度决定维护方式。例如：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql +=
    CASE
        WHEN avg_fragmentation_in_percent >= 30
            THEN N'ALTER INDEX ' + QUOTENAME(i.name) +
                 N' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) +
                 N'.' + QUOTENAME(o.name) + N' REBUILD;' + CHAR(13)
        WHEN avg_fragmentation_in_percent >= 10
            THEN N'ALTER INDEX ' + QUOTENAME(i.name) +
                 N' ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) +
                 N'.' + QUOTENAME(o.name) + N' REORGANIZE;' + CHAR(13)
        ELSE N''
    END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i
  ON ips.object_id = i.object_id
 AND ips.index_id = i.index_id
JOIN sys.objects o
  ON i.object_id = o.object_id
WHERE ips.index_id > 0
  AND ips.page_count >= 1000;

EXEC sp_executesql @sql;

这个示例适合说明自动维护思路，生产环境不要直接照搬。建议增加索引类型、分区、执行时间、日志空间、维护失败重试以及最大执行时长等控制条件。

对于大型数据库，还可以采用更精细的维护策略，只处理真正影响业务的索引，并结合SQL Server Agent、Query Store和执行日志观察维护前后的实际效果。Microsoft也建议使用Query Store比较维护前后的查询性能，而不是默认认为重建索引一定能够提升性能。

索引维护还要关注统计信息

有些情况下，索引REBUILD之后查询明显变快，并不完全是碎片减少造成的。REBUILD会更新索引统计信息，因此原本由于统计信息过期导致的执行计划问题，也可能同时得到改善。

如果实际问题只是统计信息过期，可以单独使用：

UPDATE STATISTICS dbo.Users IX_User_Name;

相比完整重建索引，单独更新统计信息通常需要消耗更少的资源。

SQL Server索引维护的实用策略

生产环境可以按照监控→判断→维护→验证的方式处理。

1. 先定期统计索引的碎片率、页面数量和页面密度。
2. 然后结合Query Store观察慢查询是否与索引扫描、I/O增加有关。
3. 确认确实存在收益后，再选择REORGANIZE或REBUILD。

对于写入频繁的表，还应该关注Page Split。索引并不是填得越满越好，如果索引中间位置经常插入数据，过高的页面密度可能增加页面分裂，此时可以根据实际工作负载调整FILLFACTOR。

最后，索引维护本身也是一项高成本操作。大型数据库不适合每天无差别重建所有索引，更合理的做法是根据索引规模、使用频率、碎片情况和实际查询性能进行针对性维护。

对于SQL Server数据库来说，索引维护的重点并不是把碎片率清零，而是在维护成本和查询性能之间找到合适的平衡。