SQLServer2005索引碎片分析和解决方法

1、SQLServer2005 索引碎片分析和解决方法毫无疑问，给表添加索引是有好处的，你要做的大部分工作就是维护索引，在数据更改期间索引可能产生碎片，所以一些维护是必要的。碎片可能是你查询产生性能问题的来源。怎样确定索引是否有碎片？SQLServer 提供了一个数据库命令：DBCCSHOWCONTIG,来确定一个指定的表或索引是否有碎片。下面举一个例子：Xt_exam表执行 DBCCSHOWCONTIG，结果如下：-扫描页数:20229- 扫描扩展盘区数:2543- 扩展盘区开关数:15328- 每个扩展盘区上的平均页数:8.0- 扫描密度最佳值：实际值：16.50%2529:15329【如果小

2、于 100,则存在碎片。16.50%说明有很多碎片】- 逻辑扫描碎片:46.23%【如果为 0 是最好）】- 扩展盘区扫描碎片:45.10%- 每页上的平均可用字节数:3240.1- 平均页密度（完整）:59.97%【如果为 100%是最好】以上结果显示：逻辑扫描碎片和扩展盘区扫描碎片都非常大，需要对索引碎片进行处理。DBCCDBREINDEX 和 DBCCINDEXDEFRAG 命令常用来整理索引碎片。这里需要注意的是，非常低的碎片级别（小于 5%）不应通过这些命令来解决，因为删除如此少量的碎片所获得的收益始终远低于重新组织或重新生成索引的开销。1、DBCCDBREINDEXDBCCDBRE

3、INDEX 用于在指定的表上物理地重建一个或多个索引。DBCCDBREINDEX 是离线操作方式。当该操作运行时，涉及到的表就无法被用户访问。DBCCDBREINDEX 动态地重建索引。没有必要知道参与重建的表结构到底如何，是否用主键或者唯一性约束等信息；重建的时候会自动管理的。DBCCDBREINDEX 完全重建索引，就是此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。从内部运行看,DBCCDBREINDEX和手工用 T-SQL 语句来运行删除然后重新创建

4、索引十分相似。下面两点是 DBCCDBREINDEX 比 DBCCINDEXDEFRAG 优越的地方：DBCCDBREINDEX 在重建索引过程中，自动重建统计；这将显著提高工作性能。DBCCDBREINDEX 可以运行在多处理器环境下，利用多处理器的优势，当重建较大和碎片厉害的索引时速度可以十分快。DBCCDBREINDEX 的所有工作是一个单一的，原子事务。必须完成创建新的索引并替换旧索引，然后旧索引页被释放。完成重建需要数据文件中有足够的空余空间。如果空余空间不够,DBCCDBREINDEX 要么无法重建索引，要么会产生大于 0 的逻辑碎片。所需空余空间视情况而定，取决于事务中要创建的索

5、引数目。2 、DBCCINDEXDEFRAGDBCCINDEXDEFRAG 用于对指定的索引进行重建。和 DBCCDBREINDEX 类似,也不需顾及表的基础结构;不过,DBCCINDEXDEFRAG 无法用一个语句对所有的索引进行重建。对于每个希望进行碎片整理的索弓 I,者 B 必须运行一次 DBCCINDEXDEFRAG。无论是 DBCCDBREINDEX 还是 DBCCINDEXDEFRAG，都可以有效地整理索引碎片，并将页密度恢复到初始填充因子规定的页密度附近。基于这些结果，下面需要决定什么时候应用哪种整理方式。如果允 许有一段时间 进行离线索 引重建，DBCCDBREINDEX 一般

6、来 说比 DBCCINDEXDEFRAG 要快。DBCCDBREINDEX 可以充分利用多处理器系统的平行性能。DBCCINDEXDEFRAG 用于对生产环境干扰不大，对工作性能影响不大的场合。测试显示，即使同时几个 DBCCINDEXDEFRAG 并行工作，对性能下降的影响也从来不会超出 10%。但是，这也同样使得 DBCCINDEXDEFRAG 针对较大的索引整理时，需要很长的时间才能完成。而且，工作时间的长短还依赖于当时在服务器上运行的访问工作。3 结论对于不同的工作类型，索引碎片整理具有十分不同的影响。某些应用可以从碎片整理中获取很大的性能提升。理解应用特征，系统性能和 SQLServ

