整理索引的实验（原创）,该如何解决

2012-01-12

整理索引的实验（原创）索引对数据库的影响是显而易见的，这里就不多说了。本文是索引碎片整理和索引重建对数

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

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

（完）

[解决办法]
学习
[解决办法]

探讨
学习

[解决办法]
学习不错
[解决办法]
收藏一下回去学习
[解决办法]
收藏回家学习！
[解决办法]
收藏一下，多谢
[解决办法]
【个人建议】如果需要整理索引的表可以脱机的话，使用重建索引；不能脱机的话，采用碎片整理。
---
可以改变恢复模型　，使碎片整理将作为一系列短事务执行

前提是碎片有时是必须的，并不都是坏事，如果进行的是大量有序扫描，那碎片引起的性能下降才是致命的。
内部碎片的存在一定程序上减少页拆分的昂贵代价，并减少了外部碎片的形成，同时对insert、update提供了好的性能。
DBCC INDEXDEFRAG并不能移除所有碎片，只是对原先占有的分页进行重新排列来修正碎片，而重建重新分配一批全新的分页，这才会移除几乎所有碎片，但也在一个前提之上的，那就是库本身的可用空间不是支离破碎的。

不控制长期锁，是在B－树上先取意向共享，只有在分页实际操作时才会有排它锁的存在。
[解决办法]
收藏,学习
[解决办法]
学习一下
[解决办法]
值得学习
[解决办法]
xx
[解决办法]
不错，有心
[解决办法]
xue xi
[解决办法]
学习
[解决办法]
.
[解决办法]
up
[解决办法]
好东西阿。
[解决办法]

SQL code

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
[解决办法]
学习
[解决办法]
晕，在生产环境执行用了差不多半小时。结果：

对象 '[dbo].[ITSIGNALS]' 没有任何索引，或者您没有所需的权限。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITTASKS' 的任何名为 'IX1_STARTTASKS' 的索引。
消息 2552，级别 16，状态 1，第 20 行
无法重新组织表 "ITWORKITEMS" 的索引 "IX10_WORKITEMS_LDY" (分区 1)，因为已禁用页级锁定。
消息 2552，级别 16，状态 1，第 20 行
无法重新组织表 "ITWORKITEMS" 的索引 "IX9_WORKITEMS_LDY" (分区 1)，因为已禁用页级锁定。
消息 2552，级别 16，状态 1，第 20 行
无法重新组织表 "ITSTRINGS" 的索引 "IX3_STRINGS_LDY" (分区 1)，因为已禁用页级锁定。

消息 2552，级别 16，状态 1，第 20 行
无法重新组织表 "ITSTRINGS" 的索引 "IX4_STRINGS_LDY" (分区 1)，因为已禁用页级锁定。
消息 2552，级别 16，状态 1，第 20 行
无法重新组织表 "ITSTRINGS" 的索引 "IX5_STRINGS_LDY" (分区 1)，因为已禁用页级锁定。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITWORKITEMDATA' 的任何名为 'IX1_ATTACHMENTCTRL' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITAPPPROPS' 的任何名为 'IX1_LOG' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITSERVERS' 的任何名为 'IX1_SERVERATTRIBUTES' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITSERVERS' 的任何名为 'IX2_SERVERATTRIBUTES' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITSERVERS' 的任何名为 'IX3_SERVERATTRIBUTES' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITLOCKS' 的任何名为 'IX1_SUBMIT' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C0ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C10ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C11ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C12ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C13ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C14ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C1ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C2ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C3ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C4ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C5ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C6ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C7ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C8ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITVSPARAMS' 的任何名为 'C9ITUSERS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITUSERS' 的任何名为 'C0ITMAILALIAS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITUSERS' 的任何名为 'C1ITMAILALIAS' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITMAILALIAS' 的任何名为 'C0ITWORKSHARE' 的索引。
消息 7999，级别 16，状态 8，第 20 行
找不到表 'ITMAILALIAS' 的任何名为 'C1ITWORKSHARE' 的索引。
消息 2552，级别 16，状态 1，第 20 行
无法重新组织表 "ITPROCESSES" 的索引 "IX2_PROCESSES_LDY" (分区 1)，因为已禁用页级锁定。
消息 2552，级别 16，状态 1，第 20 行
无法重新组织表 "ITPROCESSES" 的索引 "IX3_PROCESSES_LDY" (分区 1)，因为已禁用页级锁定。

热点排行

SQL Server

整理索引的实验（原创）,该如何解决