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

momo314相同方式共享非商业用途署名转载



一丶 表现及原因分析

在指定的数据库中执行如下 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

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步,应该大部分空页面空间占用的问题都可以解决了。

✎﹏ 本文来自于 momo314的神奇海螺 ,文章原创,转载请注明作者并保留原文链接。