Ative a compactação no nível de linha em todas as tabelas e índices

Question

Um dos requisitos (opcionais) para o teste do Works With SQL Server 2008 é que a compactação no nível de linha está habilitada em todas as tabelas e índices. Temos um banco de dados existente com muitas tabelas e índices já criados. Existe uma maneira fácil de ativar a compactação em todas essas tabelas e índices?

Aqui está o script que acabei fazendo a partir da recomendação do splattne.

select 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);'   
from   sysobjects   where  type = 'U' -- all user tables
UNION
select 'ALTER INDEX [' + k.name + '] ON [' + t.name + '] REBUILD WITH (DATA_COMPRESSION = ROW);'
from   sysobjects k
join sysobjects t on k.parent_obj = t.id
   where  k.type = 'K' -- all keys
    AND t.type = 'U' -- all user tables

    

Answer 1

Acabei de usar o Works With SQL Server Tool para testar após a compactação usando o script a_hardin-splattne. O teste falhou porque vários índices não foram compactados.

A visualização "sysobjects" inclui alguns, mas não todos os índices. Precisamos de "sysindexes" em vez disso. Graças ao pôster anônimo em aspfaq.com para este insight do índice. Nós também queremos ignorar as funções definidas pelo usuário.

SELECT 'ALTER TABLE [' + name + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
FROM  sysobjects  WHERE type = 'U' -- all user tables
UNION
SELECT  'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(i.id) + '] REBUILD WITH (DATA_COMPRESSION = ROW);' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) 
FROM 
    sysindexes i 
    inner join sysobjects o on o.name = OBJECT_NAME(i.id)
WHERE 
    (i.indid BETWEEN 1 AND 254) 
    AND (i.Status & 64)=0 
    AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0 
    AND NOT o.type in ('TF','FN')

    

Answer 2

Você pode usar este script SQL simples para criar outro script que deve fazer o trabalho:

   select 'ALTER TABLE ' + name + ' REBUILD WITH (DATA_COMPRESSION = ROW)' 
         + CHAR(13) + CHAR(10) + 'GO'
   from   sysobjects
   where  type = 'U' -- all user tables

(Eu não testei isso, mas deve funcionar.)

Você pode encontrar um script muito mais sofisticado aqui no site SQLServerBible (procure por "db_compression procs".) Leia publicação do blog do autor "Banco de dados inteiro - Procs de compactação de dados" .

    

Answer 3

Como um aparte, tenha cuidado ao permitir que tudo seja comprimido. Os dados são compactados na memória e descompactados a cada hora em que são acessados. Para um sistema OLTP com muitas alterações e dados residentes na memória, a compactação não é adequada, pois você queima mais CPU sem ganhos em IOs. Para dados que são lidos ocasionalmente, como um data warehouse, é muito mais adequado porque você obtém uma grande compensação em I / Os reduzidos em relação à CPU extra. A compactação é um recurso de data warehousing, não um recurso OLTP. Não tenho certeza se isso se aplica a você, mas vale a pena apontar just-in-case e para outros que estão lendo o tópico.

Um outro ponto - pode ser que você não tenha um ganho significativo com a compactação, por isso não vale a pena. Prática recomendada para verificar o ganho de compactação antes de ativar o uso do sp_estimate_data_compression_savings stored-proc.

Obrigado

    

Answer 4

Provavelmente, você também deve procurar manipular novas tabelas, portanto, não é necessário executar esse lote regularmente. Eu detalhei um método para compactar automaticamente novas tabelas em este post de blog .

Eu também mencionaria que você deve verificar se a tabela está ou não compactada antes de reconstruí-la.

    

Answer 5

Estou um pouco atrasado para a festa, mas aqui está uma versão que usa DMVs em vez das tabelas de sistema obsoletas e permite nomes de esquemas arbitrários. Ele ativa ou desativa a compactação de linhas ou páginas em todos os heaps, índices clusterizados e índices não-clusterizados (incluindo todas as tabelas particionadas) no banco de dados atual:

-- Enables or disables compression on all tables in the database
DECLARE @Compression NVARCHAR(4) = 'PAGE' -- NONE, ROW or PAGE
    , @Cmd NVARCHAR(MAX) = '';

-- Clustered indexes, heaps
SELECT @Cmd +=  '
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD ' + CASE WHEN p.[partition_number] > 1 THEN 'PARTITION = ALL ' ELSE '' END + 'WITH (DATA_COMPRESSION = ' + @Compression + ');'
FROM sys.schemas s
    INNER JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
    INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
WHERE p.[data_compression_desc] <> @Compression
    AND p.index_id IN (0, 1)
    AND NOT EXISTS (
        SELECT 1
        FROM sys.partitions d
        WHERE d.[object_id] = p.[object_id]
            AND d.index_id = p.index_id
            AND d.[partition_number] > p.[partition_number]
    );

-- Nonclustered indexes
SELECT @Cmd +=  '
ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' REBUILD ' + CASE WHEN p.[partition_number] > 1 THEN 'PARTITION = ALL ' ELSE '' END + 'WITH (DATA_COMPRESSION = ' + @Compression + ');'
FROM sys.schemas s
    INNER JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
    INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
    INNER JOIN sys.indexes i ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
WHERE p.[data_compression_desc] <> @Compression
    AND p.index_id > 1
    AND NOT EXISTS (
        SELECT 1
        FROM sys.partitions d
        WHERE d.[object_id] = p.[object_id]
            AND d.index_id = p.index_id
            AND d.[partition_number] > p.[partition_number]
    );

-- Review commands
SELECT @Cmd;

-- Run commands
--EXEC sp_executesql @Cmd;