sql server 索引阐述系列七 索引填充因子与碎片

原文: sql server 索引阐述系列七 索引填充因子与碎片

一.概述

　　　　索引填充因子作用：提供填充因子选项是为了优化索引数据存储和性能。 当创建或重新生成索引时，填充因子的值可确定每个叶级页上要填充数据的空间百分比，以便在每一页上保留一些剩余存储空间作为以后扩展索引的可用空间，例如：指定填充因子的值为 80 表示每个叶级页上将有 20% 的空间保留为空，以便随着向基础表中添加数据而为扩展索引提供空间。

　　填充因子的值是 1 到 100 之间的百分比，服务器范围的默认值为 0，这表示将完全填充叶级页。

　1.1 页拆分现象

　　　根据数据的查询和修改的比例，正确选择填充因子值，可提供足够的空间，以便随着向基础表中添加数据而扩展索引，从而降低页拆分的可能性。如果向已满的索引页添加新行(新行位置根据键排序规则，可以是页中任意行位置)， 数据库引擎将把大约一半的行移到新页中，以便为该新行腾出空间。 这种重组称为页拆分。页拆分可为新记录腾出空间，但是执行页拆分可能需要花费一定的时间，此操作会消耗大量资源。 此外，它还可能造成碎片，从而导致 I/O 操作增加。 如果经常发生页拆分(可能过sys.dm_db_index_physical_stats 来查看页拆分情况)，可通过使用新的或现有的填充因子值来重新生成索引，从而重新分发数据。

　　填充值设置过低： 优点是 插入或修改时降低页的拆分次数。缺点是 会使索引需要更多的存储空间，并且会降低读取性能。

　　填充值设置过高： 优点是 如果每一个索引页数据都全部填满，此时select效率最高。缺点是 插入或修改时需要移动后面所有页，效率低。

二. 碎片与填充因子案例

 　　下面分析在生产环境下，对长时间一个表的ix_1索引进行分析。

-- 有一个PUB_Search_ResultVersions2表长期有增删改操作， 在很长一段时间运行后，查看碎片如下
dbcc SHOWCONTIG (PUB_Search_ResultVersions2,'ix_1')

　　

　　　　通过上图可以了解到平均页密度是29.74%，也就是内部碎片太多，现三个页的数据存储量才是正常一个页的存储量。扫描的页数是703页,涉及到了192个区。下面重新维护索引

--重建索引
ALTER INDEX ix_1 ON dbo.PUB_Search_ResultVersions2 REBUILD

　　

 　　　　通过上图可以看到，扫描页数只有了248页(原来是703页) 用了36区(原来是192个区)，现等于一页的实际数据是之前三页的总量， 查询将会减少了大量的I/O扫描。

　　如果频繁的增删改，最好设置填充因子，默认是0,也就是100%, 如果有新索引键排序后，挤入到一个已填满8060字节的页中时，就会发生页拆分，产生碎片,这里我使用图形界面来设置填充因子为85%(最好通过t-sql来设置，做运维自动维护)，再重建下索引使设置生效。

　　

　　下图可以看出平均页密度是85%，填充因子设置生效。可以在通过sys.dm_db_index_physical_stats重新查看该索引页使用数量。