SQL Server - Como identificar e comprimir tabelas e índices sem compressão de dados

Visualizações: 2.710 views

Neste artigo

Tempo de Leitura: 6 minutos

Fala pessoal!
Nesse post eu vou compartilhar com vocês um script bem prático para identificar tabelas e índices sem compressão de dados e já aplicar a compressão de dados do tipo página nas tabelas retornadas.

Utilizando a consulta que vou compartilhar nesse artigo, vamos identificar as tabelas e índices no modo RowStore que não estão utilizando compressão de dados

Vale lembrar que até a versão 2016, esse recurso de compressão de dados era exclusivo da versão Enterprise (além da Trial e Developer), então se a sua versão for inferior ao 2016 e na edição Standard, você não irá conseguir utilizar a compressão de dados que descrevo nesse artigo.

Por quê usar compressão de dados

A compactação de dados oferece vários benefícios. Ela economiza espaço em disco e pode ajudar a melhorar o desempenho de determinadas cargas de trabalho.

Os benefícios da compactação de dados vêm ao custo de um maior uso da CPU para compactar e descompactar os dados, e por isso, é importante entender as características da carga de trabalho em uma tabela antes de decidir por uma estratégia de compactação.

A compactação de dados fornece flexibilidade em termos de níveis de compactação (linha ou página) e dos objetos que você pode compactar (tabela, índice, partição). Isso permite ajustar a compactação com base nas características dos dados e da carga de trabalho.

Outra vantagem importante da compactação de dados é que ela funciona de forma transparente para as aplicações, além de funcionar bem com outros recursos do SQL Server, como TDE e compactação de backup.

As páginas compactadas são mantidas como compactadas no disco e permanecem compactadas quando lidas na memória. Os dados são descompactados (não a página inteira, mas apenas os valores de dados de interesse) quando atendem a uma das seguintes condições:

Ele é lido para filtrar, classificar, juntar, como parte de uma resposta de consulta.
Ele é atualizado por um aplicativo.

Não há nenhuma cópia descompactada na memória da página compactada. A descompactação de dados consome a CPU.

No entanto, como os dados compactados usam menos páginas de dados, eles também economizam:

Physical I/O: Como a I/O física é cara do ponto de vista da carga de trabalho, a I/O física reduzida geralmente resulta em uma economia maior do que o custo adicional da CPU para compactar e descompactar os dados. Observe que a I/O física é salva porque um volume menor de dados é lido ou gravado no disco e porque mais dados podem permanecer armazenados em cache na memória do buffer pool.
Logical I/O (se os dados estiverem na memória): Como a I/O lógica consome CPU, a I/O lógica reduzida às vezes pode compensar o custo da CPU para compactar e descompactar os dados.

Para entender melhor o que é a compressão de dados, sugiro que leia o artigo Comprimindo todas as tabelas de um database no SQL Server.

O que é Row Compression Level

Esta característica de compressão leva em conta o tipo de estruturas de dados variáveis que definem uma coluna. Row Compression Level é um nível de compressão que não utiliza nenhum algoritmo de compressão.

O principal objetivo da Row Compression Level é reduzir o armazenamento de dados do tipo fixos, ou seja, quando você está habilitando compressão do tipo Row, você está apenas mudando o formato de armazenamento físico dos dados que estão associados a um tipo de dados.

Row Level Compression estende o formato de armazenamento vardecimal (integer, decimal, float, datetime, money, etc) por armazenar dados de todos os tipos de comprimento fixo em um formato de armazenamento de comprimento variável. Este tipo de compressão irá remover quaisquer bytes extras no tipo de dados fixo.

Os valores NULL e 0, em todos os tipos de dados, são otimizados para não ocupar nenhum byte.

Por exemplo, temos uma coluna CHAR(100), que está usando o Row Compression Level. Essa coluna só usará a quantidade de armazenamento definida pelos dados. Como assim? Vamos armazenar a frase “SQL Server 2008” na coluna. Essa frase contém apenas 15 caracteres e apenas esses 15 caracteres são armazenados, ao contrário dos 100 que foram definidos pela coluna, portanto, você tem uma economia de 85% no espaço de armazenamento.

O que é Page Compression Level

Nas versões anteriores do SQL Server, cada valor era armazenado na página, independentemente se o mesmo valor já havia aparecido na mesma coluna para algumas outras linhas em uma página. No SQL Server 2008, o valor redundante ou duplicado será armazenado apenas uma vez dentro da página e o será referenciado em todas as outras ocorrências, dessa forma temos o Page Compression Level.

Basicamente, o Page Compression Level é um superconjunto de compressão ROW, e leva em conta os dados redundantes em uma ou mais linhas em uma determinada página. Ele também usa a compactação de prefixo e dicionário.

