SQL Server索引碎片的基本知识（附Demo）

前言

索引碎片太高本身会阻碍查询的效率，这个问题要重视

1. 基本知识

索引中的数据页不再连续，导致存储和检索数据时的效率降低

碎片通常发生在以下两种情况：

• 内部碎片：索引页中有许多空闲空间
  通常是因为索引中删除了很多数据行，导致原来的数据页留下空白
• 外部碎片：索引页之间的顺序不再连续
  通常是因为数据的插入、更新和删除操作导致索引页被重新分配

索引碎片的类型

• 逻辑碎片：索引逻辑结构中存在问题，例如非连续的页链
  这种碎片会影响到查询的效率
• 物理碎片：实际存储介质上的数据页的物理分布不连续
  这种碎片会影响磁盘的读取性能

影响查询效率的原因

1. 性能下降
   -读取性能：碎片会导致数据库引擎在扫描和访问索引时需要更多的磁盘 I/O 操作，从而增加了查询时间。
   缓存效率：索引碎片会减少数据页的缓存命中率，使得更多的数据页需要从磁盘读取，影响整体性能

2. 增加的维护开销
   -索引维护：碎片化的索引会增加数据库的维护成本，包括重建和重组索引所需的时间和资源
   -存储空间：碎片化的索引可能会占用更多的磁盘空间，影响存储成本

2. 检索碎片

直奔主题，通过SQL Server索引碎片检索相关数据

2.1 dm_db_index_physical_stats

SQL Server 提供了 sys.dm_db_index_physical_stats 视图来帮助检测索引碎片

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_id,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ips
JOIN 
    sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE 
    ips.avg_fragmentation_in_percent > 10  -- 阈值可以根据实际情况调整
ORDER BY 
    ips.avg_fragmentation_in_percent DESC;

截图如下：

2.2 DBCC SHOWCONTIG

DBCC SHOWCONTIG 是 SQL Server 中用于分析表和索引碎片情况的命令

虽然在 SQL Server 2016 及以后的版本中，DBCC SHOWCONTIG 已被弃用并被 sys.dm_db_index_physical_stats 替代，但在早期版本中，它依然是一个有用的工具

目前还可以使用的话，对于某个页面的索引碎片比较严重的，可通过如下方式进行查看：

DBCC ShowContig(TableName)

截图如下：

相关的参数说明如下：

• 扫描页数：表中总共扫描的页数
  表示 DBCC SHOWCONTIG 命令检查的页的数量

• 扫描区数：表中扫描的区（或称为分区）的数量
  区是磁盘上的数据存储单位

• 区切换次数：在扫描过程中切换到不同区的次数
  频繁的区切换可能影响性能，因为可能导致磁盘 I/O 操作的增加

• 每个区的平均页数：每个区中包含的平均页数。这个指标有助于了解区的分布情况。

• 扫描密度 [最佳计数:实际计数]：扫描密度是实际扫描的页数与理想情况下应该扫描的页数之间的比例
  较低的密度表明存在较高的碎片

• 逻辑扫描碎片：表示逻辑上索引的碎片比例
  逻辑碎片表示数据页的逻辑顺序不再连续，通常用百分比表示
  高逻辑碎片通常意味着数据页的顺序被破坏

• 区扫描碎片：表示区中页的碎片比例
  区扫描碎片较高意味着在扫描过程中发现了许多不连续的页，通常也用百分比表示

• 每页的平均可用字节数：每页上未使用的字节数

• 它表示每页的空闲空间，有助于理解页的利用率

• 平均页密度（满）：每页的实际填充密度。页密度越高，表明页的空间利用率越高

3. 修复和优化

一般只能重建索引

一、对于严重碎片化的索引，通常使用重建操作：

ALTER INDEX IX_IndexName ON [SchemaName].[TableName] REBUILD;

• 作用：完全重建索引，重新组织数据页，使其连续
• 适用情况：当碎片度高于 30% 时

二、对于轻微碎片化的索引，使用重组操作：

ALTER INDEX IX_IndexName ON [SchemaName].[TableName] REORGANIZE;

• 作用：重新组织索引数据页，减少碎片，保持索引结构的稳定性
• 适用情况：当碎片度在 10% 到 30% 之间时

三、定期维护：

• 计划维护任务：定期运行重建和重组操作，以维护索引性能
• 监控性能：使用性能监控工具和视图（如 sys.dm_db_index_physical_stats）来监控索引状态