Automatic index compaction: o que ele faz e não faz

O automatic index compaction consolida linhas em menos páginas de forma contínua e em background, reduzindo espaço usado, I/O e memória sem job de manutenção. Ele não reorganiza a ordem física das páginas, não atualiza estatísticas e não devolve espaço alocado ao sistema operacional. Entender essa fronteira é o que separa "ligar e esquecer" de "ligar e descobrir que a janela de manutenção ainda é necessária".

O recurso está em preview e a lista de aplicabilidade é mais estreita do que o nome sugere.

Onde o recurso está disponível

A documentação da Microsoft lista o automatic index compaction para três serviços:

O SQL Server em versão box — 2019, 2022 e 2025 — não consta na lista de aplicabilidade nem no "What's new in SQL Server 2025". Se o seu ambiente é on-premises ou VM em IaaS, o ALTER INDEX ... REORGANIZE e o REBUILD continuam sendo as ferramentas disponíveis, e vale revisar a estratégia atual em vez de esperar pelo recurso.

No Managed Instance, a restrição da política de atualização é relevante na prática: instâncias configuradas com política vinculada a uma versão específica do SQL Server não recebem o recurso. Confirme a política antes de planejar a mudança.

O mecanismo: carona no PVS cleaner

O automatic index compaction não é um processo novo. Ele é uma tarefa adicional do persistent version store (PVS) cleaner, o componente do Accelerated Database Recovery que percorre periodicamente as páginas removendo versões de linha obsoletas.

Quando a compactação está habilitada, o cleaner faz um trabalho a mais em cada página que visita. Ao encontrar uma página com espaço livre — descontado o espaço reservado pelo fill factor —, ele move linhas da página seguinte para preencher essa lacuna, e repete a operação para um pequeno número de pares de páginas consecutivas. Se uma página fica vazia depois da movimentação, ela é desalocada.

Essa origem explica a característica mais importante do recurso: a compactação só age sobre páginas recentemente modificadas. O cleaner visita páginas que tiveram inserções, atualizações ou exclusões. Um índice que ninguém alterou desde que você ligou a opção não será compactado, por mais baixa que esteja a densidade dele.

A consequência prática está na própria recomendação da Microsoft: se a densidade de página de um índice já está ruim hoje, faça um REBUILD ou REORGANIZE pontual para corrigir o passado. A partir daí, a compactação automática mantém o resultado sem intervenção.

O que ele não faz

Quatro limites merecem atenção antes de tratar o recurso como substituto de manutenção.

Não reduz fragmentação. Compactação aumenta densidade de página; não reordena páginas fisicamente. Você pode observar avg_fragmentation_in_percent subindo depois de habilitar o recurso — desalocar uma página vazia cria lacunas na numeração dentro do extent. Para a maioria das cargas isso não é problema: mesmo que o read-ahead perca eficiência, a consulta lê menos páginas no total. Se a carga é intensiva em escrita e as páginas voltam a dividir logo após a compactação, a orientação é um rebuild pontual com fill factor levemente reduzido, na faixa de 70 a 95%.

Não atualiza estatísticas. Se a sua rotina de rebuild existe hoje em parte porque ele atualiza estatísticas com fullscan de graça, esse efeito colateral desaparece. Nesse caso, a compactação automática precisa ser combinada com um job dedicado de atualização de estatísticas.

Não reduz o tamanho alocado dos arquivos. A compactação diminui o crescimento do espaço usado dentro dos arquivos de dados, o que é diferente de um shrink. Se o problema é arquivo grande demais em disco, o assunto é outro — vale revisar por que o banco cresce até encher o disco.

Não cobre tudo. O processo considera apenas páginas do nível folha de índices B-tree em unidades de alocação IN_ROW_DATA. Ficam de fora:

• páginas de heaps;
• unidades ROW_OVERFLOW_DATA e LOB_DATA;
• rowgroups comprimidos de índices columnstore;
• tabelas memory-optimized.

Também ficam de fora tabelas de sistema, bancos de sistema exceto msdb, e índices criados com ALLOW_PAGE_LOCKS = OFF. Se o seu maior consumidor de espaço é uma heap ou uma coluna LOB, o recurso não vai ajudar.

Como habilitar e confirmar

A opção é de banco de dados e vem desabilitada por padrão:

ALTER DATABASE [SeuBanco]SET AUTOMATIC_INDEX_COMPACTION = ON;

Não é necessário reinício nem acesso exclusivo. A compactação começa ou para em poucos minutos após o comando. Para confirmar o estado:

SELECT database_id,       name,       is_automatic_index_compaction_onFROM sys.databases;