O método Page Compression Level é mais inteligente, pois permite que os dados comuns sejam compartilhados entre as linhas de uma determinada página.
Esse tipo de compressão utiliza as seguintes técnicas:

ROW COMPRESSION: Já visto acima.
PREFIX COMPRESSION: Para cada coluna em uma página os prefixos duplicados são identificados. Estes prefixos são armazenados nos cabeçalhos de Compressão de Informação (CI), que residem após o cabeçalho da página. Um número de referência é atribuído a esses prefixos e esse número de referência é utilizado onde quer que esses prefixos estejam sendo usados, diminuindo a quantidade de bytes utilizados.
DICTIONARY COMPRESSION: Pesquisas por valores duplicados por fora da página e as armazena no CI. A principal diferença entre o Prefix Compression e Dictionary Compression é que o Prefix se restringe apenas a uma coluna enquanto Dictionary é aplicável para a página completa.
Depois que a Prefix Compression foi concluída, a Dictionary Compression é aplicada e pesquisas valores repetidos em qualquer lugar da página e os armazena na área de CI. Ao contrário da Prefix Compression, a Dictionary Compression não está restrita a uma coluna e pode substituir valores repetidos, que ocorrem em qualquer lugar em uma página.

Script para identificar tabelas e índices sem compressão de dados

Para identificar tabelas e índices sem compressão de dados e já aplicar a compressão dos dados, você pode utilizar o script abaixo:

SELECT DISTINCT 
    C.[name] AS [Schema],
    A.[name] AS Tabela,
    NULL AS Indice,
    'ALTER TABLE [' + C.[name] + '].[' + A.[name] + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' AS Comando
FROM 
    sys.tables                   A
    INNER JOIN sys.partitions    B   ON A.[object_id] = B.[object_id]
    INNER JOIN sys.schemas       C   ON A.[schema_id] = C.[schema_id]
WHERE 
    B.data_compression_desc = 'NONE'
    AND B.index_id = 0 -- HEAP
    AND A.[type] = 'U'
    
UNION
 
SELECT DISTINCT 
    C.[name] AS [Schema],
    B.[name] AS Tabela,
    A.[name] AS Indice,
    'ALTER INDEX [' + A.[name] + '] ON [' + C.[name] + '].[' + B.[name] + '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE)'
FROM 
    sys.indexes                  A
    INNER JOIN sys.tables        B   ON A.[object_id] = B.[object_id]
    INNER JOIN sys.schemas       C   ON B.[schema_id] = C.[schema_id]
    INNER JOIN sys.partitions    D   ON A.[object_id] = D.[object_id] AND A.index_id = D.index_id
WHERE
    D.data_compression_desc =  'NONE'
    AND D.index_id <> 0
    AND A.[type] IN (1, 2) -- CLUSTERED e NONCLUSTERED (Rowstore)
    AND B.[type] = 'U'
ORDER BY
    Tabela,
    Indice

SELECT DISTINCT

C.[name] AS [Schema],

A.[name] AS Tabela,

NULL AS Indice,

'ALTER TABLE [' + C.[name] + '].[' + A.[name] + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' AS Comando

FROM

sys.tables A

INNER JOIN sys.partitions B ON A.[object_id] = B.[object_id]

INNER JOIN sys.schemas C ON A.[schema_id] = C.[schema_id]

WHERE

B.data_compression_desc = 'NONE'

AND B.index_id = 0 -- HEAP

AND A.[type] = 'U'

UNION

SELECT DISTINCT

C.[name] AS [Schema],

B.[name] AS Tabela,

A.[name] AS Indice,

'ALTER INDEX [' + A.[name] + '] ON [' + C.[name] + '].[' + B.[name] + '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE)'

FROM

sys.indexes A

INNER JOIN sys.tables B ON A.[object_id] = B.[object_id]

INNER JOIN sys.schemas C ON B.[schema_id] = C.[schema_id]

INNER JOIN sys.partitions D ON A.[object_id] = D.[object_id] AND A.index_id = D.index_id

WHERE

D.data_compression_desc = 'NONE'

AND D.index_id <> 0

AND A.[type] IN (1, 2) -- CLUSTERED e NONCLUSTERED (Rowstore)

AND B.[type] = 'U'

ORDER BY

Tabela,

Indice

Resultado:

Compressão de dados Page ou Row?

A documentação da Microsoft (link aqui) nos indica algumas dicas para tentar identificar se o índice deve ser criado como page compression ou row compression.

Execute o script abaixo:

