主页 / dba / 问题 / 169254
Accepted
ajeh
Asked: 2017-04-06 13:49:01 +0800 CST

尝试压缩现有表不会改变表大小

  • 772

SQL 2012 Enterprise DB 中有一个现有表，其大小约为 120MB，包含约 2000 条记录。它最大的字段是nvarchar(max)包含海绵状的 XML（范围从 20 到 300 kB），它本身在 7Zip 或 RAR 中压缩了大约 50-75%。

我尝试了以下立即返回的命令，即使只是从表中查询 * 需要几秒钟：

alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=ROW)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE)
alter table sch.table1 REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=NONE)

但是在它们中的任何一个之后大小都没有改变，即使表的属性正确地显示了压缩类型。

服务器实际上是否对行/页面应用和删除压缩？它似乎没有时间在执行每个命令的瞬间完成。

sql-server compression

1 个回答

  1. Best Answer

    行压缩和页面压缩有许多限制，并且不会总是减少表使用的空间。让我们来看一个简单的例子。假设我将一堆长度为 4030 的字符串插入到表中。应用页面压缩会导致空间从 4000 KB 减少到 24 KB：

    CREATE TABLE dbo.ZZZZZ_CI (
ID INT NOT NULL IDENTITY (1, 1),
FLUFF VARCHAR(4050),
PRIMARY KEY (ID)
);

SET NOCOUNT ON;

GO
-- insert 1000 rows
INSERT INTO dbo.ZZZZZ_CI VALUES (REPLICATE('Z', 4030));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- 4000 KB for data

ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=PAGE);

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- now 24 KB for data

    但是，如果我插入长度为 4050 的字符串，我不会从页面压缩中节省空间，即使这些字符串都是相同的字符！

    -- remove data and compression
TRUNCATE TABLE dbo.ZZZZZ_CI;
ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=NONE);

GO
-- insert 1000 rows
INSERT INTO dbo.ZZZZZ_CI VALUES (REPLICATE('Z', 4050));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- 8000 KB for data

ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=PAGE);

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- still 8000 KB for data

    对于您的表，您可能会遇到不同的限制。SQL Server 中的表每行只能包含8060 个字节。任何不适合的数据都单独存储为ROW_OVERFLOW_DATA. 您的平均行大小约为 64 KB，最小行大小为 20 KB。这意味着您的所有 XML 数据都不能存储在行中。数据压缩不适用于存储在行外的数据：

    SQL Server 会压缩行内 LOB 数据，但如果 LOB 数据存储在行外，则不会。因此，许多 LOB 繁重的应用程序无法充分利用数据压缩。但是，有两种解决方法可用。首先，可以在应用层压缩 LOB 数据，但这意味着 (a) 需要修改应用程序 (b) 应用程序无法利用 SQL 引擎中提供的搜索和部分更新功能。其次，使用文件流功能将 LOB 数据存储在压缩卷上。这是我们推荐的适用解决方案。有关文件流功能的详细信息，请参阅 Books-Online。

    SQL Server 2012 中提供了这两种解决方法。在 SQL Server 2016 中，引入了GZIP有一个附加选项。这可以压缩原本会存储在行外的数据。之前的数据无法进行页面压缩，但 GZIP 将所需空间减少到 64 KB：

    CREATE TABLE dbo.ZZZZZ_CI_2016 (
ID INT NOT NULL IDENTITY (1, 1),
FLUFF_COMPRESSED varbinary(max),
PRIMARY KEY (ID)
);

GO
-- insert 1000 compressed rows
INSERT INTO dbo.ZZZZZ_CI_2016 VALUES (COMPRESS(REPLICATE('Z', 4050)));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI_2016';  -- now 64 KB for data

SELECT COUNT(*) -- 1000 is returned
FROM ZZZZZ_CI_2016
WHERE DECOMPRESS(FLUFF_COMPRESSED) = REPLICATE('Z', 4050);

    即使很长的字符串也可以很好地压缩：

    TRUNCATE TABLE dbo.ZZZZZ_CI_2016;

GO
-- insert 1000 compressed rows
INSERT INTO dbo.ZZZZZ_CI_2016 VALUES (COMPRESS(REPLICATE(CAST('Z' AS VARCHAR(MAX)), 99999)));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI_2016';  -- now 152 KB for for 99999 length strings
    • 9

相关问题