Data Compression – Comprimindo dados…

Data Compression

O grande crescimento de dados não é um coisa fora da realidade atual, cada fez mais dados são armazenados fazendo com que o volume de disco seja maior. Mas será que apenas comprar mais discos é a solução ideal para esse problema de armazenamento? Será que não existe outra solução para se adequar ao meu volume de disco?

Não existe uma resposta precisa, tudo depende do volume de dados que são armazenados, mas o SQL Server pode te ajudar a economizar espaço em disco fazendo com que você posso armazenar mais informações.

A pergunta agora é COMO??

O SQL Server 2008 implanta uma particularidade chamada compressão de dados. A compressão está disponível somente no SQL Server 2008 Enterprise e Developer. Essa compressão suporta dois tipos: ROW e PAGE.

A compressão de dados pode ser configurada para os seguintes objetos de banco de dados:

  • Para uma tabela que é armazenada como um heap.
  • Para uma tabela que é armazenada como um índice clustered.
  • Para um índice nonclustered.
  • Para uma view indexada.
  • Para tabelas e índices particionados, a opção de compressão pode ser configurada para cada partição, e as várias partições de um objeto podem conter diferentes tem configurações de compressão.

Compressão não está disponível para objetos de sistema. A configuração de compressão não é aplicada automaticamente a índices nonclustered, por isso cada índice nonclustered deve ser configurado individual e manualmente.

Row Compression Level

Esta característica de compressão leva em conta o tipo de estruturas de dados variáveis que definem uma coluna. Row Compression Level é um nível de compressão que não utiliza nenhum algoritmo de compressão.

Como assim? O que ele faz então!?

O principal objetivo da Row Compression Level é reduzir o armazenamento de dados do tipo fixos, ou seja, quando você está permitindo Row Level Compression você está apenas mudando o formato de armazenamento físico dos dados que estão associados a um tipo de dados.

Row Level Compression estende o formato de armazenamento vardecimal por armazenar dados de todos os tipos de comprimento fixo em um formato de armazenamento de comprimento variável. Este tipo de compressão irá remover quaisquer bytes extras no tipo de dados fixo. Não há absolutamente nenhuma mudança necessária no aplicativo.

Por exemplo, temos uma coluna CHAR (100) que está usando o Row Compression Level só usará a quantidade de armazenamento definida pelos dados. Como assim? Vamos armazenar a frase “SQL Server 2008” na coluna, a frase contem apenas 15 caracteres e apenas esses 15 caracteres são armazenados ao contrario dos 100 que foram definidos pela coluna, portanto, você tem uma economia de 85% no espaço de armazenamento.

E como isso funciona na pratica!?

Vamos lá.

Primeiramente vamos criar uma tabela simples:

CREATE

 

TABLE RowCompression

(

 

[Id]

 

INTIDENTITYPRIMARYKEY

 

,[Nome] CHAR(100)

 

,[Valor] NUMERIC(18,4)

 

,[Data] DATETIME

)

GO

Vamos inserir alguns registros.

 

SET

NOCOUNTON

DECLARE

 

@min INT

, @max INT

, @rand INT

, @nome VARCHAR(100)

, @cont1 INT

, @cont INT

 

SET @cont = 0

WHILE

 

@cont < 10000000

BEGIN

SET @min = 80

SET @max = 110

SET @cont1 = 0

SET @nome =

WHILE @cont1 <CONVERT(int,RAND()* 100)

BEGIN

SET @rand =((@Max + 1) @Min)*RAND()+ @Min

SET @nome = @nome +char(@rand)

SET @cont1 = @cont1 + 1

END

INSERTINTO dbo.RowCompression

 

(

[Valor]

, [Data]

, [Nome]

)

SELECT

RAND()* 448515

,GETDATE()+Convert(Int,RAND()* 1000)

, @nome

 

SET @cont = @cont + 1

END

SETNOCOUNTOFF

Depois de inserir os registros temos o seguinte espaço utilizado pela tabela

clip_image001

 

O resultado acima indica que a tabela está armazenando 1260 MB.

Vamos aplicar o Row Compression Level agora.

clip_image002

Uau.. Notem a diferença de entre as colunas DATA e INDEX_SIZE. Depois de aplicar a compressão o armazenamento da tabela ficou em 350 MB. Deixando a tabela aproximadamente com apenas 28% do tamanho original!

Page Compression Level

Nas versões anteriores do SQL Server cada valor era armazenado na página, independentemente se o mesmo valor já havia aparecido na mesma coluna para algumas outras linhas dentro de uma página.

No SQL Server 2008, o valor redundante ou duplicado será armazenado apenas uma vez dentro da página e o será referenciado em todas as outras ocorrências, dessa forma temos o Page Compression Level.

Basicamente, o Page Compression Level é um superconjunto de compressão ROW e leva em conta os dados redundantes em uma ou mais linhas em uma determinada página. Ele também usa a compactação de prefixo e dicionário.

O método Page Compression Level é mais vital, pois permite que os dados comuns sejam compartilhados entre as linhas de uma determinada página.

Esse tipo de compressão utiliza as seguintes técnicas:

  • Row compression
  • Prefix Compression
  • Dictionary Compression

Row compression já vimos acima, agora vamos ver o resto!

Prefix Compression

 

