SQL Server2005索引碎片分析与解决方法.doc

SQL Server2005索引碎片分析和解决方法 　毫无疑问,给表添加索引是有好处的,你要做的大部分工作就是维护索引,在数据更改期间索引可能产生碎片,所以一些维护是必要的。碎片可能是你查询产生性能问题的来源。 　　怎样确定索引是否有碎片? 　　SQLServer提供了一个数据库命令:DBCC SHOWCONTIG,来确定一个指定的表或索引是否有碎片。下面举一个例子: 　　对t_exam 表执行DBCC SHOWCONTIG,结果如下: 　　- 扫描页数.....................................: 20229 　　- 扫描扩展盘区数...............................: 2543 　　- 扩展盘区开关数...............................: 15328 　　- 每个扩展盘区上的平均页数.....................: 8.0 　 - 扫描密度〔最佳值:实际值〕....................: 16.50%〔2529:15329〕 【如果小于 100,则存在碎片。16.50%说明有很多碎片】 - 逻辑扫描碎片.................................: 46.23% 【如果为0是最好)】 　　- 扩展盘区扫描碎片.............................: 45.10% 　　- 每页上的平均可用字节数.......................: 3240.1 　 - 平均页密度(完整)...........................: 59.97% 【如果为100%是最好】 　　 以上结果显示:逻辑扫描碎片和扩展盘区扫描碎片都非常大,需要对索引碎片进行处理。 　　DBCC DBREINDEX 和DBCC INDEXDEFRAG命令常用来整理索引碎片。 　　这里需要注意的是,非常低的碎片级别(小于5%)不应通过这些命令来解决,因为删除如此少量的碎片所获得的收益始终远低于重新组织或重新生成索引的开销。 1 、DBCC DBREINDEX 　　DBCC DBREINDEX用于在指定的表上物理地重建一个或多个索引。DBCC DBREINDEX是离线操作方式。当该操作运行时,涉及到的表就无法被用户访问。 　　DBCC DBREINDEX动态地重建索引。没有必要知道参与重建的表结构到底如何,是否用主键或者唯一性约束等信息;重建的时候会自动管理的。DBCC DBREINDEX完全重建索引,就是此过程中将删除碎片,通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间,并在连续页中对索引行重新排序(根据需要分配新页)。这样可以减少获取所请求数据所需的页读取数,从而提高磁盘性能。从内部运行看,DBCC DBREINDEX和手工用T-SQL语句来运行删除然后重新创建索引十分相似。 　　下面两点是DBCC DBREINDEX比DBCC INDEXDEFRAG优越的地方: 　　DBCC DBREINDEX在重建索引过程中,自动重建统计;这将显著提高工作性能。 　　DBCC DBREINDEX可以运行在多处理器环境下,利用多处理器的优势,当重建较大和碎片厉害的索引时,速度可以十分快。 DBCC DBREINDEX的所有工作是一个单一的,原子事务。必须完成创建新的索引并替换旧索引,然后旧索引页被释放。完成重建需要数据文件中有足够的空余空间。如果空余空间不够,DBCC DBREINDEX要么无法重建索引,要么会产生大于0的逻辑碎片。所需空余空间视情况而定,取决于事务中要创建的索引数目。 2、 DBCC INDEXDEFRAG 　　DBCC INDEXDEFRAG用于对指定的索引进行重建。和DBCC DBREINDEX类似,也不需顾及表的基础结构;不过,DBCC INDEXDEFRAG无法用一个语句对所有的索引进行重建。对于每个希望进行碎片整理的索引,都必须运行一次DBCC INDEXDEFRAG。 　　无论是DBCC DBREINDEX还是DBCC INDEXDEFRAG,都可以有效地整理索引碎片,并将页密度恢复到初始填充因子规定的页密度附近。基于这些结果,下面需要决定什么时候应用哪种整理方式。 　　如果允许有一段时间进行离线索引重建,DBCC DBREINDEX一般来说比DBCC INDEXDEFRAG要快。DBCC DBREINDEX可以充分利用多处理器系统的平行性能。DBCC INDEXDEFRAG用于对生产环境干扰不大,对工作性能影响不大的场合。测试显示,即使同