索引碎片分为外部碎片和内部碎片。
外部碎片:
又称平均碎片百分比,或逻辑碎片。是指在分页的逻辑顺序与物理顺序不匹配或者索引拥有的扩展不
连续时产生。包括以下两种:
逻辑碎片:这是索引的叶级页中出错页所占的百分比。出错页是指在IAM 中所指示的下一页不同于由叶级页中的下一页指针所指向的页。
区碎片(有的书翻译成:扩展碎片):这是堆的叶级页中出错区所占的百分比。出错区是指:包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。
这种碎片对索引的有序扫描操作具有非常显著的影响。它会对那些不依赖于索引链接的列表的操作(例如:查找操作,lookup操作,无序扫描)不产生影响。
对应sys.dm_db_index_physical_stats的列avg_fragmentation_in_percent 。因此为了获得最佳性能,avg_fragmentation_in_percent 的值应尽可能接近零。
但是,从0 到10%范围内的值都可以接受。所有减少碎片的方法(例如重新生成、重新组织或重新创建)都可用于降低这些值。
内部碎片:又称为平均页密度。是指索引正在占有超过它实际所需的空间大小。它具有两面型:低百分比会对读取数据的查询产生负面影响,会涉及更多读取操作,因为如果页被填充满的话,只需读取更少的页;另一方面,如果如果在创建索引时设置一个较低的填充因子,就可以避免当插入更多记录而不必进行页拆分。
微软建议索引碎片率在5%到30%之间,做索引重组;碎片率超过30%,做索引重建工作。
解决方法
ALTER INDEX indexname ON tablename REBUILD
ALTER INDEX indexname ON tablename REORGANIZE
-
- 重建索引会消耗大量的系统I/O读写资源。
- 重建索引会导致查询进程的死锁或者锁等待,尤其是非企业版SQL Server(企业版可以使用ONLINE选项来最大限度规避这个问题)。
- 重建索引会导致数据库日志文件暴涨,而因此会给Database Mirroring、Log Shipping和Backup带来压力。
- 因为Rebuild Indexes是一个IO密集型的操作,所以会非常消耗IO,所以,请选择业务低谷期进行索引碎片重整的操作。