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

 

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

Vamos aplicar o Row Compression Level agora.

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

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

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

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.

Agora aplicamos o Page Compression Level

 

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;

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

O Assistente irá aparecer.

 

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!

 

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

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

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

 

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

 

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.

 

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!