Compactação de dados

Artigo
11/18/2015

O SQL Server 2012 oferece suporte à compactação de linha e de página para tabelas e índices. Você pode usar o recurso de compactação de dados para ajudar a compactar os dados dentro de um banco de dados e ajudar reduzir o tamanho do banco de dados. Além de economizar espaço, a compactação de dados pode ajudar a aprimorar o desempenho de cargas de trabalho intensivas de E/S, pois os dados são armazenados em menos páginas e as consultas precisam ler menos páginas do disco. No entanto, recursos extras de CPU são necessários no servidor de banco de dados para compactar e descompactar os dados, enquanto os dados são trocados com o aplicativo. A compactação de dados pode ser configurada para os seguintes objetos de banco de dados:

Uma tabela inteira que é armazenada como um heap.
Uma tabela inteira que é armazenada como um índice clusterizado.
Um índice não clusterizado inteiro.
Uma exibição indexada inteira.
Para tabelas e índices particionados, a opção de compactação pode ser configurada para cada partição e as várias partições de um objeto não precisam ter a mesma configuração de compactação.

Considerações sobre quando usar a compactação de linha e de página

Ao usar compactação de linha e de página, esteja atento às seguintes considerações:

Os detalhes de compactação de dados estão sujeitos a alteração sem aviso em service packs ou versões subsequentes.
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 2012.
A compactação não está disponível para tabelas do sistema.
A compactação pode permitir que mais linhas sejam armazenadas em uma página, mas não altera o tamanho máximo de linha de uma tabela ou de um índice.
Uma tabela não pode ser habilitada para compactação quando o tamanho máximo da linha mais a sobrecarga de compactação exceder o tamanho máximo de linha de 8060 bytes. Por exemplo, uma tabela que tem as colunas c1 char(8000) e c2 char(53) não pode ser compactada devido à sobrecarga de compactação adicional. Quando o formato de armazenamento vardecimal é usado, a verificação do tamanho da linha é executada quando o formato é habilitado. Para a compactação de linha e de página, a verificação do tamanho da linha é executada quando o objeto é inicialmente compactado e, depois, verificado à medida que cada linha é inserida ou modificada. A compactação impõe as duas regras seguintes:
- Uma atualização para um tipo de comprimento fixo sempre deve ter êxito.
- A desabilitação da compactação de dados sempre deve ter êxito. Mesmo que a linha compactada caiba em uma página, o que significa que ela é menor do que 8060 bytes, o SQL Server impedirá atualizações que talvez não caibam na linha quando ela for descompactada.
Quando uma lista de partições é especificada, o tipo de compactação deve ser definido como ROW, PAGE ou NONE em partições individuais. Se a lista de partições não for especificada, todas as partições serão definidas com a propriedade de compactação de dados especificada na instrução. Quando uma tabela ou índice é criado, a compactação dos dados é definida como NONE, a menos que especificada de outra maneira. Quando uma tabela é modificada a compactação existente é preservada, a menos que especificada de outra maneira.
Se for especificada uma lista de partições ou uma partição fora do intervalo, um erro será gerado.
Índices não clusterizados não herdam a propriedade de compactação da tabela. Para compactar índices, você deve definir explicitamente a propriedade de compactação dos índices. Por padrão, a configuração de compactação de índices será definida como NONE quando o índice for criado.
Quando um índice clusterizado é criado em um heap, ele herda o estado de compactação do heap, a menos que um estado de compactação alternativo seja especificado.
Quando um heap é configurado para compactação em nível de página, as páginas só recebem compactação em nível de página nos seguintes modos:
- Os dados são importados em massa com otimizações em massa habilitadas.
- Os dados são inseridos usando INSERT INTO ... A sintaxe WITH (TABLOCK) e a tabela não têm um índice não clusterizado.
- Uma tabela é recriada executando ALTER TABLE ... Instrução REBUILD com a opção de compactação PAGE.
As novas páginas alocadas em um heap como parte de operações DML não usarão a compactação PAGE até o heap ser recompilado. Recompile o heap removendo e reaplicando a compactação ou criando e removendo um índice clusterizado.
A alteração da configuração de compactação de um heap exige que todos os índices não clusterizados na tabela sejam recriados, para que tenham ponteiros para os novos locais de linha no heap.
Você pode habilitar ou desabilitar a compactação de ROW ou PAGE online ou offline. A habilitação da compactação em um heap tem thread único para uma operação online.
Os requisitos de espaço em disco para habilitar ou desabilitar a compactação de página ou de linha são os mesmos que para criar ou recriar um índice. Para dados particionados, você pode reduzir o espaço exigido para habilitar ou desabilitar a compactação para uma partição de cada vez.
Para determinar o estado de compactação das partições em uma tabela particionada, consulte a coluna data_compression da exibição do catálogo sys.partitions.
Quando você estiver compactando índices, as páginas de nível folha poderão ser compactadas com a compactação de linha e de página. As páginas que não são de nível folha não recebem a compactação de página.
Devido ao seu tamanho, os tipos de dados de valor grande são, às vezes, armazenados separadamente dos dados de linhas normais em páginas com finalidades específicas. A compactação de dados não está disponível para os dados armazenados separadamente.

As tabelas que implementaram o formato de armazenamento vardecimal no SQL Server 2005 reterão essas configurações quando forem atualizadas. Você pode aplicar a compactação de linha a uma tabela que tenha o formato de armazenamento vardecimal. Entretanto, como a compactação de linha é um superconjunto de formato de armazenamento vardecimal, não há motivo para reter esse formato. Os valores decimais não ganham compactação adicional quando você combina o formato de armazenamento vardecimal com a compactação de linha. Só é possível aplicar a compactação de página a uma tabela com formato de armazenamento vardecimal; entretanto, as colunas de formato de armazenamento vardecimal provavelmente não alcançarão a compactação adicional.

