唯一官网:索引填充因子与碎片,索引碎片的查

一.概述

    索引填充因子作用:提供填充因子选项是为了优化索引数据存储和性能。 当创建或重新生成索引时,填充因子的值可确定每个叶级页上要填充数据的空间百分比,以便在每一页上保留一些剩余存储空间作为以后扩展索引的可用空间,例如:指定填充因子的值为 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')

  唯一官网 1

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

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

  唯一官网 2

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

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

  唯一官网 3

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

唯一官网 4

  • 内部碎片(或说叶级填充率):反映数据叶级的空间占用率或空闲率
  • 外部碎片:由于sqlserver以连续的8个page作为一个数据库块(区)extent作为读取单位,故此由于物理存储上的区和逻辑上不一致(不连续)而造成io读取切换

 

  • 逻辑碎片:这是索引的叶级页中出错页所占的百分比。对于出错页,分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页
  • 区碎片:这是堆的叶级页中出错区所占的百分比。出错区是指:包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。(微软真不会解释概念:(

 

查询碎片情况:

  1.   dbcc showcontig:四部分对象名,【索引名】|【索引id】
  2.   dbcc showcontig:当前库对象id,【索引名】|【索引id】    
  3.   sys.dm_db_index_physical_stats:数据库id,对象id,索引id,分区id,扫描模式
      • 五个参数,基本上,【0(特殊的,index可以为0,故该处为-1)】|【null】|【default】 意义是一样的

 

基本指标:

  1. 扫描密度(%)[最佳计数:实际计数]:这是“最佳计数”与“实际计数”的比率。如果所有内容都是连续的,则该值为 100;如果该值小于 100,则存在一些碎片。“最佳计数”是指在一切都连续链接的情况下,区更改的理想数目。“实际计数”是指区更改的实际次数。
  2. 逻辑扫描碎片(%):扫描索引的叶级页时返回的出错页的百分比。此数与堆无关。对于出错页,分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。
  3. 区扫描碎片(%):扫描索引的叶级页时出错区所占的百分比。此数与堆无关。对于出错区,包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。注意: 如果索引跨越多个文件,则此数字无意义。
  4. avg_page_space_used_in_percent:平均page空间使用率。相关的概念:页拆分、页填充率
  5. avg_fragment_size_in_pages:平均多少个page就有一个碎片,该值 越大越好
  6. avg_fragmentation_in_percent:碎片率,不解释。该值越小越好,和avg_fragment_size_in_pages 反比!
  7. page_count:扫描的总page数
  8. record_count:扫描的总记录数。注意:是相对于当前的扫描来说的记录数,不一定是你所认为的 用户表的一行数据
  9. forwarded_record_count:页拆分的记录数目

本文由澳门新萄京官方网站发布于信息数据库,转载请注明出处:唯一官网:索引填充因子与碎片,索引碎片的查

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。