A compactação de linha e página para tabelas e índices permite economizar espaço de armazenamento reduzindo o tamanho do banco de dados. A compactação de dados tem a desvantagem de aumentar o uso da CPU porque os dados devem ser compactados e descompactados ao serem acessados.
Você não pode usar a compactação de dados com tabelas do sistema e apenas as edições Enterprise e Developer do SQL Server oferecem suporte à compactação de dados.
Você pode configurar a compactação de dados no seguintes objetos:
- Tabelas agrupadas
- Tabelas de heap (heap é uma tabela sem índice clusterizado)
- Índices não clusterizados
- Visualizações indexadas
- Partições individuais de uma tabela ou índice particionado
Existem três formas de compactação de dados que você pode usar com o SQL Server: compactação em nível de linha, compactação Unicode e compactação em nível de página.
Compressão em nível de linha
A compactação em nível de linha funciona usando formatos de armazenamento mais eficientes para dados de comprimento fixo.
A compactação em nível de linha usa a estratégia para economizar espaço:
Armazenamento de tipos de dados numéricos de comprimento fixo e tipos de dados CHAR como se fossem tipos de dados de comprimento variável
Não armazenando valores NULL ou 0 Reduzindo os metadados necessários para armazenar dados.
Embora reduza a quantidade de espaço que os dados usam, a compactação em nível de linha não fornece as melhorias de armazenamento da compactação em nível de página. A vantagem da compactação em nível de linha é que ela requer menos uso da CPU do que a compactação em nível de página. Use a seguinte sintaxe para compactar uma tabela com compactação em nível de linha:
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)
Por exemplo, para reconstruir todas as partições da tabela rh.empregados do banco de dados EMPRESA usando a compactação de linha, use a seguinte consulta:
USE EMPRESA
ALTER TABLE rh.empregados REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)
Use a seguinte sintaxe para configurar um índice com compactação em nível de linha:
ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)Compressão Unicode
A compactação Unicode permite que o mecanismo de banco de dados comprima valores Unicode armazenados em objetos compactados de página ou linha. Você pode usar a compactação Unicode com os tipos de dados nchar (n) e nvarchar (n) de comprimento fixo. A compactação Unicode é usada automaticamente quando apropriado quando você ativa a compactação de linha e página.
Compressão de nível de página
A compactação no nível da página compacta os dados armazenando valores repetidos e prefixos comuns apenas uma vez e, em seguida, fazendo referências a esses valores de outros locais na tabela. Quando a compactação de página é aplicada a uma tabela, as técnicas de compactação de linha também são aplicadas. A compactação no nível da página usa as seguintes estratégias:
A compactação em nível de linha é aplicada para maximizar o número de linhas armazenadas em uma página.
A compactação de prefixo de coluna é aplicada substituindo padrões de dados repetidos por referências. Esses dados são armazenados no cabeçalho da página.
A compactação de dicionário verifica os valores repetidos e armazena essas informações no cabeçalho da página.
Os benefícios da compactação de página dependem do tipo de dados compactados. Os dados que envolvem muitos valores repetidos serão mais compactados do que os dados preenchidos por valores mais exclusivos. Você usa a seguinte sintaxe geral para aplicar a compactação no nível da página:
ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE)
Por exemplo, para reconstruir todas as partições da tabela rh.empregados do banco de dados EMPPRESA usando a compactação de página, use a seguinte consulta:
USE EMPRESA
ALTER TABLE rh.empregados REBUILD PARTITION = ALL
WITH(DATA_COMPRESSION = PAGE)
Use a seguinte sintaxe para configurar um índice com compactação no nível da página:
ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE)
Se as tabelas ou índices forem particionados, você pode configurar a compactação por partição. Se você dividir uma partição usando a instrução ALTER PARTITION, as novas partições herdam o atributo de compactação de dados da partição original. Se você mesclar duas partições, a partição resultante terá o atributo de compactação da partição de destino. Embora a compactação permita que mais linhas sejam armazenadas em uma página, ela não altera o tamanho máximo da linha de uma tabela ou índice. Você não pode habilitar uma tabela para compactação se o tamanho máximo da linha e a sobrecarga de compactação excederem 8.060 bytes. A configuração de compactação padrão para índices é NONE e você deve especificar a propriedade de compactação para índices ao criá-los. Índices não clusterizados não herdam a propriedade de compressão da tabela, mas os índices clusterizados criados em um heap herdam o estado de compactação do heap. A compactação de dados se aplica apenas na origem, portanto, ao exportar dados de uma origem compactada, o SQL Server produzirá os dados em formato de linha não compactado. A importação de dados descompactados para uma tabela de destino habilitada para compactação compactará os dados.
Para alterar as configurações de compactação para tabelas e índices usando o assistente de compactação de dados, execute as seguintes etapas:
Inicie o SQL Server Management Studio.
Clique com o botão direito na tabela ou índice que deseja compactar, clique em Armazenamento e clique em Gerenciar compactação .
A caixa de diálogo Bem-vindo ao Assistente de compactação de dados é exibida.
A caixa de diálogo Selecionar tipo de compactação é exibida.
Clique em Avançar
Escolha o tipo de compressão.
Você pode escolher usar o mesmo tipo de compactação para todas as partições ou escolher entre linha, página e nenhuma por partição.
Clique em Calcular para determinar a diferença entre o uso de espaço atual e o uso compactado.
A caixa de diálogo Selecionar uma opção de saída é exibida.
Escolha se deseja criar um script, executar a operação imediatamente ou executar a opção de acordo com uma programação.
Clique em Avançar e depois em Concluir .
Estimando compressão
A melhor maneira de determinar os benefícios da compactação em um objeto é usar o procedimento armazenado sp_estimate_data_compression_savings.
Os benefícios da compactação dependem de fatores como a exclusividade dos dados. O procedimento armazenado sp_estimate_data_compression_savings está disponível apenas na edição Enterprise do SQL Server.
A sintaxe do procedimento armazenado é a seguinte:
sp_estimate_data_compression_savings[ @schema_name = ] ‘schema_name’, [ @object_name = ]
‘object_name’, [@index_id = ] index_id,[@partition_number = ] partition_number,
[@data_compression = ] ‘data_compression’Por exemplo, para configurar uma estimativa dos benefícios da compactação do uso da compactação de linha na tabela rh.empregados no banco de dados EMPRESA, execute a seguinte instrução Transact-SQL:
USE EMPRESA;
GO
EXEC sp_estimate_data_compression_savings ‘rh’, ’empregados’, NULL, NULL,
‘ROW’;
GO
Para configurar uma estimativa dos benefícios da compactação do uso da compactação de página na mesma tabela, execute a seguinte instrução Transact-SQL:
USE EMPRESA;
GO
EXEC sp_estimate_data_compression_savings ‘rh’, ’empregados’, NULL, NULL,
‘PAGE’;
GO
Você pode usar o script SQL a seguir para as tabelas e índices do usuário rh e para gerar as instruções SQL para definir a compactação da página para as tabelas e índices.
–Listar todos os indexes
SELECT USER_NAME(o.uid) [owner], OBJECT_NAME(i.id) [table], i.name [index],o.type [type]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = ‘rh’
AND o.type <> ‘S’ and o.type = ‘U’ and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO
–Verificar Storage de Schema
USE EMPRESA;
GO
–List all tables
SELECT USER_NAME(o.uid) [owner], o.name,o.id,o.type,o.status
FROM sysobjects o
WHERE USER_NAME(o.uid) = ‘rh’
AND o.type <> ‘S’ and o.type = ‘U’
ORDER BY o.name,o.type;
GO–-Geração de script para setar comprssão de pagina
SELECT ‘ALTER INDEX ‘ + i.name + ‘ ON ‘ + USER_NAME(o.uid) + ‘.’ + OBJECT_NAME(i.id) + ‘ REBUILD WITH ( DATA_COMPRESSION = PAGE );’ [TXTSQL]
FROM sysindexes i inner join sysobjects o ON i.id = o.id WHERE USER_NAME(o.uid) = ‘rh’
AND i.name NOT LIKE ‘_WA%’
–AND o.type <> ‘S’ and o.type = ‘U’ and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name; GO-– Comprensão de Index de Pagina
ALTER INDEX R125_pk
ON rh.empregados
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO–- Gerar script para comprensão de tabela
SELECT ‘ALTER TABLE ‘ + USER_NAME(o.uid) + ‘.’ + o.name + ‘ REBUILD WITH (DATA_COMPRESSION = PAGE);’ [TXTSQL]
FROM sysobjects o
WHERE USER_NAME(o.uid) = ‘rh’
AND o.type <> ‘S’ and o.type = ‘U’
ORDER BY o.name,o.type;
GO