Observação
O SQL Server 2012 oferece suporte ao formato de armazenamento vardecimal; porém, como a compactação em nível de linha alcança as mesmas metas, o formato de armazenamento vardecimal é preterido. Esse recurso será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

Como a compactação afeta tabelas e índices particionados

Ao usar a compactação de dados com tabelas e índices particionados, esteja atento às seguintes considerações:

Quando as partições são divididas usando a instrução ALTER PARTITION, ambas as partições herdam o atributo de compactação de dados da partição original.
Quando duas partições são mescladas, a partição resultante herda o atributo de compactação de dados da partição de destino.
Para alternar uma partição, a propriedade de compactação de dados da partição deve corresponder à propriedade de compactação da tabela.
Há duas variações de sintaxe que podem ser usadas para modificar a compactação de uma tabela ou índice particionado:
- A sintaxe seguinte só recria a partição referenciada:
```
ALTER TABLE <table_name> 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  <option>)
```
- A sintaxe seguinte recria a tabela inteira usando a configuração de compactação existente para qualquer partição não referenciada:
```
ALTER TABLE <table_name> 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
... )
```
Os índices particionados seguem o mesmo princípio usando ALTER INDEX.
Quando um índice clusterizado é descartado, as partições de heap correspondentes mantêm sua configuração de compactação de dados, a menos que o esquema de particionamento seja modificado. Se o esquema de particionamento for alterado, todas as partições serão recriadas para um estado não compactado. As etapas seguintes são necessárias para descartar um índice clusterizado e alterar o esquema de particionamento:

1. Descarte o índice clusterizado.

2. Modifique a tabela usando a opção ALTER TABLE ... REBUILD ... opção que especifica a opção de compactação.

Descartar um índice clusterizado OFFLINE é uma operação rápida porque apenas os níveis superiores dos índices clusterizados são removidos. Quando um índice clusterizado é descartado ONLINE, o SQL Server deve recriar o heap duas vezes, uma para a etapa 1 e outra para a etapa 2.

Como a compactação afeta a replicação

Ao usar a compactação de dados com replicação, esteja atento às seguintes considerações:

Quando o Agente de Instantâneo gera o script de esquema inicial, o novo esquema usará as mesmas configurações de compactação para a tabela e seus índices. A compactação não pode ser habilitada apenas na tabela e não no índice.
Para replicação transacional, a opção de esquema de artigo determina quais objetos e propriedades dependentes devem ser incluídos no script. Para obter mais informações, consulte sp_addarticle.

O Agente de Distribuição não verifica Assinantes de nível inferior ao aplicar scripts. Se a replicação de compactação for selecionada, haverá falha na criação da tabela em Assinantes de nível inferior. Em caso de topologia mista, não habilite a replicação de compactação.
Para replicação de mesclagem, o nível de compatibilidade da publicação substitui as opções de esquema e determina os objetos de esquema que serão incluídos no script.

Em caso de topologia mista, se não for exigido o suporte a novas opções de compactação, o nível de compatibilidade da publicação deverá ser definido para a versão de Assinante de nível inferior. Se for exigido, será necessário compactar as tabelas no Assinante depois que elas forem criadas.

A tabela a seguir mostra as configurações de replicação que controlam a compactação durante a replicação.

Intenção do usuário	Replicar esquema de partição para uma tabela ou um índice	Replicar configurações de compactação	Comportamento do script
Para replicar o esquema de partição e habilitar a compactação no Assinante da partição.	Verdadeiro	Verdadeiro	Gera scripts para o esquema da partição e as configurações de compactação.
Para replicar o esquema da partição, mas não compactar os dados no Assinante.	Verdadeiro	Falso	Gera script para o esquema da partição, mas não para as configurações de compactação da partição.
Para não replicar o esquema da partição e não compactar os dados no Assinante.	Falso	Falso	Não gera scripts para a partição nem para as configurações de compactação.
Para compactar a tabela no Assinante, se todas as partições forem compactadas no Publicador, mas não replicar o esquema de partição.	Falso	Verdadeiro	Verifica se todas as partições estão habilitadas para compactação. Gera scripts para a compactação em nível de tabela.

Como a compactação afeta outros componentes do SQL Server

A compactação ocorre no mecanismo de armazenamento e os dados são apresentados à maioria dos outros componentes do SQL Server em um estado não compactado. Isso limita os efeitos da compactação nos outros componentes para:

Operações de importação e exportação em massa

Quando os dados são exportados, mesmo em formato nativo, a saída dos dados é realizada no formato de linha descompactada. Isso pode fazer com que o tamanho do arquivo de dados exportado seja significativamente maior do que os dados de origem.

Quando os dados são importados, se a tabela de destino tiver sido habilitada para compactação, os dados serão convertidos pelo mecanismo de armazenamento em formato de linha compactada. Isso pode causar um aumento no uso da CPU se comparado à importação de dados em uma tabela descompactada.

Quando os dados são importados em massa para um heap com compactação de página, a operação de importação em massa tentará compactar os dados com a compactação de página quando eles forem inseridos.
A compactação não afeta o backup e a restauração.
A compactação não afeta o envio de logs.
A compactação de dados é incompatível com colunas esparsas. Portanto, as tabelas que contêm colunas esparsas não podem ser compactadas, assim como as colunas esparsas não podem ser adicionadas a uma tabela compactada.
A habilitação da compactação pode fazer com que os planos de consulta sejam alterados, porque os dados são armazenados usando um número diferente de páginas e um número de linhas por página.