SQL Server DATA COMPRESSION

Galera, vamos ai a mais um cenário do dia a dia, desta vez vamos falar um pouquinho sobre DATA COMPRESSION uma opção muito pouca utilizada ou comentada, acredito que muitos nem utilizam está opção ou não sabem que está é uma opção existente. Neste artigo vamos ver um cenário real de utilização e vocês vão passar a entender um pouco mas como aplicar está opção no dia a dia.

Introdução

No SQL Server 2008 foi introduzida a opção de trabalhar com compressão de linhas ou páginas, então se você ainda está com o SQL Server 2005 está opção não é valida para você. Com a compressão de dados é possível reduzir drasticamente o tamanho do seu banco de dados e vamos entender como.

Hoje temos mas algumas opções de compressão que podem ser aplicadas em tabelas ou índices, na versão do SQL Server 2016 temos compressão de linha, pagina e columnstore, neste artigo vou abordar uma analise utilizando compressão de página, columnstore merece um artigo separado deste, então vamos entender um pouco mas sobre compressão de página.

Compressão de linha é uma ótima opção para reduzir tamanhos de arquivo pois faz com que o SQL Server utilize somente a quantidade de registros necessários, simplificando a ideia a compressão de linha quando você cria aquela coluna nome e atribui a ela o tipo de dados VARCHAR(3000), “mesmo sabendo que você não terá um nome com 3 mil caracteres”  e você  inseri o nome “Thiago Cruz” a compressão de linha vai comprimir aquela coluna para VARCHAR(11) que é o tamanho necessário para armazenar aqueles dados.

Compressão de página também é uma ótima opção e em resumo é uma grande compressão de linhas visando diminuir o numero de página de dados, basicamente quando realizamos a compressão de páginas estamos comprimindo 3 componentes:

1. compactação de linha
2. compactação de prefixo
3. compressão Dicionário

Os componentes acima são referentes a compactação de nível folha.

Nas imagens abaixo podemos observar como funciona a compactação de página no SQL Server:

Imagem 1 – Compressão de prefixo antes e depois onde os prefixos são enviados para o header da página de dados.

Imagem 2 – Mesma página com compressão de dicionario.

Cenário

Segue alguns dados do servidor:

Filegroups:

6 Arquivos Data Files

1 Arquivo de Log

Discos:

6 Discos

• 1 Para o sistema operacional
• 2 com 2 TB que aloca 3 arquivos data files cada
• 1 de 200 GB para arquivo de Log
• 1 de 1 TB para backup
• 1 de 500 GB para pequenas aplicações

Banco de dados:

Microsoft SQL Server 2008 R2 Enterprise

Tamanho total do banco de dados: 3.4 TB

Problema:

Constantemente o cliente se encontrava com o disco quase cheio, chegando a ter somente 5 GB livres e ao verificar a grande fragmentação em um dos filegroups chegando a cerca de 400 GB de espaço não alocado.

Vendo isto iniciou-se uma rotina de SHRINK pela manhã e REBIULD pela madrugada, todos os dias, levando assim a uma extrema ineficiência e perda de performance.

Solução aplicada:

Verificando que tinha um filegroup muito maior que os demais chegando a 1.4 TB e já tinham realizando a liberação de mais disco por duas vezes, neste momento veio a luz…

DATA COMPRESSION PAGE conforme já explicado a cima ele realiza uma compressão nas páginas de dados, reduzindo assim o tamanho do arquivo e quanto menos página de dados mas rápida será a leitura.

Mas em que índice ou tabela realizar a compressão? Para está pergunta o SQL Server tem a procedure sp_estimate_data_compression_savings que retorna o tamanho da tabela e uma estimativa de diminuição do objeto conforme mostrado na imagem 3.

Imagem 3 – sp_estimate_data_compression_savings

Como dica também vou deixar o script abaixo que retorna o tamanho das tabelas, quantidade de linhas e a qual filegroup cada tabela petence.

SELECT DISTINCT TOP 20
T.NAME AS TABLE_NAME,
P.ROWS,
I.DPAGES * 8 AS [TAMANHO KB],
FG.NAME AS FILEGROUP_NAME
FROM
SYS.PARTITIONS P
INNER JOIN SYS.TABLES T
ON P.OBJECT_ID = T.OBJECT_ID
INNER JOIN SYSINDEXES I
ON T.OBJECT_ID = I.ID
INNER JOIN SYS.ALLOCATION_UNITS AU
ON AU.CONTAINER_ID = P.PARTITION_ID
INNER JOIN SYS.FILEGROUPS FG
ON AU.DATA_SPACE_ID = FG.DATA_SPACE_ID
WHERE
P.INDEX_ID < 2

Tendo tudo isso em mãos agora ficou fácil resolver o problema, Job na janela de final de semana realizando a compressão e podemos ver o resultado na imagem 4.

Imagem 4 – Tabela com DATA COMPRESSION PAGE

Visto isso ganhamos 200 GB de disco para felicidade do cliente e a minha felicidade.

Abaixo segue a sintaxe para realização da compressão de página, para realizar a compressão de linha basta alterar PAGE para ROW.

ALTER TABLE TB_COMPRESSION REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)

Observação final: Ao utilizar o DATA COMPRESSION realize um monitoramento de ambiente pois pode aumentar o consumo de CPU e um grande ganho de I/O.

Valeu galera, deixo aqui mas está dica e fico aguardando seu comentário.