Compartilhar via

Entendendo mais sobre Compressão de Dados em SQL Server

  • Artigo

-- THIS IS A STUB ARTICLE --
**
-- ESTE ARTIGO É UM RASCUNHO --**
 

Introdução

Perguntas referentes à performance no SQL Server são comuns nos Fóruns TechNet. Este artigo procura abordar o quanto a compactação de dados no SQL Server pode efetivamente reduzir o espaço físico consumido e como muda o comportamento para execução de simples consultas em um banco de dados.

É importante lembrar que este recurso surgiu no SQL Server 2008 e que os bancos de dados devem estar configurados no nível de compatibilidade 100 (ou maior) para que estas implementações tenham efeito.

Entender quais são suas características pode evitar que. ao invés de melhorar a performance de sua instância SQL ao aplicar a compressão de dados em uma ou mais tabelas, isso possa se tornar um problema de consumo de CPU em seu servidor ao compactar e descompactar os dados.

Definir o que pode ser Comprimido

Uma compressão de dados pode ser realizada por linha ou por página nos seguintes objetos de banco de dados:

  • Uma tabela inteira armazenada como um Heap
  • Uma tabela inteira que é armazenada com um Índice Clusterizado
  • Um Índice Não Clusterizado
  • Uma View Indexada
  • Tabelas e Índices particionados

Existem diversas formas de comprimir dados através do SQL Server, dependendo da necessidade é preciso definir o que pode ser comprimido e essa decisão normalmente é baseada em:

  • Economia de Espaço necessária
  • Quantidade de consumo dos dados

Um fator importante para analisar a relevância do que pode ser comprimido é o tipo de dados utilizado em cada coluna da tabela. Alguns tipos de dados, reduzem muito pouco o espaço e deste modo não são favoráveis para esta compactação.

Veja abaixo os tipos de dados mais comuns e seu comportamento ao ser comprimido:

 Tipo de Dados Armazenamento
 é afetado?		  Descrição
int SIM 1 byte é o armazenamento mínimo necessário.
float SIM Bytes menos significantes com zeros não são armazenados. A compactação float é aplicável principalmente a valores não fracionários
datetime SIM Usa a representação de dados de número inteiro usando números inteiros de 4 bytes. O valor de inteiro representa o número de dias com data base de 1/1/1900. Os primeiros 2 bytes podem representar até o ano 2079. A compactação sempre pode salvar 2 bytes aqui até esse ponto. Cada valor de inteiro representa 3,33 milissegundos. A compactação esvazia os primeiros 2 bytes nos primeiros cinco minutos e precisa do quarto byte após às 16h. Portanto, a compactação pode salvar apenas 1 byte depois das 16h. Quando datetime é compactado como qualquer outro inteiro, a compactação salva 2 bytes na data
date NÃO Usa a representação de dados de inteiro usando 3 bytes. Representa a data a partir de 1/1/0001. Para datas contemporâneas, a compactação de linha usa todos os 3 bytes. Não gera nenhum aumento.
char SIM Caracteres de preenchimento à direita são removidos. Observe que o Mecanismo de Banco de Dados insere o mesmo caractere de preenchimento, independentemente do agrupamento usado
varchar NÃO Nenhum efeito
text NÃO Nenhum efeito

Para informações sobre outros tipos de dados, veja a tabela completa do BOL.

Algumas informações devem ser conhecidas:

  • Em 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 configurações de compressão;
  • A compressão de dados não está disponível para objetos de sistema;
  • A compressão de dados não é aplicada automaticamente a índices não clusterizados, então eles devem ser configurados individualmente
Nota
A compactação não está disponível em todas as edições do SQL Server. Para obter mais informações, consulte Recursos compatíveis com as edições do SQL Server.

Tipos de Compressão

Executar uma compressão de dados em um objeto requer espaço para realocar o conteúdo, além de consumir mais recursos no servidor, tanto de CPU como em I/O em disco. Segue os tipos existentes até a versão atual do SQL Server:

Compressão de Página

A compressão de páginas para tabelas e índices possuem três operações executadas na seguinte ordem:

  1. Compressão de Linha
  2. Compressão de Prefixo
  3. Compressão de Dicionário

Compressão de Linha

