对于利用SqlServer作为数据库的应用程序而言，如果对性能有较高要求的话，索引碎片的处理是不可忽略的一个环节：
-
MSDN: http: // msdn.microsoft.com / zh - cn / library / ms189858.aspx
-- 索引的维护 提示（online的作用非常的大，聚集的重键会使表不能使用）
昨天经过kg牛牛的提示，想了点索引碎片的知识。随想随写，有点乱，也许有错的地方，csdner可以灰铁提示
SELECT OBJECT_NAME (dt. object_id ),
si.name,
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
FROM
( SELECT object_id ,
index_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats ( DB_ID (), NULL , NULL , NULL , ' DETAILED ' )
WHERE index_id <> 0
) AS dt -- does not return information about heaps
INNER JOIN sys.indexes si
ON si. object_id = dt. object_id
AND si.index_id = dt.index_id
碎片的分类
1内部碎片（avg_page_space_used_in_percent） - 是说索引的大小，超过了索引的实际大小，也就是填充度
2外部碎片（avg_fragmentation_in_percent） - 是说索引页面的排序和物理不一致, index page的newxtpage + prevpageid的是最小相临的（REORGANIZE就是解决这个问题）
3物理连续性（fragment_count 和 avg_fragment_size_in_pages [ >65 and <256 ] ） - 是说单一的分配单元的8个页面的连续性质，简单可以说是nextpage是不是当前page的 + 1至 + 7
4内部的逻辑碎片（有聚集索引的情况） 也就是槽号的物理顺序和逻辑顺序的不同（就是很多人说的聚集索引的顺序，和物理的存储顺序是不同的）
ALTER INDEX [ index_id ] ON [ dbo ] . [ tbTest ] REORGANIZE
重新组织索引
REORGANIZE的过程：
1压缩过程 查找临近的page,查找可以移动的记录转移到小pageid的页面，增加填充度(不会分配新的index page,有些记录会因为移动，而不在先前的page里存放)
2使逻辑和物理的顺序保持一致（这和上面的不冲突），使用中间页来互换页面内容，使页面的pageid从小到大排列，删除最后的空page
ALTER INDEX [ index_id ] ON [ dbo ] . [ tbTest ] REBUILD
重键索引
同时维护2个索引，新的创建完，会删除旧索引（因为同时维护2个版本的索引，只能分配新的索引页面，删除旧的索引页面）
所以rebuild后的avg_fragmentation_in_percent逻辑碎片，应该是在0 - 3左右，avg_page_space_used_in_percent会在95以上（大型行也许会小一点）
-- 页拆分引起的碎片
if object_id ( ' tbTest ' ) is not null
drop table tbTest
go
create table tbTest(a int primary key ,b varchar ( 1600 ))
go
insert into tbtest(a,b) select 5 , replicate ( ' a ' , 1600 )
insert into tbtest(a,b) select 10 , replicate ( ' b ' , 1600 )
insert into tbtest(a,b) select 15 , replicate ( ' c ' , 1600 )
insert into tbtest(a,b) select 20 , replicate ( ' d ' , 1600 )
insert into tbtest(a,b) select 25 , replicate ( ' e ' , 1600 )
dbcc ind( [ index ] ,tbTest, 1 )
dbcc traceon( 3604 )
dbcc page( [ index ] , 1 , 45 , 1 )
insert into tbtest(a,b) select 22 , replicate ( ' f ' , 1600 )
用上面的查询看碎片的结果
(无列名) name avg_fragmentation_in_percent avg_page_space_used_in_percent
tbTest PK__tbTest__164452B1 50 59.9085742525327
dbcc page( [ index ] , 1 , 45 , 1 )
-- 内部碎片avg_page_space_used_in_percent
select ( 8096 - 3245 /* m_freeCnt */ ) * 1.0 / 8096 = 0.599184 基本可以对上
-- 逻辑碎片
avg_fragmentation_in_percent 这个的50，也许是 [ 物理连续性 ] 一类的碎片，因为外部的大小顺序倒是一样的，不深究了
dbcc page( [ index ] , 1 , 80 , 1 ) 。。。
-- 判断REORGANIZE和REBUILD，重新组织和重建索引（注意，text,varchar(max)等要特殊处理，下次再说...:)）
SELECT t.name as tablename, ' ALTER INDEX [ ' + ix.name + ' ] ON [ ' + s.name + ' ].[ ' + t.name + ' ] ' +
CASE
WHEN ps.avg_fragmentation_in_percent > 15
THEN ' REBUILD '
ELSE ' REORGANIZE '
END +
CASE
WHEN pc.partition_count > 1
THEN ' PARTITION = ' + CAST (ps.partition_number AS nvarchar ( MAX ))
ELSE ''
END ,
avg_fragmentation_in_percent
FROM sys.indexes AS ix
INNER JOIN sys.tables t
ON t. object_id = ix. object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN
( SELECT object_id ,
index_id ,
avg_fragmentation_in_percent,
partition_number
FROM sys.dm_db_index_physical_stats ( DB_ID (), NULL , NULL , NULL , NULL )
) ps
ON t. object_id = ps. object_id
AND ix.index_id = ps.index_id
INNER JOIN
( SELECT object_id ,
index_id ,
COUNT ( DISTINCT partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id ,
index_id
) pc
ON t. object_id = pc. object_id
AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10
AND ix.name IS NOT NULL
-- 上面语句是网上的，加了行数判断，也可以用页数量判断
and exists (
select * from sys.partitions where object_id = t. object_id and rows > 10000
/* 控制一下记录行数，因为小表的rebuild等会根据很多参数标准来判断是否要去重建。
网上搜索的判断因子：
生成查询计划的阀值
缓存机制，缓存的筛选，LRU算法
预读机制
checkpoint减少回滚距离
智能join判断
重编译
*/
)
1118
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
