SQL Server 索引碎片治理实战：从 avg_fragmentation_in_percent 到在线重建

dbaai

深夜收到报表库的告警，一张 8000 万行的订单明细表，同样的区间查询昨天 400ms，今天变成 4.2s。执行计划没变，等待类型是 PAGE_LATCH 而不是锁等待，CPU 和 IO 都不忙——看平均值挺正常，就是慢。打开索引的物理统计一看，聚集索引的 avg_fragmentation_in_percent 已经 72%，avg_page_space_used_in_percent 只剩 58%。这就是典型的碎片病：数据物理上散成了一地，逻辑顺序和物理顺序对不上，页内还塞了一半空气。

一、具体的问题

碎片不是"空间不够"，也不是"统计信息过期"，而是 B 树叶子层的物理布局出了问题。长年累月的随机 INSERT、页拆分、DELETE 后留下的空洞，会让范围扫描（range scan）的预读失效、逻辑读翻倍。很多团队的应对方式是"月底停机全量 REBUILD 所有索引"，结果问题反而更多：大表重建耗时长、日志暴涨、还把热表的缓存全冲掉了。

本文要回答三个问题：碎片到底伤不伤性能、伤到什么程度才值得动手；REORGANIZE 和 REBUILD 怎么选、什么版本能在线做；重建之后怎么让碎片长得慢一点。

二、核心原理

先纠正三个常见误区。

第一，碎片只影响顺序读，不影响单点查找。 等值查找走到那一页就读那几页，碎不碎片几乎无感；但区间扫描、排序、连接要顺着叶子链大量顺序读，物理上散落就会造成逻辑读暴涨和预读失效。所以"碎片高但都是点查"的场景，重建收益接近零，别白折腾。

第二，REORGANIZE 和 REBUILD 是两种东西，不是大小号。 REORGANIZE 是在线的叶子层整理（按页重排、压缩 LOB、顺带以低采样率更新统计），全程只持有短时锁，任何版本都能跑；REBUILD 是整棵树重建，默认（非企业版）会持有 Sch-M 锁把表锁死，企业版加 ONLINE = ON 才能在线。REBUILD 会全量更新统计信息，REORGANIZE 不会全量。低碎片用 REORGANIZE，高碎片才上 REBUILD。

第三，页密度往往比碎片率更值得看。 avg_page_space_used_in_percent 低，说明一页里一半是空气，同样 100 页的数据要读 170 页——这时即使碎片率只有 30%，代价也已经不小。反过来，碎片率高但页密度高（批量顺序插入场景），扫描也不慢。

修复的收益链条是：整理物理布局 → 页密度回升 → 逻辑读下降 → 预读恢复 → 查询变快。而让碎片长得慢，靠的是填充因子（FILLFACTOR）和插入模式的治理，不是靠更频繁的重建。

三、实例参考（动手步骤）

步骤 0：确认现状基线

先量化，再动手。用 sys.dm_db_index_physical_stats 拿物理画像：

2. SELECT
3.     OBJECT_SCHEMA_NAME(ps.object_id) AS [schema],
4.     OBJECT_NAME(ps.object_id)        AS [table],
5.     i.name                           AS [index],
6.     i.type_desc,
7.     ps.index_level,
8.     ps.avg_fragmentation_in_percent,
9.     ps.avg_page_space_used_in_percent,
10.     ps.fragment_count,
11.     ps.avg_fragment_size_in_pages,
12.     ps.page_count
13. FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
14. JOIN sys.indexes i
15.   ON i.object_id = ps.object_id AND i.index_id = ps.index_id
16. WHERE ps.index_level = 0           -- 只看叶子层
17.   AND ps.page_count >= 1000        -- 小于 1000 页的 DMV 采样不可靠
18. ORDER BY ps.avg_fragmentation_in_percent DESC;

复制代码

注意两个门槛：DETAILED 精确但很重，大表用 SAMPLED 就够；DMV 对小于 1000 页的索引统计不准确，小表碎片无所谓。

步骤 1：定位碎片大户与使用情况

碎片高但不被使用的索引，治理方式是删掉而不是重建。把物理统计和 sys.dm_db_index_usage_stats 关联，剔掉"零扫描"的：