Para cada coluna em uma página os prefixos duplicados são identificados. Estes prefixos são armazenados nos cabeçalhos de Compressão de Informação (CI), que residem após o cabeçalho da página. Um número de referência é atribuído a esses prefixos e esse número de referência é utilizado onde quer que esses prefixos estejam sendo usados.

  • Para cada página que está sendo comprimido, o Prefix Compression utiliza os seguintes passos:
  • Para cada coluna, um valor que é identificado pode ser usado para reduzir o espaço de armazenamento para os valores em cada coluna.
  • A linha que representa os valores de prefixo para cada coluna é criada e armazenada na CI, estrutura que segue imediatamente o cabeçalho da página.
  • Os valores repetidos na coluna são substituídos por uma referência ao prefixo correspondente. Se o valor em uma linha não corresponder exatamente ao valor do prefixo selecionado, uma correspondência parcial ainda pode ser indicada.

A ilustração abaixo, retirada do site da MSDN, mostra uma página de exemplo de uma tabela antes da utilização do Prefix Compression

clip_image003

A ilustração abaixo, também retirada do site da MSDN, mostra a mesma página após a utilização do Prefix Compression.

O prefixo é movido para o cabeçalho e os valores da coluna são alterados as referências ao prefixo

clip_image004

Na primeira coluna da primeira linha, o valor 4b indica que os quatro primeiros caracteres do prefixo (AAAB) estão presentes para essa linha, e também o caráter b. Isso torna o aaabb valor resultante, que é o valor original.

Dictionary Compression

Pesquisas por valores duplicados por fora da página e as armazena no CI. A principal diferença entre o Prefix Compression e Dictionary Compression é que o Prefix se restringe apenas a uma coluna enquanto Dictionary é aplicável para a página completa.

Depois que a Prefix Compression foi concluída, a Dictionary Compression é aplicada e pesquisas valores repetidos em qualquer lugar da página e os armazena na área de CI. Ao contrário da Prefix Compression, a Dictionary Compression não está restrita a uma coluna e pode substituir valores repetidos, que ocorrem em qualquer lugar em uma página.

A ilustração abaixo mostra a mesma página após a compressão dicionário

clip_image005

Observe que o valor 4b foi referenciado a partir de diferentes colunas da página.

Legal, como ficaria nossa tabela com esse nível de compressão?

Simples, primeiro vamos voltar à tabela ao seu estado original, sem compressão.

clip_image006

Agora aplicamos o Page Compression Level

clip_image007

 

Mais uma vez notem a diferença de entre as colunas DATA e INDEX_SIZE.

Depois de aplicar a compressão o armazenamento da tabela ficou em 338 MB.

Deixando a tabela aproximadamente com apenas 26% do tamanho original, tivemos um ganho de compressão em relação ao Row Compression Level.

Comparação entre o Row Compression Level e o Page Compression Level

Row Level

Page Level

Nenhum algoritmo de compressão é utilizado

Algoritmo de compressão é utilizado

Menor taxa de compressão em relação ao nível de página

Maior taxa de compressão em relação ao nível de linha

Este método tenta converter tipo de dados fixo para tipo de dados variáveis para a realização de compressão

Este método usa o algoritmo baseado em Prefix e Dictionary para atingir compressão

Usa menos recursos (processador, memória, etc.)

Usa mais recursos (processador, memória, etc.)

Taxa de compressão depende da distribuição dos dados e do esquema.

Taxa de compressão depende da distribuição dos dados.

Como estimar de economia de espaço

No SQL Server 2008 existem duas maneiras de estimar a economia de espaço para armazenamento de tabelas e índices. O primeiro método é usar uma SP de sistema chamada sp_estimate_data_compression_savings e o segundo método é usar o Assistente de Compactação de Dados.

Primeiro vamos usar a sp_estimate_data_compression_savings onde:

  • 1º parâmetro é o nome do schema;
  • 2º parâmetro é o nome do objeto;
  • 3º parâmetro é o ID do índice;
  • 4º parâmetro é o ID da Partição
  • 5º parâmetro é o tipo de compressão;

clip_image009

Notem as colunas size_with_current_compression_setting(KB) e size_with_requested_compression_setting(KB), essas colunas mostras o valor atual e o valor após a compressão. Dessa forma podemos saber quanto de espaço iremos ganhar com a aplicação da compressão.

Agora vamos utilizar o assistente.

Clique com o botão direito em cima da tabela escolha a opção Storege -> Manage Compression

clip_image011

O Assistente irá aparecer.

 

clip_image013

Na próxima pagina você pode escolher o tipo de compressão e antes de aplicar você pode calcular o espaço para verificar qual é melhor!

clip_image015

 

Eu escolhi o tipo de compressão PAGE, e cliquei no botão Calculate. O assistente irá trazer as informações abaixo.

clip_image017

Seguindo com o assitente, escolhi uma das opções, e em seguida irá gerar uma lista com todos os itens a serem executados.

clip_image019

Após executar irá aparecer uma tela de confirmação.

clip_image021

 

Você pode verificar todos os seus objetos e quais níveis de compressão eles estão utilizando através do comando:

clip_image022

 

Ou você pode clicar com o botão direito em cima do objeto, na guia Storage, a opção Compression informa o tipo de compressão.

clip_image024

 

Dessa maneira é mais fácil de manter banco de dados compactado e ajuda a otimizar e reduzir tempo do seu plano de manutenção.

É isso ai, até a próxima!

Maníaco, entusiasta, fascinado, fanático por SQL Server e nas horas vagas um DBA que adora o que faz! Também possui certificações como: MCT, MCSE - Data Management and Analystics, MCSA - SQL Server 2014/2012

Responda