索引碎片

--10:58 2013-01-08 索引碎片
内部索引碎片：索引中的数据没有填满索引页
外部索引碎片：索引中的索引页的逻辑顺序和物理顺序不一致
一、使用DBCC SHOWCONTIG检查索引碎片情况

DBCC SHOWCONTIG ('CheckFragmentation') WITH ALL_INDEXES

DBCC SHOWCONTIG返回结果如下

DBCC SHOWCONTIG 正在扫描 'CheckFragmentation' 表...
表: 'CheckFragmentation'（1067918926）；索引 ID: 0，数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数.....................................: 4377
- 扫描扩展盘区数...............................: 823
- 扩展盘区开关数...............................: 822
- 每个扩展盘区上的平均页数.....................: 5.3
- 扫描密度［最佳值:实际值］....................: 66.59%［548:823］
- 扩展盘区扫描碎片.............................: 82.99%
- 每页上的平均可用字节数.......................: 1147.4
- 平均页密度（完整）...........................: 85.82%
DBCC SHOWCONTIG 正在扫描 'CheckFragmentation' 表...
表: 'CheckFragmentation'（1067918926）；索引 ID: 2，数据库 ID: 7
已执行 LEAF 级别的扫描。
- 扫描页数.....................................: 4631
- 扫描扩展盘区数...............................: 991
- 扩展盘区开关数...............................: 4592
- 每个扩展盘区上的平均页数.....................: 4.7
- 扫描密度［最佳值:实际值］....................: 12.61%［579:4593］
- 逻辑扫描碎片.................................: 49.90%
- 扩展盘区扫描碎片.............................: 90.41%
- 每页上的平均可用字节数.......................: 6174.7
- 平均页密度（完整）...........................: 23.71%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。
扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。
扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。
每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。
扫描密度[最佳值:实际值]：这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。
逻辑扫描碎片：无序页的百分比。该百分比应该在0%到10%之间，高了则说明有外部碎片。
扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0%，高了则说明有外部碎片。
每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor(填充因子)。
平均页密度(完整)：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。
二、使用动态管理视图
索引碎片可以使用sys.dm_db_index_physical_stats DMV轻松查看，如果索引碎片已经大于30%,则推荐索引重建。而索引碎片小于30%时，推荐使用索引整理。索引碎片因为使查询需要读取更多的页从而增加了 IO，而更多的页意味着占用更多的缓冲区，因此还会形成内存压力。

declare @db sysname
set @db='test'
select cast(object_name(s.object_id,db_id(@db)) as varchar(20)) as 'table name',
 cast(index_type_desc as varchar(20)) as 'index type',
 i.name as 'index name',
 avg_fragmentation_in_percent as 'avg % fragmentation',
 record_count as 'recordcount',
 page_count as 'pages allocated',
 avg_page_space_used_in_percent as 'avg % page space used'
from sys.dm_db_index_physical_stats(db_id(@db),null,null,null,'detailed') s
left outer join sys.indexes i on (i.object_id=s.object_id and i.index_id=s.index_id)
and s.index_id>0
order by avg_fragmentation_in_percent desc

下面语句重建、整理索引

--下面语句重建指定表的所有索引:
alter index all on <table name> rebuild;
--下面语句重建指定索引:
alter index <index name> on <table name> rebuild;
dbcc dbreindex(table_name,index_name[,fillfactor])--后续版本将会删除此功能，请改用alter index...rebuild
--下面语句整理指定表的所有索引:
alter index all on <table name> reorganize;
--下面语句整理指定的索引:
alter index <index name> on <table name> reorganize;

--10:52 2013-8-22 重建索引应用
作业历史记录详情看到执行用户: SERVERNAME\Administrator。表错误: 数据库 ID 7，对象 ID 487776895，索引 ID 2。
链的链接不匹配。(6:4755540)->next = (6:4770301)，但 (6:4770301)->prev = (6:4762263)。
[SQLSTATE HY000]（错误 8908）. 步骤失败。
通过提示查询到是索引tablename.indexname
dbcc checktable (tablename,2)
表错误: 对象 ID 487776895，索引 ID 2。页 (6:4762197) 上的低键值（级别 0）不 >= 父代 (6:5401101) 槽 66 中的键值。
服务器: 消息 8978，级别 16，状态 1，行 1
表错误: 对象 ID 487776895，索引 ID 2。页 (6:4770301) 缺少上一页 (6:4755540) 对它的引用。可能是因为链的链接有问题。
服务器: 消息 8934，级别 16，状态 1，行 1
表错误: 对象 ID 487776895，索引 ID 2。页 (6:5149130) 上的高键值（级别 0）不小于下一页的父代 (0:1) 槽 0 中的低键值。（下一页为 (6:4762196)。）
重建索引解决
dbcc dbreindex('dbname.dbo.tablename',indexname)