2. SELECT
3.     OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS obj,
4.     i.name AS idx,
5.     s.user_seeks, s.user_scans, s.user_lookups,
6.     s.user_updates
7. FROM sys.indexes i
8. LEFT JOIN sys.dm_db_index_usage_stats s
9.   ON s.object_id = i.object_id AND s.index_id = i.index_id
10.      AND s.database_id = DB_ID()
11. WHERE i.index_id > 1 AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
12.   AND s.user_seeks + s.user_scans + s.user_lookups < 10
13.   AND s.user_updates > 10000
14. ORDER BY s.user_updates DESC;

复制代码

user_updates 远大于 user_seeks + user_scans 的索引，每次 UPDATE/DELETE 都在为它付拆页的代价，却几乎没人读它——这类是碎片和写入放大的双重制造者。

步骤 2：分诊——按碎片率选动作

微软官方的分诊线（再做微调）：

2. -- 碎片 10%~30%，页密度低于 75%：在线整理，REORGANIZE
3. ALTER INDEX idx_order_detail_created ON dbo.order_detail
4. REORGANIZE WITH (LOB_COMPACTION = ON);
6. -- 碎片 > 30% 且表不大：重建（先看步骤 3 的在线方案）
7. ALTER INDEX idx_order_detail_created ON dbo.order_detail
8. REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);

复制代码

不要一刀切全量重建。以我们那张 8000 万行的表为例，最后只动了碎片最高的 3 个索引，总耗时 41 分钟，而不是全库 6 小时。

步骤 3：在线重建与可恢复重建

企业版 2017+ 用可恢复在线重建，半夜跑不完可以挂着，不占窗口：

2. ALTER INDEX idx_order_detail_created ON dbo.order_detail
3. REBUILD WITH (
4.     ONLINE = ON,
5.     RESUMABLE = ON,
6.     MAX_DURATION = 30 MINUTES,   -- 单次运行 30 分钟自动挂起
7.     MAXDOP = 4,
8.     SORT_IN_TEMPDB = ON
9. );
11. -- 查看挂起中的重建
12. SELECT * FROM sys.index_resumable_operations;
14. -- 第二天续跑
15. ALTER INDEX idx_order_detail_created ON dbo.order_detail
16. RESUME WITH (MAXDOP = 4);

复制代码

非企业版在线不了，REBUILD 前必须确认窗口和依赖：ONLINE = OFF 时是 Sch-M，会话一进来就排队（和 MDL 是一个道理）。兜底策略是大表先 REORGANIZE 撑一段时间，等迁移到企业版再做在线重建。

步骤 4：填充因子与页密度治理

重建一次性，FILLFACTOR 决定碎片长得快不快。给随机 UPDATE 多的表留 10%~20% 空隙：

2. -- 查看当前填充因子
3. SELECT i.name, i.fill_factor
4. FROM sys.indexes i
5. WHERE i.object_id = OBJECT_ID('dbo.order_detail') AND i.index_id > 0;
7. -- 只对随机更新热点索引设 90，不要全库乱设
8. ALTER INDEX idx_order_detail_created ON dbo.order_detail
9. REBUILD WITH (FILLFACTOR = 90);
11. -- 重建后验证页密度回升
12. SELECT avg_page_space_used_in_percent, avg_fragmentation_in_percent
13. FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.order_detail'),
14.      2, 0, 'DETAILED');

复制代码

纯追加型（自增键、只插不改）的表 FILLFACTOR 保持 100，留空隙纯属浪费缓存。

步骤 5：维护窗口脚本

把分诊逻辑固化成一个每周任务，只动该动的索引：

2. DECLARE @sql nvarchar(max) = N'';
3. SELECT @sql = @sql +
4.     N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' +
5.     QUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id)) + N'.' +
6.     QUOTENAME(OBJECT_NAME(ps.object_id)) +
7.     CASE WHEN ps.avg_fragmentation_in_percent < 30
8.          THEN N' REORGANIZE;' ELSE N' REBUILD;' END + CHAR(13)
9. FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
10. JOIN sys.indexes i
11.   ON i.object_id = ps.object_id AND i.index_id = ps.index_id
12. WHERE ps.index_level = 0 AND ps.page_count >= 1000
13.   AND i.is_disabled = 0 AND i.is_hypothetical = 0
14.   AND ps.avg_fragmentation_in_percent >= 10;
15. EXEC sp_executesql @sql;

