SQL Server 性能优化之索引碎片

小渣渣

索引碎片概述

什么是索引碎片，为什么我应该关注它：
当索引包含的页中，索引中的逻辑排序（基于索引中的键值）与索引页中的物理排序不匹配时，就存在碎片。
无论何时对基础数据执行插入、更新或删除操作，数据库引擎 都会自动修改索引。 例如，在表中添加行可能会导致拆分行存储索引中的现有页，以腾出空间来插入新键值。 随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。 当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。
大量碎片式索引可能会降低查询性能，因为需要额外 I/O 来查找索引指向的数据。 较多的 I/O 会导致应用程序响应缓慢，特别是在涉及扫描操作时。
检查数据库索引碎片

命令：

登录可见。

碎片整理有两种方式：重新组织索引和重新创建索引，重建索引是指在一个事务中，删除旧的索引，并重建新的索引，这种方式会回收原有索引的硬盘空间，并分配新的存储空间，以创建索引结构。重组索引是指不分配新的存储空间，在原有的空间基础上，重新组织索引结构的叶子节点，使数据页的逻辑顺序和物理顺序保持一致，并释放索引中多余的空间，这就是说，重组索引是为了减少叶子节点的外部碎片。

使用函数 sys.dm_db_index_physical_stats 检测碎片的程度，字段 avg_fragmentation_in_percent   返回的逻辑碎片的百分比，一般情况下，微软推荐以30%为阈值：

avg_fragmentation_in_percent >5% and <=30%： 重组索引（ALTER INDEX REORGANIZE）；
avg_fragmentation_in_percent >30%： 重建索引（ALTER INDEX REBUILD）；

avg_fragmentation_in_percent：索引碎片百分比，如果碎片小于10%~20%，碎片不太可能会成为问题，如果索引碎片在20%~40%，碎片可能成为问题，但是可以通过索引重组来消除索引解决，大规模的碎片(当碎片大于40%)，可能要求索引重建。


查询指定表的所有索引的碎片信息

登录可见。

表重建索引

登录可见。

重建整个数据库所有表的索引

登录可见。

参考资料：https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

zgy0209zgy

好东西，