SQL Server 索引碎片率查询及维护

一、使用 `sys.dm_db_index_physical_stats` 动态管理函数‌

这是 SQL Server 提供的标准方法，可直接获取索引的物理存储状态。

‌基本语法

SELECT
    DB_NAME(ips.database_id) AS DatabaseName,
    OBJECT_NAME(ips.object_id) AS TableName,
    si.name AS IndexName,
    ips.index_type_desc AS IndexType,
    ips.avg_fragmentation_in_percent AS FragmentationPercent,
    ips.page_count AS PageCount
FROM
    sys.dm_db_index_physical_stats(
        DB_ID(),      -- 当前数据库ID（替换为其他数据库名可查其他库）
        NULL,         -- 对象ID（NULL表示所有表）
        NULL,         -- 索引ID（NULL表示所有索引）
        NULL,         -- 分区号（NULL表示所有分区）
        'LIMITED'     -- 扫描模式：LIMITED（快速）、SAMPLED（抽样）、DETAILED（详细）
    ) ips
JOIN
    sys.indexes si ON ips.object_id = si.object_id AND ips.index_id = si.index_id
WHERE
    ips.avg_fragmentation_in_percent > 5  -- 仅显示碎片率>5%的索引
    AND si.name IS NOT NULL               -- 排除堆表（无索引的表）
ORDER BY
    FragmentationPercent DESC;

二、关键参数说明‌

‌扫描模式 (scan_mode)‌
- LIMITED：快速扫描，仅检查元数据和最低限度的页面（‌推荐默认使用‌）。
- SAMPLED：抽样1%的数据页，结果可能不精确。
- DETAILED：扫描所有数据页，结果精确但资源消耗大。
‌过滤条件‌
- avg_fragmentation_in_percent：逻辑碎片百分比（页顺序错乱）。
- page_count：索引占用的数据页数量，小索引（如 < 1000 页）的碎片可忽略。

‌三、结果解读

列名	说明
`FragmentationPercent`	‌逻辑碎片率‌：值越高，索引性能越差。
`IndexType`	索引类型（CLUSTERED INDEX、NONCLUSTERED INDEX、HEAP 等）。
`PageCount`	索引占用的数据页数。小索引（如 < 1000 页）即使碎片高，优化意义不大。

四、操作建议‌

‌根据碎片率采取行动‌

碎片率	操作
‌< 5%‌	无需处理
‌5%~30%‌	重组索引 (`REORGANIZE`)
‌> 30%‌	重建索引 (`REBUILD`)

‌注意事项‌

‌小索引忽略‌：若 page_count < 1000，即使碎片率高，优化效果微乎其微。
‌在线操作‌：生产环境尽量使用 ONLINE = ON（需企业版）避免阻塞用户操作。
‌维护窗口‌：大表重建可能耗时较长，建议在低峰期执行。

‌五、扩展：快速查看单个表的索引碎片

DECLARE @TableName NVARCHAR(255) = 'YourTableName';  -- 替换为你的表名

SELECT
    si.name AS IndexName,
    ips.avg_fragmentation_in_percent AS FragmentationPercent,
    ips.page_count AS PageCount
FROM
    sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID(@TableName),  -- 指定表名
        NULL,
        NULL,
        'LIMITED'
    ) ips
JOIN
    sys.indexes si ON ips.object_id = si.object_id AND ips.index_id = si.index_id
WHERE
    si.name IS NOT NULL
ORDER BY
    FragmentationPercent DESC;

‌六、自动化建议‌

‌使用维护计划‌
- 在 SQL Server Management Studio (SSMS) 中创建定期维护任务，自动重组/重建索引。
- 路径：数据库右键 → Tasks → Maintenance Plans → 配置索引优化任务。
‌使用 Ola Hallengren 脚本‌
- 免费开源脚本，支持灵活配置碎片率阈值：

EXECUTE dbo.IndexOptimize
  @Databases = 'USER_DATABASES',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE',
  @FragmentationLevel1 = 5,
  @FragmentationLevel2 = 30;