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:
- compactação de linha
- compactação de prefixo
- 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.
Link permanente
Thiago, boa tarde, tudo bem?
Desde o dia do anuncio desta “feature” eu faco o mesmo questionamento: e quanto ao “overhead” de CPU e disco gerado pelo processo de compressao/descompressao dos dados ?
Voce ja chegou a utilizar isto na pratica ?
Tem ideia do percentual de aumento da carga do servidor que esta feature pode gerar ?
Obrigado, abcs!
Link permanente
Pericles, boa tarde.
Que bom que surgiu está pergunta, a taxa de aumento é quase nula, 2%, 3% podendo chegar a 10%.
A compactação de linha terá uma sobrecarga de CPU ainda menor que a de página, mas o beneficio vale a pena.
Obs: Este artigo veio de uma utilização em um cliente.