DELETE Eficiente: Estratégias para Minimizar o Impacto no Transaction Log

 

Estar imerso no universo dos bancos de dados é embarcar em um desafio constante, repleto de gratificações e descobertas prazerosas. Em nosso cotidiano, é muito comum nos depararmos com solicitações que demandam a execução de um script, como, por exemplo, um "DELETE" ou até mesmo solicitações em que o arquivo de Transaction Log fica cheio, impactando o sistema. Neste artigo, vamos explorar estratégias para otimizar esse processo e evitar que impactos como esses sejam recorrentes.

O Transaction Log, funciona como uma estrutura que registra as informações à medida em que elas ocorrem no banco de dados de maneira sequencial. Cada operação de DML(Data Manipulation Language), é registrada no Transaction Log. E para registrar essas modificações, o SQL Server emprega um protocolo conhecido como Write Ahead Logging (WAL). Esse protocolo assegura que, antes de efetivamente gravar as alterações vinculadas a uma página de dados no arquivo de dados, elas sejam persistidas em uma mídia estável. Após o processo de COMMIT, essas informações são retiradas do Log Buffer, por meio de um processo de Flush, e armazenadas em disco.

Um dos principais papeis do Transaction Log, é reforçar um dos conceitos do (ACID), principalmente o conceito de Durabilidade, garantindo que mesmo em casos de falha, após as transações serem confirmadas (“Comitadas”). Essas modificações estão protegidas, possibilitando, assim, a recuperação desses dados em caso de desastres!

Ao explorarmos e nos deparamos com as estratégias comuns de DELETE, sabemos que a depender da quantidade de dados a ser removida, esse processo terá impactos significativos no arquivo de Transaction Log, de modo que cada linha excluída é registrada no Log de transações, incluindo informações para reverter essa operação, ou seja, fazer o ROLLBACK, caso seja necessário desfazê-la!

Saindo um pouco do contexto técnico, para um contexto prático temos o exemplo realizando o processo de DELETE, um utilizando a cláusula WHERE. Sendo assim, o que podemos pensar, é, utilizando a clausula WHERE, não teremos impactos no Transaction Log, e caso isso ocorra esse impacto será mínimo. Nem sempre esse fato é verídico, porque outros processos podem começar a concorrer em paralelo com o DELETE que está sendo executado, ou até mesmo solicitar um lock (trava) no mesmo objeto que está sendo utilizado pelo processo de DELETE, isso pode ser em uma linha, página de dados, ou tabela!

Exemplo 1:

Estou realizando um DELETE em uma tabela de Clientes, onde a data de cadastro do cliente for nula, eliminado pouco mais de 1 milhão e 100 mil registros.

Antes de iniciar de fato o processo de deleção de dados, em outra guia no SQL Server Management Studio (SSMS), estou monitorando o meu arquivo de Transaction Log, da base de dados DBA que irei utilizar nesse contexto, é fácil observar que temos 13% de espaço lógico utilizado no arquivo de Log em relação aos 7 Gbs que é o tamanho total da base DBA até o momento em que iniciei esse artigo, após isso devido a outros testes o tamanho da base foi se modificando.

Em cerca de 6 segundos todos os registros que atendem ao filtro utilizado na cláusula WHERE da tabela de Cliente foram removidos, a dúvida que fica é, operação rápida, não causou impactos ao Transaction Log, certo? 

 

Monitorando o Log novamente, podemos observar que agora ouve um crescimento expressivo na nossa base de dados, e principalmente, aquele espaço lógico que estava livre no arquivo de log não existe mais!

 

Evento como esse em um cenário real de produção, caso houvesse um sistema que utilizasse essa base de dados, possivelmente em instantes o DBA receberia um acionamento que o sistema parou devido ao erro “The transaction log for database DBA is full due to ACTIVE_TRANSACTION”

Caso não seja utilizado um filtro na condição para realizar o processo de deleção de dados, esse evento se intensifica ainda mais porque agora, o número de linha excluídas e o número de informações para um possível Rollback, que serão persistidas no arquivo de Transaction Log é ainda maior.

Exemplo 2:

Uma das formas de minimizar os impactos do DELETE no arquivo de Transaction Log, e otimizar esse processo é executar o DELETE em lotes. No exemplo a seguir criei um Loop (WHILE), para a execução desse processo, iniciei o processo: 

 

 

Após iniciar o processo de DELETE, e monitorar o arquivo de Transaction Log, foi possível notar que ele estava com 10% do espaço lógico utilizado, à medida que o processo ia executando eu notei que o arquivo não cresceu muito!

 

Quinze minutos após iniciar o processo foi finalizado com sucesso, removendo todos os registros que atendiam a condição que foi empregada na cláusula WHERE. 

Monitorando novamente o arquivo de Transaction Log, ainda tinha espaço lógico disponível, diferente da primeira vez que executamos o processo de deleção! 

 

Um cenário interessante é deixar no Looping, o BatchSize Menor e Waitfor Maior isso pode reduzir o impacto nas transações concorrentes, pois os bloqueios são liberados com mais frequência. e pode ser benéfico se você estiver tentando minimizar bloqueios prolongados. A desvantagem é que pode prolongar o tempo total necessário para concluir a operação de deleção de dados, pois o processo é interrompido por períodos mais longos.

Exemplo 3:

Outra forma de otimizar esse processo de DELETE seria, criando o índice abaixo pela coluna DataCadastro: 

 

Irei analisar em quanto tempo o DELETE irá rodar, e como ficará o arquivo de Transaction Log, um adendo estou rodando somente o DELETE como rodei no primeiro exemplo sem o Looping, combinar o Looping e o índice fica muito performático na execução do processo. É possível observar que a operação durou 20 segundos. 

 

Ao verificar o espaço lógico utilizado no arquivo de Transaction Log, é possível observar que 92 % do espaço ainda está livre, e sem impactos significativos com a execução do processo de deleção após a criação do índice!

 

Outra ferramenta que pode ajudar é o Backup de Transaction Log, uma política de backups de log bem definida pode ajudar a prevenir a expansão incontrolável do arquivo de log, lógico que definir uma política de backups depende de outros fatores como a definição do RTO e RPO.

O que podemos tirar de bom dos cenários empregados nesse artigo, é que analisar a causa ou o processo antes de efetuar a operação é extremamente importante, saber propor melhorias em processos de DML, ou qualquer outro processo enviado por um cliente, pode mudar o jogo, e como consequência você se torna um profissional realizado e melhor em diversos aspectos, e reforça a importância de sempre monitorar a execução de processos como este que foi mostrado.

Espero que tenham gostado, e até a próxima! 

Se você chegou até aqui porque o seu servidor já sofreu uma queda ou lentidão generalizada devido a esse cenário, veja também Como Resolver o Erro de Transaction Log Cheio por causa do CDC no SQL Server. Para garantir que seus arquivos não sofram danos durante esses picos de processamento, recomendo o artigo Como Identificar e Corrigir Corrupção de Páginas no SQL Server. 

 

Referências: 

O log de transações  

Transaction Log Internals - Luciano Moreira