7、er 提供的碎片统计信息，是正确决定何时进行碎片整理的关键。SQLServer 提供一些命令来完成索引碎片整理。而在 SQLServer2005 中，DBCCDBREINDEX 和 DBCCINDEXDEFRAG 已经被作为维护计划中的两个步骤： 重新生成索引和重新组织索引， 方便了数据库管理的数据库维护工作。本文可以帮助我们来决定何时以及如何整理索引碎片，从而使性能得到最大的改善。扫描页数表或系引的贝数。扫描扩展盘区数表或索引中的犷展盘区数。扩展盘区开关数遍历索引或表的贝时，C C 电句从一午力展盘区移动到其它扩展盘区的次数。平均扩展盘区上的平均页数页链中每个扩展盘区的页数。扫描密度最佳值：

8、实际值最佳值是指在一切都连续地链接的情况卜，扩展盘区更改的理想数目。实际值是指扩展盘区更改的实际次数。如果一切都连续，则扫描密度数为 100;100;如果小于 100,100,则存在碎片。扫描密度为百分比值。逻辑扫描碎片对索引的叶级贞扫描所返回的无序贞的百分比。 该数与堆集和文本索引尤夫。 无序贝是指在 IAMIAM 中所指示的下一直不 1 1 司十由叶级页中的下一页指针所指向的页。扩展盘区扫描碎片无序扩展盘区在扫描索引叶级页中所占的百分比。 该数与堆集无关。无序扩展盘区是指：含有索引的当前页的扩展盘区不是物理上的含有索引的前一页的扩展盘区后的下一个扩展盘区。平均每页上的平均可用字节数所扫描的

9、页上的平均可用字节数。数字越大，页的填满程度越低。数字越小越好。该数还受行大小影响：行大小越人，数字就越人。平均页密度（完整）平均页密度（为百分比）。该俏考虑行大小，所以它是页的填满程度的更准确表示。百分比越大越好。DBCCSHOWCONTIG 正在扫描MSmergetombstone表.表：MSmerge_tombstone2132202646ID:1ID:6已执行 TABLE 级别的扫描。- 扫描页数:1061343- 扫描扩展盘区数:139332- 扩展盘区开关数:1060915- 每个扩展盘区上的平均页数：7.6- 扫描密度最佳值：实际值 I:12.51%132668:10609161

10、- 逻辑扫描碎片：50.28%- 扩展盘区扫描碎片:51.61%- 每页上的平均可用字节数:7640.1- 平均页密度（完整）：5.61%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。DBCCSHOWCONTIG 正在扫描MSmergecontents表.表：MSmerge_contents（719055）;索引 ID:1,数据库 ID:6已执行 TABLE 级别的扫描。- 扫描页数:511344- 扫描扩展盘区数:79309- 扩展盘区开关数:511113- 每个扩展盘区上的平均页数:6.4- 扫描密度最佳值：实际值 I:12.51%63918:5111141- 逻

11、辑扫描碎片:50.02%- 扩展盘区扫描碎片:64.25%- 每页上的平均可用字节数:5880.7- 平均页密度（完整）：27.35%DBCCDBCC 输出了错误信息，请与系统管理员联系。DBCCSHOWCONTIG 正在扫苗MSmergegenhistory表.表：MSmerge_genhistory（16719112）;索引 ID:1,数据库 ID:6已执行 TABLE 级别的扫描。- 扫描页数：74- 扫描扩展盘区数:15- 扩展盘区开关数:20- 每个扩展盘区上的平均页数:4.9- 扫描密度最佳侑：实际侑:47.62%1。21- 逻辑扫描碎片：9.46%- 扩展盘区扫描碎片:93.33%- 每页上的平均可用字节数:1900.3- 平均页密度（完整）：76.52%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。DBCCSHOWCONTIG 正在扫描I_KANBAN表.表:衣 KANBAN(286624064);索引 ID:1,数据库 ID:6已执行 TABLE 级别的扫描。- 扫描页数:24763-