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

栏目: 数据库 · 发布时间: 7年前

内容简介: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'

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

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

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

DELETE dbo . space_test WHERE id % 2 = 0

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

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

我们删除了一半记录,照理说,数据使用的空间总量(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%

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

我们知道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统计数据使用的空间总量不正确的原因


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

查看所有标签

猜你喜欢:

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

解码宇宙

解码宇宙

(美) 塞费 / 隋竹梅 / 上海科技教育出版社 / 2010-4 / 26.00元

《解码宇宙:新信息科学看天地万物》:宇宙,或许就是一台庞大的计算机。这是查尔斯·塞费在《解码宇宙:新信息科学看天地万物》中对宇宙做出的结论。作者从信息的特点开始谈起,详细论述了信息论和量子计算,向我们展示了一种不可思议的拜占庭式宇宙的情景,涉及生命的本质、热力学、相对论、量子力学、黑洞、多重宇宙,直至宇宙的命运。《解码宇宙:新信息科学看天地万物》资料翔实,内容丰富多彩,思路清晰,观点明确,读后使人......一起来看看 《解码宇宙》 这本书的介绍吧!

HTML 编码/解码
HTML 编码/解码

HTML 编码/解码

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具