Compactação de dados

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

O SQL Server, o Banco de Dados SQL do Azure e a Instância Gerenciada de SQL do Azure dão suporte à compactação de linhas e de páginas para tabelas e índices rowstore, e dão suporte a columnstore e à compactação de arquivamento columnstore para tabelas e índices columnstore.

Para tabelas e índices rowstore, use o recurso de compactação de dados para ajudar a 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. Você pode configurar a compactação de linha e página nos 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, você pode configurar a opção de compactação para cada partição, e as várias partições de um objeto não precisam ter a mesma configuração de compactação.

Para tabelas e índices de columnstore, todas as tabelas e índices de columnstore sempre usam a compactação de columnstore, e isso não é configurável pelo usuário. Use a compactação de arquivamento columnstore para reduzir ainda mais o tamanho dos dados em situações em que tenha tempo extra e recursos de CPU para armazenar e recuperar os dados. Você pode configurar a compactação de arquivamento columnstore nos seguintes objetos de banco de dados:

  • Uma tabela columnstore inteira ou um índice columnstore clusterizado inteiro. Desde que uma tabela columnstore é armazenada como um índice columnstore clusterizado, ambas as abordagens têm os mesmos resultados.
  • Um índice columnstore não clusterizado inteiro.
  • Para tabelas columnstore particionadas e índices columnstore, você pode configurar a opção de compactação de arquivamento para cada partição, e as várias partições não precisam ter a mesma configuração de compactação de arquivamento.

Observação

Os dados também podem ser compactados usando o formato de algoritmo GZIP. Essa é uma etapa adicional e é mais adequada para compactar partes dos dados ao arquivar dados antigos para armazenamento de longo prazo. Dados compactados usando a função COMPRESS não podem ser indexados. Para obter mais informações, consulte COMPRESS (Transact-SQL).

Considerações sobre 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 está disponível no Banco de Dados SQL do Azure

  • A compactação não está disponível em todas as edições do SQL Server. Veja mais informações na lista de edições e recursos compatíveis ao final desta seção.

  • A compactação não está disponível para tabelas de sistema.

  • A compactação pode permitir que mais linhas sejam armazenadas em uma página, mas não altera o tamanho máximo da linha de uma tabela ou 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 excede o tamanho máximo de linha de 8060 bytes. Por exemplo, uma tabela que tenha as colunas c1 CHAR(8000) e c2 CHAR(53) não pode ser compactada devido à sobrecarga adicional de compactação. Quando o formato de armazenamento vardecimal é usado, a verificação do tamanho da linha é realizada 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.
  • Os dados fora da linha não são compactados ao habilitar a compactação de dados. Por exemplo, um registro XML com mais de 8060 bytes usa páginas fora da linha, que não são compactadas.

  • Vários tipos de dados não são afetados pela compactação de dados. Para obter mais detalhes, confira Como a compactação de linha afeta o armazenamento.

  • Quando uma lista de partições é especificada, o tipo de compactação pode 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.

  • Os í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 é definida como NONE quando o índice é 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 a sintaxe INSERT INTO ... WITH (TABLOCK) e a tabela não tem um índice não clusterizado.
    • Uma tabela é recriada executando a instrução ALTER TABLE ... REBUILD com a opção de compactação PAGE.
  • As novas páginas alocadas em um heap como parte das operações DML não usarão a compactação PAGE até que o heap seja reconstruído. 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 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 de catálogo sys.partitions.

  • Quando você compactar í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 estão no nível da folha não recebem 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 dados que são armazenados separadamente.

  • As tabelas que implementaram o formato de armazenamento vardecimal no SQL Server 2005 (9.x) retêm essas configurações quando forem atualizadas. Você pode aplicar a compactação de linhas a uma tabela que tenha o formato de armazenamento vardecimal. No entanto, como a compactação de linhas é um superconjunto do formato de armazenamento vardecimal, não há motivo para manter o formato de armazenamento vardecimal. Valores decimais não ganham compressão adicional quando você combina o formato de armazenamento vardecimal com a compressão de linha. Você pode aplicar a compactação de página a uma tabela que tenha o formato de armazenamento vardecimal. No entanto, as colunas do formato de armazenamento vardecimal provavelmente não recebem compactação adicional.

    Observação

    Todas as versões compatíveis do SQL Server dão suporte ao formato de armazenamento vardecimal; porém, como a compactação de dados alcança as mesmas metas, o formato de armazenamento vardecimal é preterido. Esse recurso será removido em uma versão futura do SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam.

