SQL Server表空间碎片化回收的实现

碎片化的成因与性能影响

在SQL Server中，表数据通常按照聚集索引的物理顺序存储。当出现以下情况时，容易产生严重的空间碎片化：

• 聚集索引离散化：若数据的物理排序与逻辑结构差异较大，导致页分裂。
• DML操作：频繁的增删改操作引发数据页分裂，打乱数据存储顺序。
• Delete清理历史数据：删除大表中的大量记录后，产生大量的空闲碎片空间。

碎片化对查询性能的负面影响主要体现在IO消耗上。当碎片严重时，优化器在执行计划时需要扫描更多的“无效页面”才能获取所需数据，导致磁盘I/O显著增加，从而拖慢整体查询效率。

如何定位碎片化水平

要有效处理碎片，首先需要通过以下三种方式确认数据库对象的健康状况。

第1步：使用DBCC SHOWCONTIG查看统计信息

该命令用于快速获取索引层面的碎片概况，语法灵活支持全库或指定对象检查：

1. 查看当前库所有索引：USE <数据库名>; DBCC SHOWCONTIG WITH ALL_INDEXES;
2. 查看指定表的所有索引：DBCC SHOWCONTIG (<表名>) WITH ALL_INDEXES;
3. 查看特定索引：DBCC SHOWCONTIG (<表名>, <索引名>)

第2步：查询动态管理视图 sys.dm_db_index_physical_stats

相比传统命令，DMV提供更精确的结构化数据。需重点关注以下指标：

• avg_fragmentation_in_percent：反映逻辑碎片百分比。数值越高表示越严重，通常建议对该值较高的索引执行 Reorganize 或 Rebuild 操作。
• avg_page_space_used_in_percent：表示数据页的填充率。若该值偏低，意味着存在严重的内部页碎片，必须通过索引重建来压缩数据。
• fragment_count 与 page_count：分别代表碎片页数和总页数，用于评估碎片规模。

示例代码：

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

第3步：按库生成Top列表排查大表

对于拥有众多表的生产环境，建议先运行聚合查询，找出占用磁盘多但存在大量未用空间的“Top表”，作为优先优化目标。

SELECT 
     t.NAME AS TableName,
     s.Name AS SchemaName,
     SUM(a.total_pages) * 8 AS TotalSpaceKB,
     SUM(a.used_pages) * 8 AS UsedSpaceKB,
     (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS FragmentedSpaceKB
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
GROUP BY t.Name, s.Name, p.Rows
ORDER BY TotalSpaceKB DESC;

处理策略：索引重建方式比对

解决碎片的根本手段是重新组织B-tree结构。根据是否阻塞业务及实现原理的不同，主要有以下几种方式：

第4步：完全删除并重建聚集索引（传统方式）

此操作先DROP原有聚集索引再CREATE新索引，具有最彻底的重排效果，但代价极高：

• 全局阻塞：在重建期间会持有Sch-M锁，直接阻断表的读写操作。
• 连锁反应：删除主键聚集索引将强制触发所有相关非聚集索引的自动重建。
• 优点：能将整张表物理数据重排至最优状态，最大化回收空间。

第5步：使用 DROP_EXISTING 优化重建

这是官方推荐的标准做法。通过参数 WITH (DROP_EXISTING = ON) 配合建表语句完成替换：

CREATE INDEX <index_name> ON T(<index_col>) WITH (DROP_EXISTING = ON);

• 关键优势：保留主键键值映射，避免连带刷新非聚集索引，减少了工作量。
• 副作用：重建期间依然会产生阻塞，不适合要求7x24小时高可用的核心交易时段。

第6步：使用 DBCC DBREINDEX 支持在线操作（企业版功能）

在非聚簇索引场景下，该命令支持Online（在线）模式：

• 适用版本：仅限 SQL Server 企业版引擎支持非聚集索引的在线重建。
• 性能权衡：虽然不阻塞读写请求，但由于需要维护两个镜像版本，DML操作的执行延迟会有所上升。
• 暂停/终止警告：若在线操作中途被暂停且不再继续，应直接终止(Terminate)而非Pause，否则可能导致表长期处于锁定边缘且浪费资源。

第7步：轻量级整理 DBCC INDEXDEFRAG

不同于前四种暴力“推倒重来”，该方法采用渐进式整理：

• 机制：按逻辑页顺序移动行，仅移除彻底废空的页，不会改变物理布局的全局排序。
• 零阻塞：全程不锁定表，允许业务并发访问。
• 局限性：回收空间的幅度有限，对于极度破碎的大表，其效果远不如Rebuild索引。

语法：`DBCC INDEXDEFRAG (, '.

SELECT a.name [文件名], 
       CAST(a.size*1.0/128 AS DECIMAL(12,1)) AS SizeMB,
       CAST(fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS UsedMB,
       CASE WHEN A.growth = 0 THEN '固定大小' 
            ELSE '自动增长('+CAST(CASE WHEN is_percent_growth=1 THEN cast(a.growth AS VARCHAR)+'%'
            ELSE cast(cast(a.growth*1.0/128 as decimal(0)) AS VARCHAR)+'MB' END AS VARCHAR)+')'
       END AS GrowthMode
FROM sys.database_files a 
JOIN sys.sysfiles s ON a.file_id=s.fileid 
ORDER BY a.type;

USE [<db_name>];
GO
DBCC SHRINKDATABASE(N'<db_name>', 0);
GO