SELECT
    [o].[name] AS [Table_Name],
    [x].[name] AS [Index_Name],
    [i].[partition_number] AS [Partition],
    [i].[index_id] AS [Index_ID],
    [x].[type_desc] AS [Index_Type],
    [i].[leaf_update_count] * 100.0 / ([i].[range_scan_count] + [i].[leaf_insert_count] + [i].[leaf_delete_count] + [i].[leaf_update_count] + [i].[leaf_page_merge_count] + [i].[singleton_lookup_count]) AS [Percent_Update],
    [i].[range_scan_count] * 100.0 / ([i].range_scan_count + [i].leaf_insert_count + [i].leaf_delete_count + [i].leaf_update_count + [i].leaf_page_merge_count + [i].singleton_lookup_count) AS [Percent_Scan]
FROM
    [sys].dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS [i]
    JOIN [sys].[objects] AS [o] ON [o].[object_id] = [i].[object_id]
    JOIN [sys].[indexes] AS [x] ON [x].[object_id] = [i].[object_id] AND [x].[index_id] = [i].[index_id]
WHERE
    ([i].[range_scan_count] + [i].[leaf_insert_count] + [i].[leaf_delete_count] + [i].[leaf_update_count] + [i].[leaf_page_merge_count] + [i].[singleton_lookup_count]) <> 0
    AND OBJECTPROPERTY([i].[object_id], 'IsUserTable') = 1
ORDER BY
    [Percent_Update] ASC;

SELECT

[o].[name] AS [Table_Name],

[x].[name] AS [Index_Name],

[i].[partition_number] AS [Partition],

[i].[index_id] AS [Index_ID],

[x].[type_desc] AS [Index_Type],

[i].[leaf_update_count] * 100.0 / ([i].[range_scan_count] + [i].[leaf_insert_count] + [i].[leaf_delete_count] + [i].[leaf_update_count] + [i].[leaf_page_merge_count] + [i].[singleton_lookup_count]) AS [Percent_Update],

[i].[range_scan_count] * 100.0 / ([i].range_scan_count + [i].leaf_insert_count + [i].leaf_delete_count + [i].leaf_update_count + [i].leaf_page_merge_count + [i].singleton_lookup_count) AS [Percent_Scan]

FROM

[sys].dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS [i]

JOIN [sys].[objects] AS [o] ON [o].[object_id] = [i].[object_id]

JOIN [sys].[indexes] AS [x] ON [x].[object_id] = [i].[object_id] AND [x].[index_id] = [i].[index_id]

WHERE

([i].[range_scan_count] + [i].[leaf_insert_count] + [i].[leaf_delete_count] + [i].[leaf_update_count] + [i].[leaf_page_merge_count] + [i].[singleton_lookup_count]) <> 0

AND OBJECTPROPERTY([i].[object_id], 'IsUserTable') = 1

ORDER BY

[Percent_Update] ASC;

Quanto menor o valor da coluna “Percent_Update” ou quanto maior o valor da coluna “Percent_Scan”, melhor candidato esse índice é para utilizar a compressão de página.

Algumas informações para te ajudar a decidir:

A compactação de página procura padrões repetidos, conforme vimos no seu modo de funcionamento. Por isso, se seus dados não tiverem padrões repetidos, você não obterá muitos benefícios extras com a compactação de página. Você verá uma utilização extra da CPU, mas provavelmente não terá muito ganho de desempenho para valer a pena.
A sobrecarga da CPU da compactação de linha geralmente é mínima (geralmente menor ou igual a 10%). Se a compactação de linha resultar em economia de espaço e o sistema puder acomodar um aumento de 10% no uso da CPU, todos os dados deverão ser compactados por linha.
Em média, a compactação de linha leva 1.5 vezes o tempo de CPU usado para recriar um índice, enquanto a compactação de página leva de 2 a 5 vezes o tempo de CPU usado para recriar um índice.
Exemplos de tabelas que são boas candidatas para compactação de página são tabelas de log ou auditoria, que são escritas uma vez e raramente lidas.
Utilize compressão de página quando o objetivo for reduzir espaço em disco, pois a compressão de página geralmente irá comprimir mais do que a compressão por linha
Para obter a compressão máxima disponível, utilize a compressão COLUMNSTORE_ARCHIVE, mas ela não possui boa performance de leitura.

É isso aí, pessoal!
Espero que tenham gostado dessa dica e até a próxima!

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Como identificar e comprimir tabelas e índices sem compressão de dados

Por quê usar compressão de dados

O que é Row Compression Level

O que é Page Compression Level

Script para identificar tabelas e índices sem compressão de dados

Compressão de dados Page ou Row?

You may also like...

Deixe uma respostaCancelar resposta

Por quê usar compressão de dados

O que é Row Compression Level

O que é Page Compression Level

Script para identificar tabelas e índices sem compressão de dados

Compressão de dados Page ou Row?

You may also like...

SQL Server – Operações com arquivos utilizando xp_cmdshell (Como listar, ler, escrever, copiar, excluir e mover arquivos)

SQL Server – Como gerar um histórico de deadlocks para análise de falhas em rotinas

SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)

Deixe uma respostaCancelar resposta