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 来进行碎片化回收

• avg_page_space_used_in_percent ： 该参数表示数据页的填充程度，一般小于100％，但是该参数越小，表示数据页面碎片化情况越严重。若想要数据页使用率的问题，必须进行索引重建操作

• fragment_count : 碎片化数据页数

• page_count : 扫描数据页数

3、通过统计信息查看数据库碎片化空间Top表信息

SELECT
db_name() as DbName,
   t.NAME AS TableName,
   s.Name AS SchemaName,
   p.rows AS RowCounts,
   SUM(a.total_pages) * 8 AS TotalSpaceKB,
   CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
   SUM(a.used_pages) * 8 AS 总使用空间KB,
   CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总使用空间MB,
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 碎片化空间KB,
   CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS 碎片化空间MB
FROM
   sys.tables t
INNER JOIN      
   sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
   sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
   sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
   sys.schemas s ON t.schema_id = s.schema_id
WHERE
   t.is_ms_shipped = 0
   AND i.OBJECT_ID > 0
GROUP BY
   t.Name, s.Name, p.Rows
ORDER BY
   总共占用空间MB desc

2 碎片化处理

由于表数据是根据聚集索引排序进行物理存储，所以当表碎片化比较严重时，可以通过对聚集索引的重新组织来进行碎片化空间回收，重建索引的方式也有比较多方式，主要如下：

2.1 删除并重建聚集索引

该方式其实就是将碎片化比较严重的表，先通过drop index删除其聚集索引，然后通过create index或者alter table重建聚集索引。该方式的特点是：

• 执行删除聚集索引后，会影响该表有关利用该索引进行查询的SQL执行效率

• 执行删除聚集索引，也会导致该表相关的非聚集索引重建

• 在重建聚集索引期间，会获取相应的Sch-M锁，阻塞业务正常读写操作，且创建聚集索引后也会导致相应的非聚集索引重建

• 该方式会将整张表数据进行重新组织，可回收最大限度的碎片化空间

2.2 DROP_EXISTING

使用DROP_EXISTING进行重建索引，也是对聚集索引的删除重建，但是该方式在方法一的基础上做了一些优化：

• 删除聚集索引时，会保留主键索引的键值，避免了删除、重建聚集索引时对非聚集索引的重建

• 执行DROP_EXISTING重建索引期间，仍然会对正常业务读写操作造成阻塞

• 该方式会将整张表数据进行重新组织，可回收最大限度的碎片化空间

基本语法：

CREATE INDEX ${index_name} ON T(${index_col})  WITH (DROP_EXISTING = ON)  

2.3 DBCC DBREINDEX

DBCC DBREINDEX也是通过对索引的删除以及重建来实现碎片化回收。根据数据库版本（企业版or非企业版）以及索引类型（非聚集or聚集），该操作是可以实现在线或者离线操作。

• 在企业版数据引擎中，对于非聚集索引的索引重建可以通过在线的方式进行操作

• 在线索引重建期间，虽然不阻塞正常业务读写操作，但还是对应的DML操作执行效率还是会有所下降

• 离线索引重建期间，阻塞业务读写

• 对于在线索引重建，可以进行暂停或者终止。但是暂停期间应用会影响该表的DML执行效率，如果后续不继续索引的重建操作，请直接终止而不是暂停

• 该方式会将整张表数据进行重新组织，可回收最大限度的碎片化空间

基本语法：

-- 重建指定索引
USE ${db_name};   
GO  
DBCC DBREINDEX ('${schema_name}.${table_name}', ${index_name},80);  
GO

-- 重建指定表全部索引
USE ${db_name};   
GO  
DBCC DBREINDEX ('${schema_name}.${table_name}', ' ', 70);  
GO

2.4 DBCC INDEXDEFRAG

该方式的实现逻辑与以上三种大有不同，DBCC INDEXDEFRAG并非完全重新组织整张表的b-tree结构：

DBCC INDEXDEFRAG按照索引键的逻辑顺序，通过压缩索引页里的行然后删除那些由此产生的不必要的碎片化数据页、删除完全碎片化数据页面的方式来进行碎片化空间的回收
该方式执行期间不阻塞业务读写操作
该方式下可回收的碎片化空间效果可能不如以上三种索引重建的方式
基本语法：

DBCC INDEXDEFRAG (${db_name}, '${schema_name}.${table_name}', ${index_name});  

3 空间回收

需要注意的是，在SQL Server数据库，我们对表空间数据进行碎片化处理、或者truncate清空无效历史数据，这些释放出来的空间只是空出来，当有新数据写入时，优先使用这些空出来的数据页，而不是再向OS申请新的数据空间扩展。所以这部分并不会直接释放给OS，如果我们想要达到降低整个OS的磁盘空间使用率的话，还需要对数据库的数据文件进行收缩。

1、检查数据文件空间使用率

-- 检查数据库文件空间使用率
SELECT a.name [文件名称] ,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件设置大小(MB)] ,
   CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文件所占空间(MB)] ,
   CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0 AS DECIMAL(12,1)) AS [所占空间率％] ,
   CASE WHEN A.growth =0 THEN '文件大小固定，不会增长' ELSE '文件将自动增长' end [增长模式] ,CASE WHEN A.growth > 0 AND is_percent_growth = 0
   THEN '增量为固定大小' WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示' ELSE '文件大小固定，不会增长' END AS [增量模式] ,
   CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB'
   WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'％' ELSE '文件大小固定，不会增长' end AS [增长值(％或MB)] ,
   a.physical_name AS [文件所在目录] ,a.type_desc AS [文件类型]
FROM sys.database_files a
INNER JOIN sys.sysfiles AS s  ON a.[file_id]=s.fileid
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id] ORDER BY a.[type]

2、收缩数据文件

USE [${db_name}]
GO
DBCC SHRINKDATABASE(N'${db_name}' )
GO

参考链接：

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

来源：https://blog.csdn.net/weixin_37692493/article/details/122577940