20 Janeiro 2016

Compressão de Dados SQL Server: Rebuilding Disabled Indexes

Este texto tem como objectivo descrever uma possível abordagem a um problema que podemos encontrar ao manter uma solução de compressão de dados num Data Warehouse.

A compressão de dados oferece um conjunto de vantagens sendo a principal a poupança de espaço (storage). Em determinadas situações pode também melhorar o desempenho. Pelo facto de poder exigir mais recursos de CPU, é importante decidir, individualmente para cada objecto de base de dados, a melhor forma de a aplicar. Irei, no entanto, focar este texto num detalhe específico.

Numa arquitectura de Business Intelligence, temos tipicamente uma base de dados “Staging” e uma “Data Warehouse”. Na situação que irei descrever, surge a necessidade de implementar uma solução de compressão de dados. Por uma questão de simplicidade decidimos aplicar compressão do tipo ROW e PAGE para as bases de dados Staging e DW respectivamente. Estando a lidar com um volume de dados bastante considerável (~1TB), criou-se uma Stored Procedure para mais tarde integrar num processo de manutenção agendado.

Esta Stored Procedure aceita os seguintes parâmetros:

  • Compression Type
    • NONE, PAGE, ROW
  • MinNumberRows
    • Define o número mínimo de registos a partir do qual uma determinada tabela será alvo de compressão.
  • MinTableSize
    • Define o tamanho mínimo (em MB) que uma tabela deve ter para ser alvo de compressão.
  • PrintOnly
    • Opção para prever apenas (sem executar) os comandos que irão ser executados.
  • Schema
    • Possibilita o filtro por SCHEMA.
  • MaxDuration
    • Define a duração máxima da execução da Stored Procedure.

Aqui fica um exemplo de uma execução utilizando a BD “Adventure Works DW 2012”:


A janela de resultados mostra o progresso da execução, os comandos executados e o tipo de compressão que cada objecto tinha anteriormente (“Before was NONE”). Adicionalmente, este procedimento ignora os objectos que já se encontram com o tipo de compressão que se pretende. Assim, executando novamente, obtemos o seguinte resultado:

Até aqui tudo bem. No entanto, passados alguns dias e algumas execuções diárias de processos agendados (ETL, etc), reparámos que alguns índices tinham perdido a compressão. Isto deveu-se ao facto de nos processos ETL existirem alguns comandos “Rebuid Index” do seguinte tipo:

Nada de errado com este comando, a não ser que seja executado para um índice que esteja desabilitado. Neste caso, perde a compressão. Como se pode ler na Documentação Microsoft:

REBUILD [ WITH ( [ ,... n]) ]

Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

Assim, temos (pelo menos) duas possibilidades. Editar vários pacotes de Integration Services (mais de 100) com o objectivo de adicionar a cláusula ”COMPRESSION_TYPE” aos comandos de REBUILD ou encontrar uma forma de forçar o tipo de compressão para cada índice tendo como base o tipo de compressão da tabela subjacente. Uma vez que a primeira opção apresenta o risco de termos que repetir todo o processo de edição dos pacotes de SSIS no caso de se decidir alterar o tipo de compressão, optou-se pela segunda.

Aqui fica uma possível forma de reconstruir um índice utilizando o tipo de compressão da tabela adjacente:

Assim, substituindo o comando SQL em todos os pacotes SSIS pela execução deste procedimento, conseguirmos garantir que os índices (nonclustered) não perdem o tipo de compressão que foi definido originalmente.

.

.

.

.

      Pedro Teixeira
  Associate Manager
Solutions Development