SQLServer优化之索引重建

在 SQL Server 中，索引碎片分为 逻辑碎片（页分裂） 和 物理碎片（不连续页）。判断碎片是“轻度”还是“重度”需要结合 碎片百分比 和 索引的页数量 两个关键指标。

---

1. 查询索引碎片信息

通过系统函数 sys.dm_db_index_physical_stats 获取碎片详情：

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.index_type_desc AS IndexType,
    ps.avg_fragmentation_in_percent AS FragmentationPercent,
    ps.page_count AS PageCount
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
INNER JOIN 
    sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE 
    ps.avg_fragmentation_in_percent > 5  -- 仅关注有碎片的索引
    AND ps.page_count > 100              -- 仅处理有实际数据量的索引
ORDER BY 
    FragmentationPercent DESC;

---

2. 判断标准

根据微软官方建议和最佳实践，碎片程度分为以下两类：

(1) 轻度碎片

• 碎片百分比：5% ≤ 碎片率 < 30%
• 页数量：页数 ≥ 1000
  （如果页数太少，即使碎片率高，整理收益也有限）

操作建议：
使用 ALTER INDEX ... REORGANIZE（在线操作，对业务影响小）。

(2) 重度碎片

• 碎片百分比：碎片率 ≥ 30%
• 页数量：页数 ≥ 1000

操作建议：
使用 ALTER INDEX ... REBUILD（更彻底，但可能阻塞业务，建议在维护窗口操作）。

---

3. 注意事项

1. 页数量重要性：

   • 如果索引仅有 100 页，即使碎片率 50%，实际性能影响也较小，无需立即处理。
   • 重点关注页数多（如 ≥ 1000 页）且碎片率高的索引。

2. 自动维护建议：

   • 定期（如每周）检查碎片，避免积累。
   • 使用 Ola Hallengren 的维护脚本或 SQL Server 维护计划自动化处理。

3. 特殊场景：

   • 对于 HEAP 表（无聚集索引），使用 ALTER TABLE ... REBUILD。
   • 若使用 SSD 存储，物理碎片影响较小，可适当放宽阈值。

---

4. 碎片整理操作示例

(1) 轻度碎片整理（REORGANIZE）

ALTER INDEX [索引名] ON [表名] REORGANIZE;

(2) 重度碎片整理（REBUILD）

-- 默认选项（可能阻塞）
ALTER INDEX [索引名] ON [表名] REBUILD;

-- 在线重建（企业版可用，减少阻塞）
ALTER INDEX [索引名] ON [表名] REBUILD WITH (ONLINE = ON);

---

5. 自动化脚本

-- 根据阈值自动选择 REORGANIZE 或 REBUILD
DECLARE @FragmentationThresholdForRebuild INT = 30; -- 重度阈值
DECLARE @MinPageCount INT = 1000;                   -- 最小页数

SELECT 
    'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(ps.object_id) + '] ' +
    CASE 
        WHEN ps.avg_fragmentation_in_percent >= @FragmentationThresholdForRebuild 
            THEN 'REBUILD;' 
        ELSE 'REORGANIZE;' 
    END AS Command
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
INNER JOIN 
    sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE 
    ps.avg_fragmentation_in_percent > 5
    AND ps.page_count >= @MinPageCount;

---