Esta compressão altera somente o formato de armazenamento físico dos dados. O conteúdo de cada coluna é armazenado novamente com as seguintes alterações:

Compressão de Columnstore

Compressão de Objetos Particionados

A compressão de tabelas ou índices particionados é realizada do mesmo modo que a compressão de páginas.

Nota
Não é possível realizar a compressão de dados em colunas esparsas, assim como não é possível adicionar colunas esparsas em tabelas compactadas. Para obter mais informações, consulte Restrições para uso de Colunas Esparsas.

Estimando a Economia de Espaço

Existem dois caminhos para estimar a economia de armazenamento em tabelas e índices:

Analisar os resultados obtidos nestas avaliações podem determinar se é possível ganhar espaço e/ou performance. Em alguns casos, a situação utilizando a compressão poderá piorar a alocação de espaço, além de utilizar mais recursos do servidor para compactar e descompactar seus dados.

Usando o procedimento "sp_estimate_data_compression_savings"

Primeiro vamos demonstrar como utilizar o procedimento sp_estimate_data_compression_savings, indicando em seus parâmetros: o schema, o objeto (tabela ou view), o ID do índice (NULL para retornar todos os índices), o ID da Partição (NULL para retornar todas as partições) e o Tipo da Compressão (NONE, ROW ou PAGE);

Imagem 1 - Executando o procedimento, usando estimando a Compressão "ROW" (Clique na imagem para aumentar)

Abaixo segue o script T-SQL

EXEC sp_estimate_data_compression_savings 'dbo', 'TB_SAMPLE', NULL, NULL, 'ROW';
GO

Executando este procedimento é possível obter o tamanho atual de cada índice vinculado à tabela e qual é a estimativa de tamanho após executar a compactação. Com estes resultados é possível observar que alguns índices passarão a ter um volume maior após a compressão de dados (veja por exemplo o índice 20 da imagem) e nestes casos a compressão não é recomendada.

Usando o assistente "Data Compression Wizard"

Agora vamos utilizar o assistente Data Compression Wizard, que efetivamente realiza a mesma tarefa do procedimento *sp_estimate_data_compression_savings *porém utiliza os recursos da interface gráfica do SQL Server Management Studio (SSMS).

Neste opção, é necessário clicar com o botão direito na tabela a ser analisada e selecionar "Storage" e a seguir em "Manage Compression...". Veja na imagem abaixo:

Imagem 2 - Acessando o Assistente, através do SSMS

Você pode obter uma estimativa da quantidade de espaço que você vai economizar com esse tipo de compressão, clicando no botão Calcular e comparar os valores no espaço atual e espaço compactados.

Imagem 3 - Executando o Assistente, obtendo estimativas de Compressão para cada Partição (Clique na imagem para aumentar)

Imagem 4 - Trocando o Tipo de Compressão para comparação (Clique na imagem para aumentar)

Imagem 5 - Gerando um script T-SQL usando o Assistente (Clique na imagem para aumentar)

Abaixo segue o script T-SQL criado pelo Assistente

USE YourDB
GO

ALTER TABLE [dbo].[TB_SAMPLE] REBUILD PARTITION = 1 WITH(DATA_COMPRESSION = PAGE)
GO
ALTER TABLE [dbo].[TB_SAMPLE] REBUILD PARTITION = 2 WITH(DATA_COMPRESSION = ROW)
GO

Conclusão

Realizar a compressão de dados pode parecer uma excelente alternativa, mas é necessário realizar um estudo prévio para evitar que uma compressão mau planejada torne as consultas e a manutenção de seu banco de dados lentas, ocasionando transtornos desnecessários para os usuários.

Compreendendo os Tipos de Compressão existentes, quando e em quais objetos podem ser aplicados tornará o armazenamento e o desempenho de seu banco de dados muito mais eficientes.

Créditos

Este artigo foi criado após eu ser inspirado por Gokan Ozcifci e Jens Suessmeyer que me estimularam a entender que estamos produzindo artigos técnicos para profissionais altamente qualificados e em uma das plataformas mais prestigiadas do mundo, o TechNet Wiki. Obrigado!

Por favor, colabore melhorando este artigos apenas acrescentando idéias e sugestões. Sinta-se livre para editar este conteúdo.

Referências

Veja Também

Outros Idiomas