A mesma informação está disponível pela propriedade IsAutomaticIndexCompactionOn em DATABASEPROPERTYEX.

Como medir se está funcionando

Ligar sem medir transforma o recurso em fé. Há três níveis de observação.

A métrica de resultado é a densidade de página, em avg_page_space_used_in_percent de sys.dm_db_index_physical_stats. Colete antes de habilitar e acompanhe a evolução. Use o modo SAMPLED para bancos grandes; DETAILED varre todos os índices elegíveis e pode demorar muito.

A métrica de atividade está em sys.dm_db_index_operational_stats, que expõe contadores cumulativos por partição:

SELECT OBJECT_NAME(ios.object_id) AS object_name,       i.name AS index_name,       ios.compaction_attempt_count,       ios.compaction_complete_count,       ios.compaction_skip_count,       ios.compaction_ineligible_count,       ios.compaction_row_move_count,       ios.compaction_page_deallocation_countFROM sys.dm_db_index_operational_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT) AS ios     INNER JOIN sys.indexes AS i         ON ios.object_id = i.object_id        AND ios.index_id = i.index_idWHERE i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')      AND OBJECT_SCHEMA_NAME(ios.object_id) <> 'sys';

A leitura útil é a relação entre as colunas. compaction_attempt_count alto com compaction_complete_count baixo indica que as tentativas estão sendo abandonadas. compaction_ineligible_count alto aponta páginas que nunca serão elegíveis — provável sinal de heap, LOB ou columnstore no objeto.

O detalhe temporal vem do extended event auto_index_compaction_stats, que dispara a cada 10 minutos com estatísticas acumuladas desde o start do engine, incluindo linhas movidas, páginas desalocadas e os motivos de skip.

Quando a compactação simplesmente para

O recurso cede prioridade. A compactação é suspensa quando o PVS atinge 150 GB ou mais, ou quando há 1.000 ou mais transações abortadas para limpar — a limpeza do version store vem primeiro. Páginas também são puladas quando há transação ativa usando a página, rebuild ou reorganize em andamento no índice, ou operação de shrink em curso.

Isso cria um cenário que confunde no diagnóstico: um ambiente com transações longas e muito rollback pode ter a compactação habilitada e praticamente inativa. Se a densidade de página não melhora, verifique o tamanho do PVS antes de concluir que o recurso não funciona.

Índices em rebuild ou reorganize são ignorados, incluindo operações resumable pausadas. As duas coisas convivem sem conflito.

O custo real

O overhead não é zero, mas é dirigido. Como a compactação toca apenas páginas recentemente modificadas, ela é muito mais barata que rebuild ou reorganize, que processam todas as páginas.

Três efeitos merecem acompanhamento. O primeiro é o log de transações: mover muitas linhas gera escrita, o que aumenta o I/O de log e o tamanho dos backups de log. O segundo é CPU, com aumento na casa de unidades percentuais e não frequente. O terceiro são os locks: a compactação adquire locks exclusivos de página de curta duração, e pula a página se não conseguir o lock imediatamente, justamente para não bloquear a carga. Bloqueios são improváveis e da ordem de milissegundos.

Se aparecer bloqueio suspeito, o head blocker em sys.dm_exec_requests mostra o comando VERSION_CLEANER_MAIN ou VERSION_CLEANER_WORKER. Esse é o método para atribuir o bloqueio à compactação em vez de à aplicação, complementando o diagnóstico usual de bloqueios e locks no SQL Server.

Vale registrar o que continua funcionando: compressão de linha ou página não interfere — a compactação remove espaço vazio independentemente de os dados estarem comprimidos. E em bancos serverless, o recurso não impede o auto-pause nem acorda o banco pausado.

O que muda no plano de manutenção

Para quem está em Azure SQL Database, Managed Instance com política Always-up-to-date ou Fabric, a sequência razoável é: medir densidade de página atual, executar um rebuild ou reorganize pontual onde ela estiver baixa, habilitar a compactação automática, manter um job de atualização de estatísticas e acompanhar os contadores por algumas semanas antes de reduzir a frequência do job de índices.

Para quem está on-premises, o recurso não é uma opção hoje — e o ponto que ele evidencia continua válido: densidade de página costuma explicar mais sobre consumo de recursos do que o percentual de fragmentação que a maioria dos scripts de manutenção usa como gatilho.

Referências: Automatic index compaction, otimização de manutenção de índices e sys.dm_db_index_operational_stats.

Se a janela de manutenção de índices está longa demais ou o banco cresce sem explicação clara, a H1 Data oferece um diagnóstico gratuito de SQL Server para avaliar densidade de página, estratégia de manutenção e o impacto real no consumo de recursos.