用DBCC ShowContig看看SQL Server里索引碎片到底有多严重怎么处理

DBCC SHOWCONTIG是SQL Server 2008及更早版本中用于测量表或索引碎片程度的经典命令，虽然在新版本（SQL Server 2005及以后）中，更推荐使用动态管理视图（如sys.dm_db_index_physical_stats）来获取更精确的信息，但DBCC SHOWCONTIG因其直观的输出，至今仍被许多数据库管理员使用，理解它的输出是判断碎片严重性的关键。

如何使用DBCC SHOWCONTIG

基本语法很简单,你想查看某个特定表的碎片情况，比如一个叫Orders的表，你可以这样用：

DBCC SHOWCONTIG ('Orders')

这会显示该表的所有索引的碎片信息,如果你只想看特定的索引，比如一个叫IX_Orders_CustomerID的索引，那就需要指定表名和索引名：

DBCC SHOWCONTIG ('Orders', 'IX_Orders_CustomerID')

运行这个命令后,你会得到一长串结果，别看输出项那么多，对我们判断碎片是否严重最关键的几个指标是：扫描密度、逻辑扫描碎片和平均页密度。

解读关键指标：碎片到底有多严重？

1. 逻辑扫描碎片（Logical Scan Fragmentation）：这是最重要的指标，它表示索引的叶级页（也就是实际存储数据的那一层）在物理磁盘上排列的顺序，与索引逻辑顺序的不一致程度，简单说，就是你的数据页不是排好队一个接一个存放的，而是东一页西一页，跳来跳去。

   • 多少算严重？ 普遍认为，这个值低于10% 可以认为是健康的，基本不用管，如果在 10% 到 30% 之间，就表示有轻度到中度的碎片了，需要开始关注，如果超过30%，那就是严重碎片，会对查询性能产生明显的负面影响，必须处理。

2. 扫描密度（Scan Density）：这是一个百分比，表示“最佳计数”与“实际计数”的比率，理想情况下，如果数据页完全连续排列，扫描密度应该是100%，碎片越多，这个值越低。

   • 多少算严重？ 通常认为低于75% 就是一个需要警惕的信号了。

3. 平均页密度（Average Page Density）：这个指标和前面两个不太一样，它指的不是碎片，而是指每个数据页的“饱满程度”，比如一个8KB的页，平均页密度80%就意味着平均每页装了大概6.4KB的数据，还有1.6KB的空闲空间。

   • 这个值不是越高越好，也不是越低越好，太高（如95%以上）意味着页基本满了，下次插入新数据很可能导致“页拆分”（就像本子写满了要换新页，这个操作开销大），太低（如低于60%）则说明空间浪费严重，通常维持在合理的水平（如75%-85%）比较好。

当你运行完命令,首要关注的就是“逻辑扫描碎片”的百分比，如果它飙红了（比如超过了30%），你的数据库很可能正面临“我明明有索引，为什么查询还是慢？”的问题。

为什么碎片严重了是坏事？

你可以把没有碎片的索引想象成一本页码连续、内容按顺序排列的书，你想看第5章，接着看第6章，翻页非常顺畅，而严重碎片的索引就像一本被撕开后又胡乱装订的书，第5章后面可能是第80章，然后下一页又跳回第12章，数据库引擎为了读取一段连续的数据，磁头就不得不在磁盘上跳来跳去地寻找这些零散的页，这会产生大量的随机I/O，相比于顺序读取，随机I/O的速度要慢好几个数量级，这就直接导致了查询性能的急剧下降。

如何处理严重的索引碎片？

发现了严重碎片,主要有两种处理方法：重建（REBUILD）和重组（REORGANIZE）。

1. 索引重建（ALTER INDEX ... REBUILD）

   • 它做什么：这相当于把旧索引彻底扔掉，从头到尾重新创建一个全新的索引，重建过程中会移除碎片，回收磁盘空间，并且会使用FILLFACTOR（填充因子）指定的密度来重新填充页，让数据页恢复“饱满”且“连续”的状态。
   • 优点：效果最彻底，能最大程度地减少碎片，在重建过程中，索引的统计信息也会被更新，这是一举两得。
   • 缺点：开销非常大，它需要大量的临时磁盘空间，并且在操作期间会锁定整个索引（在Enterprise版本中可以在线操作，不影响查询，但其他版本会阻塞），不适合在业务高峰期进行。
   • 何时使用：当逻辑扫描碎片非常严重（gt;30%） 时，或者需要彻底清理空间时，应该选择重建。

2. 索引重组（ALTER INDEX ... REORGANIZE）

   

   • 它做什么：重组更像是一种“温和的整理”，它不会重建整个结构，而是通过物理地重新排列索引的叶级页，使页的物理顺序与逻辑顺序相匹配，这个过程是原地进行的，不需要额外空间。
   • 优点：开销小，是一个在线操作（不会长期锁定对象，允许并发查询和修改），对系统影响较小。
   • 缺点：整理碎片的效果不如重建彻底，它不会更新统计信息，也不会像重建那样优化页的密度。
   • 何时使用：当碎片程度不太高（比如在10%到30%之间） 时，或者系统需要7x24小时运行，无法承受重建带来的长时间锁定时，可以选择重组。

具体的操作命令示例：

• 重建 Orders 表上的 IX_Orders_CustomerID 索引：

  ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD;

• 重组 Orders 表上的 IX_Orders_CustomerID 索引：

  ALTER INDEX IX_Orders_CustomerID ON Orders REORGANIZE;

• 重建整个表的所有索引：

  ALTER INDEX ALL ON Orders REBUILD;

重要提醒和最佳实践

• 新版本工具：再次强调，对于SQL Server 2005及以后版本，请优先使用sys.dm_db_index_physical_stats这个动态管理函数来获取碎片信息，它更强大、更灵活，DBCC SHOWCONTIG未来可能会被弃用。
• 自动化维护：手动检查和处理碎片不是长久之计，对于生产数据库，应该建立定期的数据库维护计划，使用SQL Server代理作业，在业务低峰期（如深夜）自动执行索引重建或重组任务。
• 权衡利弊：不是所有碎片都需要立即处理，对于几乎只做插入、很少做更新删除的表，碎片可能增长很慢，而对于一些非常小的表，即使碎片百分比高，但其绝对页数很少，整理带来的性能提升可能微乎其微，不值得花费资源。
• 关注系统数据库：别忘了tempdb以外的系统数据库（尤其是msdb），它们也会产生碎片，定期维护它们同样重要。

DBCC SHOWCONTIG是一个快速诊断索引碎片问题的听诊器，通过关注“逻辑扫描碎片”这个核心指标，你可以判断问题的严重性，然后根据碎片的程度和系统的繁忙情况，明智地选择“重建”（大扫除）或“重组”（小整理）来恢复数据库的查询性能。