Início / dba / Perguntas / 169254
Accepted
ajeh
Asked: 2017-04-06 13:49:01 +0800 CST

A tentativa de compactar uma tabela existente não altera o tamanho da tabela

  • 772

Há uma tabela existente no SQL 2012 Enterprise DB, que tem aproximadamente 120 MB de tamanho com aproximadamente 2.000 registros. Seu maior campo nvarchar(max)contém XML esponjoso (variando de 20 a 300 kB) que por si só compacta aproximadamente 50-75% em 7Zip ou RAR.

Eu tentei os seguintes comandos que retornam instantaneamente, mesmo que simplesmente consultar * da tabela leve alguns segundos:

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)

Mas o tamanho não muda após nenhum deles, mesmo que as propriedades da tabela mostrem corretamente o tipo de compactação.

O servidor está realmente aplicando e removendo a compactação das linhas/páginas? Aparentemente, não tem tempo para fazer isso na fração de segundo que cada comando leva para ser executado.

sql-server compression

1 respostas

  1. Best Answer

    A compactação de linha e a compactação de página têm várias limitações e nem sempre reduzem o espaço usado por sua tabela. Vamos percorrer um exemplo simples. Suponha que eu insira um monte de strings de comprimento 4030 em uma tabela. A aplicação da compactação de página resulta em uma redução de espaço de 4.000 KB para 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

    No entanto, se eu inserir strings de comprimento 4050, não obtenho economia de espaço com a compactação de página, mesmo que as strings sejam todas do mesmo caractere!

    -- 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

    Para sua mesa, você provavelmente está enfrentando uma limitação diferente. Uma tabela no SQL Server pode conter apenas 8060 bytes por linha . Quaisquer dados que não couberem são armazenados separadamente como arquivos ROW_OVERFLOW_DATA. O tamanho médio da linha é de cerca de 64 KB e o tamanho mínimo da linha é de 20 KB. Isso significa que todos os seus dados XML não podem ser armazenados em linha. A compactação de dados não funciona em dados armazenados fora da linha :

    O SQL Server compacta dados LOB na linha, mas não se os dados LOB estiverem armazenados fora da linha. Como resultado, muitos aplicativos pesados ​​de LOB não conseguem tirar o máximo proveito da compactação de dados. No entanto, existem duas soluções alternativas disponíveis. Primeiro, os dados LOB podem ser compactados na camada do aplicativo, mas isso significa que (a) o aplicativo precisa ser modificado (b) o aplicativo não pode aproveitar os recursos de pesquisa e atualização parcial fornecidos no SQL Engine. Segundo, use o recurso de fluxo de arquivos para armazenar dados LOB em um volume compactado. Esta é a nossa solução recomendada quando aplicável. Consulte Books-Online para obter detalhes sobre o recurso de fluxo de arquivos.

    Ambas as soluções alternativas estão disponíveis no SQL Server 2012. No SQL Server 2016, há uma opção adicional com a introdução do GZIP . Isso pode compactar dados que, de outra forma, seriam armazenados fora da linha. Os dados anteriores não podiam ser compactados por página, mas o GZIP reduz o espaço necessário para 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);

    Mesmo strings muito longas podem ser bem compactadas:

    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

relate perguntas