Compartilhar via

  • Artigo

Criando tabelas e índices compactados

O SQL Server 2008 oferece suporte à compactação de linha e de página para tabelas e índices. 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.

A configuração de compactação de uma tabela não é aplicada automaticamente a seus índices não clusterizados. Cada índice deve ser definido individualmente. A compactação não está disponível para tabelas do sistema. As tabelas e os índices podem ser compactados quando são criados usando as instruções CREATE TABLE e CREATE INDEX. Para alterar o estado de compactação de uma tabela, índice ou partição, use as instruções ALTER TABLE ou ALTER INDEX.

ObservaçãoObservação

Se os dados existentes estiverem fragmentados, é possível reduzir o tamanho do índice ao recriá-lo, em vez de usar a compactação. O fator de preenchimento de um índice será aplicado durante uma recriação de índice, o que pode aumentar potencialmente o tamanho do índice. Para obter mais informações, consulte Fator de preenchimento.

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:

  • A compactação só está disponível nas edições Enterprise e Developer do SQL Server 2008.

  • 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 tenha as colunas c1char(8000) e c2char(53) não pode ser compactada em razão da sobrecarga da 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 recebem compactação em nível de página apenas dos seguintes modos:

    • Os dados são importados em massa com as otimizações em massa habilitadas.

    • Os dados são inseridos usando a sintaxe INSERT INTO ... WITH (TABLOCK).

    • 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 com 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çãoObservação

    O SQL Server 2008 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.

Implementação da compactação

Para um resumo da implementação da compactação de dados, consulte Implementação da compactação de linha e Implementação da compactação de página.

Calculando o aumento da compactação

Para determinar como a alteração do estado de compactação afeta uma tabela ou um índice, use o procedimento armazenado sp_estimate_data_compression_savings. O procedimento armazenado sp_estimate_data_compression_savings está disponível somente nas edições do SQL Server que oferecem suporte à compactação de dados.

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:

  • Dividindo um intervalo

    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.

  • Mesclando um intervalo

    Quando duas partições são mescladas, a partição resultante herda o atributo de compactação de dados da partição de destino.

  • Alternando partições

    Para alternar uma partição, a propriedade de compactação de dados da partição deve corresponder à propriedade de compactação da tabela.

  • Recriando uma partição ou todas as partições

    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.

  • Descartando um índice clusterizado particionado

    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.

    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 Snapshot Agent 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 Distribution Agent 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. Para obter mais informações sobre nível de compatibilidade, consulte Usando várias versões do SQL Server em uma topologia de replicação.

    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 não é compatível com as 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, pois os dados são armazenados usando um número diferente de páginas e um número de linhas por página.

  • A compactação de dados é suportada pelo SQL Server Management Studio por meio do Assistente de Compactação de Dados.

Para iniciar o Assistente de Compactação de Dados

  • No Pesquisador de Objetos, clique com o botão direito do mouse em uma tabela, índice ou exibição indexada, aponte para Armazenamento e clique em Compactar.

Monitorando a compactação

Para monitorar a compactação de toda a instância do SQL Server, use os contadores Page compression attempts/sec e Pages compressed/sec do SQL Server, Access Methods Object.

Para obter estatísticas de compactação de página para partições individuais, consulte a função de gerenciamento dinâmico sys.dm_db_index_operational_stats.

Exemplos

Alguns dos exemplos a seguir usam tabelas particionadas e exigem um banco de dados com grupos de arquivos. Para criar um banco de dados que tenha grupos de arquivos, execute a instrução a seguir.

CREATE DATABASE TestDatabase
ON  PRIMARY
( NAME = TestDatabase,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO

Para alternar para o novo banco de dados:

USE TestDatabase
GO

A. Criando uma tabela que usa a compactação de linha

O exemplo a seguir cria uma tabela e define a compactação para ROW.

CREATE TABLE T1 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO

B. Criando uma tabela que usa a compactação de página

O exemplo a seguir cria uma tabela e define a compactação para PAGE.

CREATE TABLE T2 
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO

C. Configurando a opção DATA_COMPRESSION em uma tabela particionada

O exemplo a seguir usa a tabela TestDatabase criada ao usar o código fornecido anteriormente nesta seção. O exemplo cria uma função e um esquema de partição, depois, cria uma tabela particionada e especifica as opções de compactação para as partições da tabela. Nesse exemplo, a partição 1 é configurada para compactação de ROW e as partições restantes são configuradas para compactação de PAGE.

Para criar uma função de partição:

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO

Para criar um esquema de partição:

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO

Para criar uma tabela particionada que tenha partições compactadas:

CREATE TABLE PartitionTable1 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1),
  DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO

D. Configurando a opção DATA_COMPRESSION em uma tabela particionada

O exemplo a seguir usa o banco de dados usado no exemplo C. O exemplo cria uma tabela usando a sintaxe para partições não contíguas.

CREATE TABLE PartitionTable2 
(col1 int, col2 varchar(max))
ON myRangePS1 (col1) 
WITH 
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
  DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO

E. Modificando uma tabela para alterar a compactação

O exemplo a seguir altera a compactação da tabela não particionada criada no exemplo A.

ALTER TABLE T1 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

F. Modificando a compactação de uma partição em uma tabela particionada

O exemplo a seguir altera a compactação da tabela particionada criada no exemplo C. A sintaxe REBUILD PARTITION = 1 causa somente a recriação do número da partição 1.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;
GO

A mesma operação que usa a sintaxe alternada a seguir causa a recriação de todas as partições na tabela.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

G. Modificando a compactação de várias partições em uma tabela particionada

A sintaxe REBUILD PARTITION = ... só pode recriar uma partição. Para recriar mais de uma partição, é necessário executar várias instruções, ou executar o exemplo a seguir para recriar todas as partições, usando as configurações de compactação atuais para partições não especificadas.

ALTER TABLE PartitionTable1 
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO

H. Modificando a compactação em um índice

O exemplo a seguir usa a tabela criada no exemplo A e cria um índice na coluna C2.

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2) 
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

Execute o código seguinte para alterar o índice para compactação de página:

ALTER INDEX IX_INDEX_1 
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO

I. Modificando a compactação de uma única partição em um índice particionado

O exemplo a seguir cria um índice em uma tabela particionada que usa a compactação de linha em todas as partições do índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

Para criar o índice de forma que ele use configurações de compactação diferentes para partições diferentes, use a sintaxe ON PARTITIONS. O exemplo a seguir cria um índice em uma tabela particionada que usa compactação de linha na partição 1 da compactação de índice e de página nas partições de 2 a 4 do índice.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
    DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO

O exemplo a seguir altera a compactação do índice particionado.

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO

J. Modificando a compactação de várias partições em um índice particionado

A sintaxe REBUILD PARTITION = ... só pode recriar uma partição. Para recriar mais de uma partição, é necessário executar várias instruções ou executar o exemplo a seguir para recriar todas as partições, usando as configurações de compactação atuais para partições não especificadas.

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL 
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1), 
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4) 
) ;
GO