SQL Server碎片收集及维护

在对表进而对表中定义的索引进行数据修改（INSERT、UPDATE 和DELETE 语句）的整个过程中都会出现碎片。由于这些修改通常并不在表和索引的行中平均分布，所以每页的填充度会随时间而改变。对于扫描表的部分或全部索引的查询，这种碎片会导致附加的页读取。从而延缓了数据的并行扫描。

可以通过自带sys.dm_db_index_physical_stats函数了解索引的碎片情况，此函数返回指定表或视图的数据和索引的大小和碎片信息。对于索引，针对每个分区中的 B 树的每个级别，返回与其对应的一行。对于堆，针对每个分区的 IN_ROW_DATA 分配单元，返回与其对应的一行。对于大型对象 (LOB) 数据，针对每个分区的 LOB_DATA 分配单元返回与其对应的一行。

通过sys.dm_db_index_physical_stats 函数获取碎片超过10的表和索引的信息,代码如下：

    SET NOCOUNTON;

DECLARE@dbid INT;

Select@dbid=DB_ID()

SELECTobject_id ,index_id ,partition_number,avg_fragmentation_in_percent

INTO#cc

FROMsys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, ‘LIMITED‘)

WHEREavg_fragmentation_in_percent > 10.0 AND index_id > 0

selectt2.name,t2.schema_id,t1.* into #dd from #cc t1,sys.objects t2

wheret1.object_id=t2.object_id

orderby t2.name

selectt2.name+‘.‘+t1.name as tablename ,t1.* into #ee

from#dd t1,sys.schemas t2

wheret1.schema_id=t2.schema_id

select t1.tablename,t2.nameasindexname,t1.avg_fragmentation_in_percent,

t1.partition_number from #ee t1,sys.indexes t2

wheret1.index_id=t2.index_id and t1.object_id=t2.object_id

orderby t1.name

通过以上代码，索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中，对于堆，此值表示堆的区碎片。对于索引，此值表示索引的逻辑碎片。为了获得最佳性能，avg_fragmentation_in_percent 的值应尽可能接近零。但是，从0 到 10％ 范围内的值都可以接受。超过此范围的，就需考虑进行维护以提高SQL性能：

其中有三种方法可减少碎片：

1. 删除并重新创建聚集索引。重新创建聚集索引将对数据进行重新分布，从而使数据页填满。填充度可以使用CREATE INDEX 中的 FILLFACTOR 选项进行配置。

    

2. 碎片在10%至30%之间，进行重新组织索引，使用 ALTER INDEX REORGANIZE按逻辑顺序重新排序索引的叶级页。

如：ALTER INDEXindexname on tablename  REORGANIZEwith(online=on)

 3.使用 ALTER INDEX REBUILD联机或脱机重新生成索引.

如：ALTER INDEXindexname on tablename  REBUILD With(FillFactor = 90 , Online= On)

注：1.由于分区表不支持联机重建，因此对分区表仅重建有索引碎片的分区；非分区表全表联机重建，设置填充因子为90; partition_number>1为分区表

本文出自 “什么是我们的最爱” 博客，请务必保留此出处http://pxizhi.blog.51cto.com/5283742/1602447