Veja uma lista dos recursos compatíveis com as edições do SQL Server em:

Compactação columnstore e de arquivamento columnstore

As tabelas e os índices columnstore são sempre armazenados com a compactação columnstore. Você pode reduzir ainda mais o tamanho dos dados de columnstore configurando um compactação adicional denominada compactação de arquivamento. Para executar a compactação de arquivamento, o SQL Server executa o algoritmo de compactação XPRESS da Microsoft nos dados. Adicione ou remova a compactação de arquivamento usando os seguintes tipos de compactação de dados:

  • Use a compactação de dados COLUMNSTORE_ARCHIVE para compactar os dados de columnstore com a compactação de arquivamento.
  • Use a compactação de dados COLUMNSTORE para descompactar a compactação de arquivos. Esses dados resultantes continuam a ser compactados com a compactação columnstore.

Para adicionar compactação de arquivamento, use ALTER TABLE (Transact-SQL) ou ALTER INDEX (Transact-SQL) com a opção REBUILD e DATA COMPRESSION = COLUMNSTORE_ARCHIVE.

Por exemplo:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4)
);

Para remover a compactação de arquivamento e restaurar os dados à compactação columnstore, use ALTER TABLE (Transact-SQL) ou ALTER INDEX (Transact-SQL) com a opção REBUILD e DATA COMPRESSION = COLUMNSTORE.

Por exemplo:

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = 1 WITH (
     DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
);

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (2, 4)
);

O exemplo a seguir define a compactação de dados para columnstore em algumas partições, e para o arquivamento columnstore em outras partições.

ALTER TABLE ColumnstoreTable1
REBUILD PARTITION = ALL WITH (
    DATA_COMPRESSION = COLUMNSTORE
        ON PARTITIONS (4, 5),
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE
        ON PARTITIONS (1, 2, 3)
);

Desempenho

Quando você comprime índices de columnstore com a compactação de arquivo, isso faz com que o índice tenha um desempenho mais lento do que os índices de columnstore que não têm a compactação de arquivo. Use a compactação de arquivamento apenas quando tiver tempo extra e recursos de CPU para compactar e recuperar os dados.

O benefício da compactação de arquivos é a redução do armazenamento, o que é útil para dados que não são acessados com frequência. Por exemplo, se você tiver uma partição para cada mês de dados, e a maior parte da atividade for para os meses mais recentes, você poderá arquivar os meses anteriores para reduzir os requisitos de armazenamento.

Metadados

As seguintes exibições do sistema contêm informações sobre compactação de dados para índices clusterizados:

O procedimento sp_estimate_data_compression_savings (Transact-SQL) também pode se aplicar a índices columnstore.

Impacto nas tabelas e nos í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 a seguir reconstrói a tabela inteira usando a configuração de compactação existente para todas as partições que não são referenciadas:

      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 que especifica a opção de compactação.

    Para remover um índice clusterizado, OFFLINE é uma operação rápida, pois somente os níveis superiores dos índices clusterizados são removidos. Quando um índice clusterizado é removido ONLINE. o SQL Server recria 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 usa 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 a replicação transacional, a opção de esquema de artigo determina quais objetos e propriedades dependentes devem ter scripts definidos. Para obter mais informações, veja sp_addarticle.

    O Agente de Distribuição não verifica os 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 uma topologia mista, não habilite a replicação da 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 são incluídos no script.

    Em uma topologia mista, se não for necessário oferecer suporte às novas opções de compactação, o nível de compatibilidade da publicação deverá ser definido como a versão de nível inferior do Assinante. 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.
Não replicar o esquema de partição e não compactar os dados no Assinante. Falso Falso Não define scripts das configurações de partição ou 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 True Verifica se todas as partições estão habilitadas para compactação.

Gera scripts para a compactação em nível de tabela.

Efeito em outros componentes do SQL Server

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

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 compressão sobre os outros componentes aos seguintes fatores:

  • 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, o Mecanismo de Banco de Dados converterá os dados 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 tenta 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, nem as colunas esparsas 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.