整理索引的实验

整理索引的实验

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

首先介绍一下索引碎片整理和索引重建的区别：
      与 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