Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

Compressing all tables in a database in SQL Server

Post Views 11,684 views
Reading time 7 minutes

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

This compression feature takes into account the type of variable data structures that define a column. Row Compression Level is a compression level that does not use any compression algorithm.

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.
This type of compression uses the following techniques:

  • ROW COMPRESSION: Already seen above.
  • PREFIX COMPRESSION: For each column on a page duplicate prefixes are identified. These prefixes are stored in Information Compression (CI) headers, which reside after the page header. A reference number is assigned to these prefixes and this reference number is used wherever these prefixes are being used, decreasing the amount of bytes used.
  • DICTIONARY COMPRESSION: Looks for duplicate values off the page and stores them in the CI. The main difference between Prefix Compression and Dictionary Compression is that Prefix is restricted to just one column whereas Dictionary is applicable for the complete page.
    Once the Prefix Compression has completed, the Dictionary Compression is applied and searches for repeated values anywhere on the page and stores them in the CI area. Unlike Prefix Compression, Dictionary Compression is not constrained to a column and can replace repeated values, which occur anywhere on a page.

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:

Usage example:

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