SQL Server中sp_spaceused统计数据使用的空间总量不正确的原因

内容简介：SQL Server中sp_spaceused统计数据使用的空间总量不正确的原因

很多时候，我们经常使用sp_spaceused来查看表的空间使用情况，上个月群里有个网友说他使用DELETE删除了数据后，使用sp_spaceused查看，发现该表的分配的空间总量（reserved）与数据使用的空间总量（data)没有变化，当时和他讨论了并分析了一下原因，随手记录了一下这个案例，这个周末刚好有点时间，正好分析整理一下这个案例、分享在这篇文章。如下所示，我们先构造数据，我们的测试案例比较极端，刚刚保证每个页面（page）刚好存储两条记录。如下所示：

USE Test ;
GO
 
CREATE TABLE space_test
(
    id        INT,
    name    VARCHAR(4000)
)
GO
SET NOCOUNT ON;
Go
DECLARE @Index INT ;
SET @Index = 1;
WHILE @Index <= 10000
BEGIN 
    INSERT INTO dbo.space_test
    SELECT @Index, REPLICATE(N'k', 4000);
 
    SET @Index += 1;
END

EXEC sp_spaceused 'dbo.space_test'

接下来我们删除了id为偶数的记录。

DELETE dbo . space_test WHERE id % 2 = 0

如下截图所示，我们删除了5000条记录，还剩下5000条记录，但是该表的分配的空间总量（reserved）与数据使用的空间总量（data)没有变化，依然是40008KB和40000KB

我们删除了一半记录，照理说，数据使用的空间总量（data)应该变化了。那么我使用DBCC SHOWCONTIG('space_test')来看看，如下截图所示，

你会发现每页的平均可用字节数（Avg. Bytes Free per Page） 和平均页密度(满)（Avg. Page Density (full)）出现了变化。如下对比所示：

	Avg. Bytes Free per Page	Avg. Page Density (full)
删除前	62	99.23%
删除后	4077.8	49.62%

我们知道sp_spaceused的精确单位是页。它是根据sys.allocation_units 和sys.partitions 这两张管理视图来计算存储空间的。有时候，这两张表可能不能及时反映出数据的准确信息。可以通过 updateusage这个参数，要求SQL Server为这个指令更新管理视图里的统计信息。但是这个案例中，sp_spaceused不准确的原因并不是因为这两张表没有及时反映出数据的准确信息，而是实验中按照 特殊规律，在每一页都删除一条记录，保留一条记录，导致每一页上释放了接近一半的空间，并且页的填充程度接近50%，从而出现页面碎片化非常严重的情况，而sp_spaceused的精确单位是页，从而导致这种特殊的情况出现。

其实这个倒没有必要大惊小怪，这个空间虽然没有释放，但是下次依然可以再次利用，就好比一列火车，每节车厢只载了一半乘客，后面的路程中，可以继续往里面加入新的乘客。如下所示我们插入5000条记录，你会发现页的平均密度变化了，之前空闲的空间被重新利用了。

DECLARE @Index INT ;
SET @Index = 2;
WHILE @Index <= 10000
BEGIN 
    INSERT INTO dbo.space_test
    SELECT @Index, REPLICATE(N'k', 4000);
 
    SET @Index += 2;
END
GO

以上所述就是小编给大家介绍的《SQL Server中sp_spaceused统计数据使用的空间总量不正确的原因》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！

查看所有标签

猜你喜欢:

• 金色前哨 | Polkadot社区发起提案 建议DOT总量由1000万增至10亿
• [译]使用 Pandas 对 Kaggle 数据集进行统计数据分析
• Ubuntu 公布用户统计数据：中文用户占 4%
• 微信小程序数据统计和错误统计的实现
• 实时数据指标统计对数方案
• 首发特性：goproxy.cn 现已推出首个 Go 模块代理统计数据 API

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。