Criando tabelas e índices compactados

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çã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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Consulte também

Referência