索引维护（原作者草根）

 

索引对数据库的影响是显而易见的，这里就不多说了。本文是索引碎片整理和索引重建对数据查询和空间占用的一个实验，和大家一起学习。请高手多多指点。

首先介绍一下索引碎片整理和索引重建的区别：
  与 DBCC DBREINDEX 或任何常规索引生成不同，DBCC INDEXDEFRAG 是一个联机操作，因此它不控制长期锁，该锁会阻塞查询或更新的运行。根据碎片的量，DBCC INDEXDEFRAG 可以比运行 DBCC DBREINDEX 快得多，因为对碎片相对较少的索引进行碎片整理会比生成新索引快得多。另一个优点是，与 DBREINDEX 不同，使用 DBCC INDEXDEFRAG 时索引始终可用。大量的碎片可以导致 DBCC INDEXDEFRAG 运行的时间比 DBCC DBREINDEX 长得多，这一点可能会也可能不会胜过该命令的联机功能所带来的优势。如果两个索引在磁盘上交叉存取事务，DBCC INDEXDEFRAG 将没有作用，原因是 INDEXDEFRAG 打乱了已有的页。若要改善页的聚集，请重建索引。

【个人建议】如果需要整理索引的表可以脱机的话，使用重建索引；不能脱机的话，采用碎片整理。

【实验过程】：
--.查看表的索引（使用sp_helpindex）
指令：sp_helpindex 'tablename'

结果：
id clustered located on PRIMARY src_addr
ix_createtime nonclustered located on PRIMARY createtime 

【结果】该表有一个聚集索引‘id’和一个非聚集索引‘ix_createtime’

--查看数据和索引的碎片信息（使用DBCC SHOWCONTIG）
指令：DBCC SHOWCONTIG (tablename)

结果：
DBCC SHOWCONTIG 正在扫描 'tablename' 表...
表: 'tablename'（1764201335）；索引 ID: 1，数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数.....................................: 50084
- 扫描扩展盘区数...............................: 6308
- 扩展盘区开关数...............................: 50072
- 每个扩展盘区上的平均页数.....................: 7.9
- 扫描密度［最佳值:实际值］....................: 12.50%［6261:50073］ --小于 100，则存在碎片。12.50%说明有很多碎片
- 逻辑扫描碎片.................................: 50.40% --0是最好
- 扩展盘区扫描碎片.............................: 52.31%
- 每页上的平均可用字节数.......................: 2728.0
- 平均页密度（完整）...........................: 66.30% --100%是最好


--整理索引碎片（使用DBCC INDEXDEFRAG）
DBCC INDEXDEFRAG (0,tablename,id)
DBCC INDEXDEFRAG (0,tablename,ix_createtime)

--重建索引（使用DBCC DBREINDEX）
DBCC DBREINDEX(tablename,'',0)

--重建索引后查看碎片
DBCC SHOWCONTIG (tablename)
DBCC SHOWCONTIG 正在扫描 'tablename' 表...
表: 'tablename'（1764201335）；索引 ID: 1，数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数.....................................: 36482
- 扫描扩展盘区数...............................: 4578
- 扩展盘区开关数...............................: 4577
- 每个扩展盘区上的平均页数.....................: 8.0
- 扫描密度［最佳值:实际值］....................: 99.63%［4561:4578］
- 逻辑扫描碎片.................................: 0.00%
- 扩展盘区扫描碎片.............................: 19.94%
- 每页上的平均可用字节数.......................: 726.6
- 平均页密度（完整）...........................: 91.02%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

--碎片整理对表空间的影响（使用sp_spaceused）
sp_spaceused tablename
tablename 1614319 485360 KB 400672 KB 83216 KB 1472 KB
tablename 1614319 485392 KB 400672 KB 59008 KB 25712 KB --整理非聚集索引的碎片后
tablename 1614319 485424 KB 299512 KB 59008 KB 126904 KB --整理聚集索引的碎片后
tablename 1614938 348736 KB 291856 KB 56832 KB 48 KB --重建索引后

【结论】1.非聚集索引占用的是索引页空间，聚集索引占用的是数据页空间，重建索引后释放出空间。2.碎片整理可以清理出比较多的空间。3.重建索引后效率提升相当明显。


------下边是整理某个数据库的报有索引碎片的脚本（该脚本来自网络）------
set nocount on  
  declare @s_table varchar(50),@s_index varchar(50)  
  create table #test(  
  index_name varchar(100),  
  index_description varchar(500),  
  index_keys varchar(500)  
  )  
  insert into #test  
  exec sp_msforeachtable 'sp_helpindex ''?'''  
  declare c_index cursor for  
  select index_name from #test  
  open c_index  
  fetch next from c_index into @s_index  
  while(@@fetch_status = 0)  
  begin  
  select @s_table = b.name  
  from sysobjects a,sysobjects b  
  where a.id = object_id(@s_index) and  
  a.parent_obj = b.id  
  dbcc indexdefrag(0,@s_table,@s_index) WITH NO_INFOMSGS  
  fetch next from c_index into @s_index  
  end  
  close c_index  
  deallocate c_index