SQL Server表空间碎片化回收

1 锁片化的产生

1.1 产生碎片化的原因

1、在B-tree索引中，表数据按照聚集索引的排序进行物理存储，若聚集索引离散化比较严重，那么可能会出现较为严重的碎片化问题；

2、随着业务的DML操作，会伴随着数据页分裂的情况，这种情况下也会导致表空间碎片化问题;

3、大表通过delete清理无效历史数据，delete产生碎片化空间；

1.2 碎片化的影响

表空间碎片化越严重越容易影响对该表的查询效率，这是因为当表碎片化比较严重时，数据库根据执行计划扫描满足需求的数据页会扫描较多“无效页面”，导致查询操作需要更多的IO消耗。

1.3 定位碎片化

1、在SQL Server中，可以通过DBCC SHOWCONTIG的方式查看表空间碎片化的一些统计信息，具体语法如下：

--查看数据库中所有索引的碎片信息
use ${数据库名}
DBCC SHOWCONTIG WITH ALL_INDEXES 
--查看指定表的所有索引的碎片信息
DBCC SHOWCONTIG (${表名}) WITH ALL_INDEXES   
--查看指定表、指定索引的碎片信息
DBCC SHOWCONTIG (${表名},${索引名})

2、通过sys.dm_db_index_physical_stats()查看索引碎片化

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'db1'), OBJECT_ID(N'db1.dbo.users'), NULL, NULL , 'LIMITED');
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'db1'), OBJECT_ID(N'db1.dbo.users'), NULL, NULL , 'DETAILED');

重点关注：

• avg_fragment_size_in_pages ： 该参数值越大，范围扫描的性能越好
• avg_fragmentation_in_percent ：对于heap表，该参数表示区碎片百分比；对于index，该参数表示逻辑碎片；该参数越大表示表的碎片化越严重，需要通过 Reorganize or Rebuild Indexes