合 MSSQL索引碎片和usp_AdaptiveIndexDefrag的使用

简介

在SQL Server中，索引碎片指的是索引存储结构中的空隙和未被充分利用的空间，这可能会导致数据库性能下降。索引是用于加速数据库查询操作的数据结构，它们可以按照特定的列对数据库表进行排序和组织，从而提高检索效率。当数据库中的数据发生变化（例如插入、更新、删除操作）时，索引可能会出现碎片化。

• 当索引包含的页中，索引中的逻辑排序（基于索引中的键值）与索引页中的物理排序不匹配时，就存在碎片。

• 无论何时对基础数据执行插入、更新或删除操作，数据库引擎 都会自动修改索引。例如，在表中添加行可能会导致拆分行存储索引中的现有页，以腾出空间来插入新键值。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。

• 大量碎片式索引可能会降低查询性能，因为需要额外 I/O 来查找索引指向的数据。较多的 I/O 会导致应用程序响应缓慢，特别是在涉及扫描操作时。

索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构，用来帮助查找和整理数据，它的重要性体现在能够使数据库引擎快速返回查询结果。当对索引所在的基础数据表进行增删改时，若存储的数据进行了不适当的跨页（SQL Server中存储的最小单位是页，页是不可再分的），就会导致索引碎片的产生。随着索引碎片的不断增多，查询响应时间就会变慢，性能也因此而下降。要解决这个问题，可以通过重新生成或重新组织索引来解决。

索引碎片分类

一般我们可以把碎片分为外部碎片和内部碎片。外部碎片，可分为逻辑碎片（索引）和区碎片（堆）。内部碎片主要有页填充密度衡量。

• 逻辑碎片，这是索引的叶级页中出错页所占的百分比。对于出错页，分配给索引的下一个物理页不是当前叶级页中的下一页 指针所指向的页。 这种碎片发生在索引的逻辑结构上，即索引中的页顺序与相应表中的实际行顺序不匹配。这可能是由于数据的插入、更新和删除操作导致的。逻辑碎片可以导致查询性能下降，因为数据库引擎需要更多的I/O操作来读取和处理这些碎片。

• 物理碎片： 这种碎片发生在索引所在的物理存储结构上，即索引的页不是顺序存储的，而是分散在磁盘上。物理碎片通常是由于数据页的分配和释放操作引起的。物理碎片同样会导致磁盘I/O的增加，从而影响性能。

• 区碎片，这是堆的叶级页中出错区所占的百分比。出错区是指：包含堆当前页的区在物理上不是包含前一页的区后的下一个区。

• 内部碎片，指示索引页的平均填充率（以百分比表示）。100% 表示索引页完全填充，没有碎片。不过这种理想状态很难存在。

内部碎片

先说说内部碎片，右键索引属性下可以查看其页填充度。

该索引的页填充度为 97.90%，说明还有一部分空间未使用，有点浪费了。当经过多次DML操作后（如下图），页内有较多的空间空闲出来了，而实际存储的数据只是很少一部分。所以当我们查询部分数据时，需要查询更多的数据页才能获取完整数据，这样增加了IO。

外部碎片

那外部碎片又是什么样子呢？什么样的数据页是物理不连续呢？

我们创建一个数据文件，数据文件将划分成数据页来存储数据。假设我们预先给数据文件分配一定的空间，数据页分配理应是按顺序在磁盘文件上分配空间（如下图）。当新的数据插入表后，页内空间不足以填充新的行数据，因此产生了页拆分。由于索引键的顺序性，逻辑上要保证其页是顺序的，但是物理上已经出现了跨多个页的情况。这种就是外部碎片。同理，堆表的区碎片也类似。

如何查看索引碎片，可通过系统函数sys.dm_db_index_physical_stats 查看。

既然索引碎片产生了更多的空间和磁盘IO，定期清理仍是有必要的。参考以上的字段 avg_fragmentation_in_percent ，进行索引重组或者索引重建。

查看碎片也可以通过SSMS工具来查看：

碎片总计要越小越好，而页填充度要越高越好！！！

重新组织索引（重组索引）使用的系统资源最少，并且是联机操作。也就是说，不保留长期阻塞性表锁，且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续进行。数据库引擎 通过以物理方式重新排序叶级别页，以匹配叶节点逻辑顺序（从左到右），从而对表和视图中的聚集索引和非聚集索引的叶级别进行碎片整理。重新组织还会根据索引的填充因子值压缩索引页。

重新生成索引（重建索引）将会删除并重新创建索引。重新生成操作可以联机或脱机执行，具体取决于索引类型和 数据库引擎 版本。对于行存储索引，重新生成操作会：删除碎片；根据指定或现有的填充因子设置来压缩页，从而回收磁盘空间；还会在连续页中重新排序索引行。如果指定 ALL，将删除表中的所有索引，然后在一个事务中重新生成。不必预先删除外键约束。重新生成具有 128 个区或更多区的索引时，数据库引擎延迟实际的页释放及其关联的锁，直到事务提交。

不过也有注意以下事项：

• 如果删除并重建了聚集索引，那么非聚集索引也将重建。
• 对于堆表的产生的碎片，可以创建并删除聚集索引，必要可保留聚集索引。
• 当索引重新生成发生时，物理介质必须有足够的空间来存储索引的两个副本。

以下场景强制自动在表上重新生成所有行存储非聚集索引：

• 在表上创建聚集索引
• 删除聚集索引，从而使表存储为堆
• 更改聚集键以包括或排除列

以下场景不需要在表上自动重新生成所有行存储非聚集索引：

• 重新生成唯一聚集索引
• 重新生成非唯一聚集索引
• 更改索引架构，例如将分区方案应用于聚集索引或将聚集索引移到其他文件组

如何知道是否发生了索引碎片？

方法1 查询sys.dm_db_index_physical_stats函数

相关SQL查看最后的总结部分。

方法2：使用SHOWCONTIG

在SQL Server数据库中，可以通过DBCC SHOWCONTIG WITH ALL_INDEXES或DBCC SHOWCONTIG(表ID或者表名) WITH ALL_INDEXES来检查索引碎片情况。

通过对逻辑扫描碎片（过高）、平均页密度(满)（过低）的结果分析，判定是否需要进行索引处理，如下所示：

逻辑扫描碎片 ..................：97.83% 该百分比应该在0%到10%之间，高了则说明有外部碎片。

平均页密度(满) ..................：62.42% 该百分比应该尽可能靠近100%，低了则说明有外部碎片。

Page Scanned-扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。

Extents Scanned-扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。