通过DBCC整理Sqlserver数据库表索引碎片

概述昨天检查了一张效率极慢的表，两年多没有维护，逻辑扫描碎片高达99.81%，于是开始对这个表进行重点跟踪和记录日志。今天用DBCC SHOWCONTIG WITH TABLERESULTS  命令检查了一下所有表的概况，然后参照着MSDN对字段的说明，发现问题比较严重。ScanDensity（这是“最佳计数”与“实际计数”的比率。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在 @H_301_2@

昨天检查了一张效率极慢的表，两年多没有维护，逻辑扫描碎片高达99.81%，于是开始对这个表进行重点跟踪和记录日志。今天用DBCC SHOWCONTIG WITH tableRESulTS  命令检查了一下所有表的概况，然后参照着MSDN对字段的说明，发现问题比较严重。ScanDensity（这是“最佳计数”与“实际计数”的比率。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片）有的甚至在16.6666666666667，其他扫描密度等指标也特别不理想。

检查：

随便贴出一个表的扫描结果：

--------------------------------------------------------------------------------------------------------------------------

DBCC SHOWCONTIG 正在扫描 'UserInfo' 表...
表: 'UserInfo' (1646628909)；索引 ID: 1，数据库 ID: 7
已执行 table 级别的扫描。
- 扫描页数................................: 367
- 扫描区数..............................: 48
- 区切换次数..............................: 355
- 每个区的平均页数........................: 7.6
- 扫描密度 [最佳计数:实际计数].......: 12.92% [46:356]
- 逻辑扫描碎片 ..................: 95.37%
- 区扫描碎片 ..................: 47.92%
- 每页的平均可用字节数.....................: 2996.8
- 平均页密度(满).....................: 62.98%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 --------------------------------------------------------------------------------------------------------------------------
解释如下（ 查看解释来源点此 ，MSDN解释太官方，还是用简单点的话来解释）：

扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。 

扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。 

扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。 

每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。 

扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。

逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。 

扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。 

每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。 

平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。

整理数据库碎片索引

看到如此不对劲，果断去MSDN查找相关资料，找到了MSDN有对数据库索引进行碎片整理的T-sql，执行了一下，发现效果还不错。

@H_502_160@ 使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 对数据库中的索引进行碎片整理：（以下示例将展示一种简单的方法，对数据库中碎片数量在声明的阈值之上的所有索引进行碎片整理。）@H_502_160@

@H_502_160@

/*Perform a 'USE <database name>' to select the database in which to run the script.*/-- Declare variablesSET NOCOUNT ON;DECLARE @tablename varchar(255);DECLARE @execstr   varchar(400);DECLARE @objectID  int;DECLARE @indexID   int;DECLARE @frag      decimal;DECLARE @maxfrag   decimal;-- DecIDe on the maximum fragmentation to allow for.SELECT @maxfrag = 30.0;-- Declare a cursor.DECLARE tables CURSOR FOR   SELECT table_SCHEMA + '.' + table_name   FROM informatION_SCHEMA.tableS   WHERE table_TYPE = 'BASE table';-- Create the table.CREATE table #fragList (   Objectname char(255),ObjectID int,Indexname char(255),IndexID int,Lvl int,CountPages int,CountRows int,MinRecSize int,MaxRecSize int,AvgRecSize int,ForRecCount int,Extents int,ExtentSwitches int,AvgFreeBytes int,AvgPageDensity int,ScanDensity decimal,BestCount int,ActualCount int,LogicalFrag decimal,ExtentFrag decimal);-- Open the cursor.OPEN tables;-- Loop through all the tables in the database.FETCH NEXT   FROM tables   INTO @tablename;WHILE @@FETCH_STATUS = 0BEGIN;-- Do the showcontig of all indexes of the table   INSERT INTO #fragList    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')       WITH FAST,tableRESulTS,ALL_INDEXES,NO_INFOMSGS');   FETCH NEXT      FROM tables      INTO @tablename;END;-- Close and deallocate the cursor.CLOSE tables;DEALLOCATE tables;-- Declare the cursor for the List of indexes to be defragged.DECLARE indexes CURSOR FOR   SELECT Objectname,ObjectID,IndexID,LogicalFrag   FROM #fragList   WHERE LogicalFrag >= @maxfrag      AND INDEXPROPERTY (ObjectID,Indexname,'IndexDepth') > 0;-- Open the cursor.OPEN indexes;-- Loop through the indexes.FETCH NEXT   FROM indexes   INTO @tablename,@objectID,@indexID,@frag;WHILE @@FETCH_STATUS = 0BEGIN;   PRINT 'Executing DBCC INDEXDEFRAG (0,' + RTRIM(@tablename) + ',' + RTRIM(@indexID) + ') - fragmentation currently '       + RTRIM(CONVERT(varchar(15),@frag)) + '%';   SELECT @execstr = 'DBCC INDEXDEFRAG (0,' + RTRIM(@objectID) + ',' + RTRIM(@indexID) + ')';   EXEC (@execstr);   FETCH NEXT      FROM indexes      INTO @tablename,@frag;END;-- Close and deallocate the cursor.CLOSE indexes;DEALLOCATE indexes;-- Delete the temporary table.DROP table #fragList;GO

@H_502_160@ 执行后会返回索引扫描数、移动数、删除数（Pages Scanned、Pages Moved、Pages Removed）。效果还是很明显的，然后再把扫描结果进行比对：

-----------------------------------------------------------------------------------------------------------------

DBCC SHOWCONTIG 正在扫描 'UserInfo' 表...
表: 'UserInfo' (1646628909)；索引 ID: 1，数据库 ID: 7
已执行 table 级别的扫描。
- 扫描页数................................: 237
- 扫描区数..............................: 31
- 区切换次数..............................: 30
- 每个区的平均页数........................: 7.6
- 扫描密度 [最佳计数:实际计数].......: 96.77% [30:31]
- 逻辑扫描碎片 ..................: 2.95%
- 区扫描碎片 ..................: 29.03%
- 每页的平均可用字节数.....................: 200.3
- 平均页密度(满).....................: 97.52%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系

------------------------------------------------------------------------------------------------------------

结果很有对比性，碎片大幅降低，每页的平均可用字节数大幅降低，扫描密度提高，平均页密度达到理想中的近饱和数值。看来DBCC的一些命令和MSDN的联机丛书还是很不错滴。虽然暂时降低了一些碎片指标，但只要有 *** 作肯定也会有碎片产生，通过一段的时间跟踪，才能对整体情况进行主观的判断。

相关链接：http://msdn.microsoft.com/zh-cn/library/ms188796.aspx

  http://msdn.microsoft.com/zh-cn/library/ms175008(v=sql.90).aspx

@H_301_2@ 总结

以上是内存溢出为你收集整理的通过DBCC整理Sqlserver数据库表索引碎片全部内容，希望文章能够帮你解决通过DBCC整理Sqlserver数据库表索引碎片所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。