SQL Server 索引碎片治理实战：从 avg_fragmentation_in_percent 到在线重建
深夜收到报表库的告警，一张 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 拿物理画像：
SELECT
OBJECT_SCHEMA_NAME(ps.object_id) AS ,
OBJECT_NAME(ps.object_id) AS ,
i.name AS ,
i.type_desc,
ps.index_level,
ps.avg_fragmentation_in_percent,
ps.avg_page_space_used_in_percent,
ps.fragment_count,
ps.avg_fragment_size_in_pages,
ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
JOIN sys.indexes i
ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.index_level = 0 -- 只看叶子层
AND ps.page_count >= 1000 -- 小于 1000 页的 DMV 采样不可靠
ORDER BY ps.avg_fragmentation_in_percent DESC;
注意两个门槛：DETAILED 精确但很重，大表用 SAMPLED 就够；DMV 对小于 1000 页的索引统计不准确，小表碎片无所谓。
步骤 1：定位碎片大户与使用情况
碎片高但不被使用的索引，治理方式是删掉而不是重建。把物理统计和 sys.dm_db_index_usage_stats 关联，剔掉"零扫描"的：
SELECT
OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS obj,
i.name AS idx,
s.user_seeks, s.user_scans, s.user_lookups,
s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
ON s.object_id = i.object_id AND s.index_id = i.index_id
AND s.database_id = DB_ID()
WHERE i.index_id > 1 AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
AND s.user_seeks + s.user_scans + s.user_lookups < 10
AND s.user_updates > 10000
ORDER BY s.user_updates DESC;
user_updates 远大于 user_seeks + user_scans 的索引，每次 UPDATE/DELETE 都在为它付拆页的代价，却几乎没人读它——这类是碎片和写入放大的双重制造者。
步骤 2：分诊——按碎片率选动作
微软官方的分诊线（再做微调）：
-- 碎片 10%~30%，页密度低于 75%：在线整理，REORGANIZE
ALTER INDEX idx_order_detail_created ON dbo.order_detail
REORGANIZE WITH (LOB_COMPACTION = ON);
-- 碎片 > 30% 且表不大：重建（先看步骤 3 的在线方案）
ALTER INDEX idx_order_detail_created ON dbo.order_detail
REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);
不要一刀切全量重建。以我们那张 8000 万行的表为例，最后只动了碎片最高的 3 个索引，总耗时 41 分钟，而不是全库 6 小时。
步骤 3：在线重建与可恢复重建
企业版 2017+ 用可恢复在线重建，半夜跑不完可以挂着，不占窗口：
ALTER INDEX idx_order_detail_created ON dbo.order_detail
REBUILD WITH (
ONLINE = ON,
RESUMABLE = ON,
MAX_DURATION = 30 MINUTES, -- 单次运行 30 分钟自动挂起
MAXDOP = 4,
SORT_IN_TEMPDB = ON
);
-- 查看挂起中的重建
SELECT * FROM sys.index_resumable_operations;
-- 第二天续跑
ALTER INDEX idx_order_detail_created ON dbo.order_detail
RESUME WITH (MAXDOP = 4);
非企业版在线不了，REBUILD 前必须确认窗口和依赖：ONLINE = OFF 时是 Sch-M，会话一进来就排队（和 MDL 是一个道理）。兜底策略是大表先 REORGANIZE 撑一段时间，等迁移到企业版再做在线重建。
步骤 4：填充因子与页密度治理
重建一次性，FILLFACTOR 决定碎片长得快不快。给随机 UPDATE 多的表留 10%~20% 空隙：
-- 查看当前填充因子
SELECT i.name, i.fill_factor
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.order_detail') AND i.index_id > 0;
-- 只对随机更新热点索引设 90，不要全库乱设
ALTER INDEX idx_order_detail_created ON dbo.order_detail
REBUILD WITH (FILLFACTOR = 90);
-- 重建后验证页密度回升
SELECT avg_page_space_used_in_percent, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.order_detail'),
2, 0, 'DETAILED');
纯追加型（自增键、只插不改）的表 FILLFACTOR 保持 100，留空隙纯属浪费缓存。
步骤 5：维护窗口脚本
把分诊逻辑固化成一个每周任务，只动该动的索引：
DECLARE @sql nvarchar(max) = N'';
SELECT @sql = @sql +
N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' +
QUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id)) + N'.' +
QUOTENAME(OBJECT_NAME(ps.object_id)) +
CASE WHEN ps.avg_fragmentation_in_percent < 30
THEN N' REORGANIZE;' ELSE N' REBUILD;' END + CHAR(13)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i
ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.index_level = 0 AND ps.page_count >= 1000
AND i.is_disabled = 0 AND i.is_hypothetical = 0
AND ps.avg_fragmentation_in_percent >= 10;
EXEC sp_executesql @sql;
生产上更建议直接用 Ola Hallengren 的 IndexOptimize，它自带分诊、在线选项、超时与日志，造轮子不如用社区打磨过的。
步骤 6：监控与告警落地
不要等用户报慢才查。建一张巡检表，每周快照对比：
CREATE TABLE dbo.index_frag_snapshot (
snap_date date,
schema_name sysname,
table_namesysname,
index_namesysname,
frag_pct float,
page_density float,
page_countbigint
);
-- 周一凌晨采样，环比涨幅 > 20 个百分点的索引发告警
INSERT INTO dbo.index_frag_snapshot
SELECT CONVERT(date, GETDATE()), OBJECT_SCHEMA_NAME(ps.object_id),
OBJECT_NAME(ps.object_id), i.name,
ps.avg_fragmentation_in_percent,
ps.avg_page_space_used_in_percent, ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
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%
区间查询 P954.2s410ms
逻辑读（单次区间查询）218,400 页41,700 页
维护窗口（全库）6h 停机全量重建41min 仅动 3 个索引
告警工单3~5 起/周0（连续 45 天）
碎片治理的价值不在"重建了多狠"，而在"动得有多准"。看负载、看密度、看使用统计，然后只修真正在流血的索引。
页:
[1]