SQLSERVER 中大量空白页(unused_pages) 空间占用的问题的解决方案

momo3142020-09-21相同方式共享非商业用途署名转载

一丶表现及原因分析

在指定的数据库中执行如下 SQL 语句：

SELECT 
    t.name AS TableName,
    s.name AS SchemaName,
    p.rows,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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 JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.object_id > 255 
GROUP BY t.name, s.name, p.rows
ORDER BY TotalSpaceMB DESC, t.name ASC

如下图所示，如果出现 ( UnusedSpaceMB / TotalSpace ) 占比过大的情况，则证明对应的 table 中包含大量未被使用的空间。

如果对这部分空间进行释放，我们的数据库大小则会进一步下降。

空白页(unused_pages)占用太多的空间

这种情况一般会在数据删除较多或较频繁的表中出现，其根本原因在于：

对于一张表而言，其早期的数据被大量删除（这部分数据所占用的空间集中于整张表的前部，或零散分布于各个位置），而后续的数据又只会写入到整张表的尾部，从而导致因数据删除而释放出来的空间无法被再次使用，这部分空间即 unused_pages。

unused_pages = total_pages - used_pages

对于整张表来说，ununsed_pages 是无用的，只会徒增整张表所占用的空间大小（total_pages）。
对于整个数据库而言，unused_pages 已经被分配给了指定的表，其他表也是无法使用的，只会徒增整个数据库的大小。

二、解决方案

其实，从上面的分析很容易看出来，只要我们能够把这部分空间抽离出来，并放到表的尾部，这部分空间就可以重新被使用。

STEP 01：重建目标表的聚集索引

ALTER INDEX [聚集索引名] ON [表名] REBUILD

根据表的大小不同，该语句执行时间可能会很长，且会影响数据库的性能表现。

STEP 02：查看 Data File ID

SELECT 
    DB_NAME() AS DbName,
    name AS [FileName],
    type_desc,
    size / 128.0 AS CurrentSizeMB, 
    size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS FreeSpaceMB, 
    file_id
FROM sys.database_files

STEP 03：针对指定的 File 进行收缩


DBCC SHRINKFILE (1, TRUNCATEONLY/*只会释放尾部空页面所占用的空间*/);

DBCC SHRINKFILE (1, 256000/*收缩到250G*/);

根据表的大小不同，该语句执行时间可能会很长，且会影响数据库的性能表现。

如果在 SHRINKFILE 时指定了大小，SqlServer 会对页面重新排序，中间的空页面会被放到尾部，并被释放掉。
关于 SHRINKFILE 的大小，可以参考本文第一条SQL语句查询到的非空页面所占用的空间大小之和。但如果一次性收缩太多的话，执行会需要花很长时间，可以少量多次执行此语句，以便加快速度。

STEP 04：确认表索引的逻辑碎片百分比是否在理想范围内

DECLARE @TableName NVARCHAR(100) = '表名'

SELECT 
    stat.object_id, 
    object_name(stat.object_id) AS TableName,
    stat.index_id, 
    name AS IndedxName, 
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@TableName), NULL, NULL, NULL) AS stat
INNER JOIN sys.indexes AS idx ON stat.object_id = idx.object_id AND stat.index_id = idx.index_id

一般来说，为了获得更好的性能，avg_fragmentation_in_percent 的值应该尽可能的趋近于0，但 10% 以内基本都是可以接受的。

STEP 05：重建碎片较多的索引

针对STEP 04中的每个碎片较多的索引，执行以下语句进行重建：

ALTER INDEX [索引名] ON [表名] REBUILD

一般来说，重建非聚集索引要比重建聚集索引快很多，但根据表大小的不同，依然有可能会花费很长时间。

STEP 06：重新收缩数据库

DBCC SHRINKFILE (1, 256000/*收缩到250G*/);

依然建议以少量多次的方式执行

经过上面6步，应该大部分空页面空间占用的问题都可以解决了。

SQLSERVER 中大量空白页(unused_pages) 空间占用的问题的解决方案

一丶 表现及原因分析

二、 解决方案

一丶表现及原因分析

二、解决方案