Comprimindo todas as tabelas de um database no SQL Server

Neste post irei falar um pouco sobre o método de compressão de dados do SQL Server e como podemos utilizar esse recurso para compactar todos os objetos de um database. 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.

Lembrando que a compressão não está disponível para objetos de sistema e a configuração de compressão não é aplicada automaticamente a índices nonclustered. Sendo assim, 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.

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.

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: Já visto acima.
  • 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, diminuindo a quantidade de bytes utilizados.
  • 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.

Estimando o ganho 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;

SQL Server - Page Compression

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
SQL Server - Page Compression

Será aberta a tela do assistente de compressão
SQL Server - Page Compression 3

Na próxima tela do assiste, você pode testar o tipo de compressão e antes de aplicar você pode calcular o espaço para verificar qual obteve a maior taxa de compressão de dados.
SQL Server - Page Compression 4

Seguindo com o assistente de compressão, escolhi a opção PAGE, que geralmente obtém o melhor resultado, e em seguida irá perguntar o que fazer: Apenas gerar o script, executar a compactação ou agendar a alteração para ativar a compactação.
SQL Server - Page Compression 5

Por fim, será mostrado na tela uma mensagem de sucesso na compressão dos dados
SQL Server - Page Compression 6

Você pode verificar todos os seus objetos e quais níveis de compressão eles estão clicando com o botão direito em cima do objeto, na guia Storage, a opção Compression informa o tipo de compressão ou utilizando o comando:
SQL Server - Page Compression 7

Aplicando compressão em um banco de dados inteiro

Nesse post já vimos o quão benéfica é a compressão de dados, principalmente para o storage. Pois bem, vamos colocar isso em prática. Criei uma procedure que permite compactar todos os objetos de um database, de forma fácil e rápida. O código da procedure está disponível aqui:

Exemplo de utilização:

Obs: Não recomendo utilizar a opção @Fl_Rodar_Shrink em ambientes de produção.

sql server, compactar tabela, compactar database, comprimir, compression, compact, shrink

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

Deixe uma resposta