Quando Ghost Records viram um problema de performance no SQL Server!

Recentemente analisamos um caso em produção muito interessante. Um job que executava em cerca de 5 minutos, passou a ficar cada dia mais lento até chegar a 48 minutos.

Sem alterações na rotina ou no ambiente que justificassem essa demora. Tentamos simular em base de HML e não estava sendo possível reproduzir o cenário. Sem waits que justificassem, tempo de processamento era +90% CPU, sem spinlocks com impactos diretos, fizemos diversos testes para entender a diferença em uma base cópia.

O processo era “complexo”. Muitos loops de DELETES faziam com que o plano de execução não fosse gerado inteiro para conseguirmos comparar efetivamente.

Primeiros testes

Então decidimos simplificar as comparações entre os ambientes, e testamos um simples SELECT TOP (5000) na tabela que mais apresentava lentidão no processo.

Comparando os ambientes produtivo e HML teve uma grande diferença:

• ~2700ms em produção

• 16ms em HML

(tempo de CPU)

Ambientes visualmente iguais, hardware equivalente, configurações similares, plano de execução com os mesmos operadores e estimativas, estatísticas iguais… nada parecia diferente para justificar.

Um comportamento curioso

Após restaurar a base novamente para novos testes tivemos uma boa surpresa: a query demorou nas 3 primeiras execuções e depois voltou a ficar com 16ms nas demais.

Apenas na primeira execução teve leituras físicas / read-ahead, então não seria uma justificativa para as execuções subsequentes também terem tempo de CPU alto.

Repetimos esse teste para pegar mais informações e reparamos que a quantidade de leituras lógicas começava alta e caía aos poucos.

O efeito do rebuild

Vimos que, com o REBUILD do índice cluster após o restore, a consulta não apresentava mais lentidão nas primeiras execuções. Já iniciava com o tempo esperado de 16ms.

A fragmentação do índice também não justificava os sintomas.

Aí começou uma nova busca para entender o que o rebuild resolve, impacta nas leituras lógicas e não é fragmentação.

Parece até uma charada rsrs.

A hipótese que explicou tudo

Conversando internamente com o time para ajudarem com ideias, a Raiane Flores Borba Lins deu várias ideias e hipóteses.

Mas todas já haviam sido testadas e descartadas, menos a última:

“Ghost Records”

Algo que ainda não havia sido validado.

Causaria os mesmos sintomas e seria resolvido com o rebuild.

Validação do cenário

Para conseguirmos comprovar, desativamos o processo do Ghost Cleanup em uma nova instância para testes.

Subimos a base e conseguimos validar que a tabela tinha mais de 11 milhões de ghost records.

Algumas tabelas na base passavam de 40 milhões.

O que são Ghost Records

No SQL Server, quando um registro é deletado, ele nem sempre é removido fisicamente naquele momento.

Muitas vezes ele é apenas marcado para remoção futura, ficando como “ghost record”.

A limpeza física é feita periodicamente por um processo interno chamado Ghost Cleanup.

O impacto na prática

Na prática, isso fazia uma consulta simples como:

SELECT TOP (5000)

precisar percorrer dezenas de milhares de páginas para encontrar 5.000 linhas válidas.

O resultado foi:

• alto volume de leituras lógicas

• aumento de CPU

À medida que o Ghost Cleanup avançava, a mesma consulta passava a executar com poucas leituras e CPU praticamente zero.

O mesmo efeito também foi validado com rebuild de índice, que reduziu drasticamente a quantidade de ghost records e devolveu a performance esperada.

Por que o problema não apareceu antes

Uma característica do Ghost Cleanup é que, após um scan na tabela ou índice, as ghost records são enfileiradas para que ele possa fazer a limpeza.

Isso fez com que os testes iniciais em HML resolvessem o problema antes de conseguirmos identificar a causa, nos levando para outros caminhos.

O cuidado do cliente em não permitir scans na tabela fez com que elas não fossem “forçadas” à limpeza, entrando na fila padrão concorrendo com milhares de outras.

Algumas ações que podem ajudar nesse tipo de cenário

• manutenção periódica de índices

• reavaliar estratégia de deletes em batches maiores

• usar TRUNCATE quando aplicável

• em alguns casos, recriar a tabela e mover apenas os dados necessários

• forçar scan das tabelas para forçar o ghost cleanup (brincadeira rs, o rebuild ainda é uma opção melhor)

Está passando por esse tipo de problema na sua empresa? A Power Tuning oferece consultoria especializada em performance para Banco de Dados.

Fale com a gente: +55 27 3441-3060

Artigo desenvolvido por: Eduardo Rabelo (Tech Leader).