合 MSSQL索引碎片和usp_AdaptiveIndexDefrag的使用
Tags: MSSQLSQL Server索引性能调优重建索引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
查看。
列 | 说明 |
---|---|
avg_fragmentation_in_percent | 堆的区碎片/逻辑碎片(索引中的无序页)的百分比 |
avg_page_space_used_in_percent | 平均页密度 |
既然索引碎片产生了更多的空间和磁盘IO,定期清理仍是有必要的。参考以上的字段 avg_fragmentation_in_percent
,进行索引重组或者索引重建。
avg_fragmentation_in_percent | 处理方法 |
---|---|
> 5% 且 < = 30% | ALTER INDEX REORGANIZE |
> 30% | ALTER INDEX REBUILD WITH (ONLINE = ON) |
查看碎片也可以通过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来检查索引碎片情况。
1 2 3 4 5 | -- 全库 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返回的数高,说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。