复制代码

生产上更建议直接用 Ola Hallengren 的 IndexOptimize，它自带分诊、在线选项、超时与日志，造轮子不如用社区打磨过的。

步骤 6：监控与告警落地

不要等用户报慢才查。建一张巡检表，每周快照对比：

2. CREATE TABLE dbo.index_frag_snapshot (
3.     snap_date   date,
4.     schema_name sysname,
5.     table_name  sysname,
6.     index_name  sysname,
7.     frag_pct    float,
8.     page_density float,
9.     page_count  bigint
10. );
12. -- 周一凌晨采样，环比涨幅 > 20 个百分点的索引发告警
13. INSERT INTO dbo.index_frag_snapshot
14. SELECT CONVERT(date, GETDATE()), OBJECT_SCHEMA_NAME(ps.object_id),
15.        OBJECT_NAME(ps.object_id), i.name,
16.        ps.avg_fragmentation_in_percent,
17.        ps.avg_page_space_used_in_percent, ps.page_count
18. FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
19. JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
20. WHERE ps.index_level = 0 AND ps.page_count >= 1000;

复制代码

四、实操检查清单

• 先查 avg_fragmentation_in_percent 和 avg_page_space_used_in_percent 两个指标，且只统计 page_count >= 1000 的索引，小表不治理。
  
• 动手前关联 sys.dm_db_index_usage_stats，user_seeks + user_scans + user_lookups 长期为零的索引直接下线，别重建它。
  
• 点查为主的负载（碎片高但都是等值查找）不重建，确认负载里有区间扫描、排序、连接才值得动手。
  
• 碎片 10%~30% 用 REORGANIZE（在线、任何版本可用），超过 30% 才考虑 REBUILD。
  
• REBUILD 非企业版会锁表；企业版务必 ONLINE = ON，2017+ 再加 RESUMABLE = ON 和 MAX_DURATION。
  
• FILLFACTOR 只对随机 UPDATE 热点索引设 85~95，追加型索引保持 100，不要全库统一设一个值。
  
• 大批量 DELETE/UPDATE 后，及时补一次 REORGANIZE，别让空洞留到大促窗口。
  
• 每周对 LIMITED 模式快照入库，环比涨幅超过 20 个百分点的索引主动触发治理，不等业务报慢。
  
• REORGANIZE 带上 LOB_COMPACTION = ON，否则带大字段的索引只整理了行内页。
  
• 重建后用 DETAILED 模式复核页密度与碎片率，确认修复生效再收工。
  

几个容易踩的坑

一是把 REBUILD 当万能药。 碎片高但负载全是点查，重建后 P95 纹丝不动，白花了 6 小时窗口。先确认负载类型再动手，sys.dm_db_index_operational_stats 里的 range_scan_count 是判断依据。

二是非企业版在业务时段 REBUILD。 Sch-M 锁一挂，后面所有查询排队，等于一次"计划内事故"。在线重建不可用时，用 REORGANIZE 兜底或挪窗口。

三是 REORGANIZE 当成统计信息维护。 它确实会更新统计，但采样率很低，直方图可能严重失真。统计信息该用 UPDATE STATISTICS ... WITH FULLSCAN 单独维护，别指望顺带。

四是 FILLFACTOR 全库统一设 80。 缓存被空气占掉两成，内存命中率反而下降。它只该给随机更新热点用。

五是重建完不看页密度。 有一种情况是碎片率降了、密度还低——通常是填充因子设小了或又是批量删除造成的，得回头调参数，不是再重建一遍。

治理前后对比

指标	治理前	治理后
聚集索引碎片率	72%	1.2%
页密度	58%	96.3%
区间查询 P95	4.2s	410ms
逻辑读（单次区间查询）	218,400 页	41,700 页
维护窗口（全库）	6h 停机全量重建	41min 仅动 3 个索引
告警工单	3~5 起/周	0（连续 45 天）

碎片治理的价值不在"重建了多狠"，而在"动得有多准"。看负载、看密度、看使用统计，然后只修真正在流血的索引。