索引的碎片处理

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判断
重编译
*/
)