SQL Server 索引碎片整理

FreeGuideOnline 最新 2026-07-08

sql SELECT dbschemas.[name] AS 'Schema', dbtables.[name] AS 'Table', dbindexes.[name] AS 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, indexstats.avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats ( DB_ID(), -- 当前数据库 NULL, -- 所有表（可指定 object_id） NULL, -- 所有索引 NULL, -- 分区 'LIMITED' -- 扫描模式 ) AS indexstats INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() AND indexstats.page_count > 100 -- 忽略小表 AND dbindexes.[name] IS NOT NULL -- 仅关注命名索引（排除堆） ORDER BY indexstats.avg_fragmentation_in_percent DESC;


### 关键列解释

- `avg_fragmentation_in_percent`：逻辑碎片百分比。这是最重要的指标，微软推荐阈值：
  - **> 5% 且 ≤ 30%**：建议重组索引（`ALTER INDEX ... REORGANIZE`）
  - **> 30%**：建议重建索引（`ALTER INDEX ... REBUILD`）
- `page_count`：索引使用的页数。小于 100 页的索引碎片影响可忽略，不建议整理。
- `avg_page_space_used_in_percent`：页内空间使用率，反映内部碎片。低于 70% 时值得关注。

**扫描模式说明**：`LIMITED` 模式速度最快，扫描索引的父级页；`SAMPLED` 扫描 1% 的叶级页；`DETAILED` 全面扫描，最准确但慢。日常运维用 `SAMPLED` 或 `LIMITED` 即可。

## 索引整理方法：重组 vs 重建

| 特性               | 重组（REORGANIZE）                 | 重建（REBUILD）                     |
| ------------------ | --------------------------------- | ----------------------------------- |
| 原理               | 原地整理叶级页，压缩页面空闲空间，重新排序页 | 删除旧索引并重新创建，**可使用不同文件组** |
| 是否锁表           | 不长时间锁表，在线操作             | 默认模式下会获取排他锁，导致整表无法读写 |
| 事务日志影响       | 日志量小，逐页操作                 | 日志量大，是整个索引的批量操作        |
| 统计信息更新       | 不更新                             | 会重新计算统计信息（`WITH STATISTICS` 可关闭） |
| 可用性要求         | 可中断，无需独占                   | 企业版支持 `ONLINE = ON` 减少锁定     |
| 适用碎片程度       | 轻度碎片（5%~30%）                 | 重度碎片（>30%）                     |

### REORGANIZE 基本语法

```sql
ALTER INDEX [IX_IndexName] ON [dbo].[TableName] REORGANIZE;
-- 对分区索引指定分区
ALTER INDEX [IX_IndexName] ON [dbo].[TableName] REORGANIZE PARTITION = 1;

REBUILD 基本语法

-- 默认脱机重建（会锁表）
ALTER INDEX [IX_IndexName] ON [dbo].[TableName] REBUILD;

-- 联机重建（仅限 Enterprise/Developer 版）
ALTER INDEX [IX_IndexName] ON [dbo].[TableName] REBUILD WITH (ONLINE = ON);

-- 指定填充因子，减少后续内部碎片
ALTER INDEX [IX_IndexName] ON [dbo].[TableName] 
REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);

-- 指定排序位置（tempdb），避免日志增长
ALTER INDEX [IX_IndexName] ON [dbo].[TableName] 
REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);

实用技巧：

  • 对于大表重建，配置 SORT_IN_TEMPDB = ON 可将排序过程移到 tempdb，减轻用户数据库日志压力。
  • FILLFACTOR 指索引页填充率（如 80%，预留 20% 空间），适合频繁插入更新的表，延缓碎片产生。
  • 如果没有 Enterprise 版，重建过程会阻塞写操作，务必在维护窗口执行。

自动化索引碎片维护脚本

手动逐表处理不现实，通常编写一个游标或者循环脚本，根据碎片阈值动态执行 REORGANIZE 或 REBUILD。以下是一个简化但安全的示例（基于阈值自动选择操作方法）：

DECLARE @TableName NVARCHAR(256)
DECLARE @IndexName NVARCHAR(256)
DECLARE @FragPercent FLOAT
DECLARE @PageCount INT
DECLARE @SQL NVARCHAR(MAX)

DECLARE IndexCursor CURSOR FOR
SELECT 
    '[' + s.name + '].[' + t.name + ']' AS TableName,
    '[' + i.name + ']' AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.tables t ON t.object_id = ips.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 100
  AND ips.avg_fragmentation_in_percent > 5
  AND i.name IS NOT NULL
ORDER BY ips.avg_fragmentation_in_percent DESC;

OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @FragPercent, @PageCount

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @FragPercent > 30
        SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);'
    ELSE IF @FragPercent >= 5
        SET @SQL = 'ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REORGANIZE;'

    BEGIN TRY
        PRINT 'Executing: ' + @SQL
        EXEC sp_executesql @SQL
    END TRY
    BEGIN CATCH
        PRINT 'Error on ' + @IndexName + ': ' + ERROR_MESSAGE()
    END CATCH

    FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @FragPercent, @PageCount
END

CLOSE IndexCursor
DEALLOCATE IndexCursor

注意：如果使用 ONLINE = ON 但版本不支持，引擎会自动降级为脱机操作并可能导致阻塞。生产环境建议先检查 SERVERPROPERTY('Edition') 或在 CATCH 块中去掉 ONLINE 重试。

维护计划与最佳实践

  1. 碎片检查频率：

    • 核心表：每天或每周检查一次（根据写操作频繁度）。
    • 只读或低频表：每月检查即可。

  2. 整理执行窗口：

    • 避开业务高峰期，通常在夜间或周末。
    • 大表重建耗时可能很久，先估算测试。

  3. 统计信息更新：

    • 重建索引会自动更新统计信息，但重组不会。重组后如果查询计划变差，需手动 UPDATE STATISTICS。

  4. 分区表处理：

    • 对于分区的大表，可以仅重建碎片化严重的分区，语法：ALTER INDEX ... REBUILD PARTITION = N。

  5. 填充因子调优：

    • 根据表写入模式调整：插入随机键值（GUID）的表建议 FILLFACTOR = 70~80；自增主键的表默认 100 即可。

  6. 资源监控：

    • 重建会大量消耗 CPU、I/O 和日志空间。务必监控事务日志大小，必要时分批次进行。

  7. 使用智能维护方案：

    • Ola Hallengren 的维护脚本社区版广泛使用，可配置动态阈值和并行度，比自写脚本更稳健。

常见误区与排错

  • 误区一：“碎片越低越好”
    整理本身也会产生日志和 IO，太频繁反而增加负载。按阈值 5% 和 30% 即可。

  • 误区二：“所有索引都需整理”
    页面数少于 100 的小索引，或几乎只读的表，即便碎片高也不构成实际性能影响。

  • 误区三：“重组不需要日志”
    重组虽逐页操作，但仍产生事务日志，大表同样可能导致日志暴涨，需留意。

  • 报错：“Non-yielding Scheduler” 或死锁
    可能因可用内存不足或并发冲突。建议调整 MAXDOP 限制并行度：

    ALTER INDEX ... REBUILD WITH (MAXDOP = 4);