SQL Server碎片收集及维护

原创

allen750 2015-01-12 13:55:29 博主文章分类：SQL Server ©著作权

©著作权归作者所有：来自51CTO博客作者allen750的原创作品，请联系作者获取转载授权，否则将追究法律责任

在对表进而对表中定义的索引进行数据修改（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性能：

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