Deletando informações em batches –

Olá pessoal,

Quando trabalhamos bases de dados muito grandes e criticas é normal que alguma forma de purge (expurgo) seja feita no banco de dados, é melhor para a aplicação e também para o DBA. Porem uma grande preocupação quando vamos falar sobre deletar uma grande quantidade de dados é não prejudicar nosso arquivo Transaction Log para que o mesmo não estoure o espaço em disco, como também não bloqueie nossa aplicação que esta tentando inserir ou atualizar dados na base. Alem de tudo isso nos preocupamos com performance, pois uma grande operação de delete poderia impactar todo o nosso ambiente.

Pensando nisso criei um script bem simples, mas que pode te ajudar no momento de realizar esse expurgo da base de dados.

Apenas para ilustrar, segue abaixo o ambiente.

Base: BatchingDeleting
Tamanho:4GB
Tamanho Log File: 1GB
Tabela: tbMessages
Numero de registros: 18455404

Caso 1: Realizando o delete da tabela completa

image

Utilizando o script acima vou realizar uma unica transação de delete que irá então apagar os 18 milhoes de registros da tabela de uma unica vez. Também vou coletar os seguintes contadores para avaliar o desempenho do transaction log.

— SQLServer: Databases
–\SQLServer:Databases(BatchingDeleting)\Log File(s) Size (KB)
–\SQLServer:Databases(BatchingDeleting)\Log File(s) Used Size (KB)
–\SQLServer:Databases(BatchingDeleting)\Log Growths
–\SQLServer:Databases(BatchingDeleting)\Log Flushes/sec
–\SQLServer:Databases(BatchingDeleting)\Log Flush Wait Time

— Processor
–\Processor(_Total)\% Processor Time

Abaixo o tempo gasto para realizar o delete.

image

Como podem ver, levamos 323 segundos, cerca de quase 5:30 minutos para realizar o delete.

Porem, vamos validar os dados gerados pelo Performance Monitor.

CPU

image

Não tivemos uma utilização grande em CPU, nossa média de utilização foi de 5%

Tamanho do Arquivo e Tamanho Utilizado do arquivo de dados.

image

O arquivo iniciou com o tamanho de 1GB.

image

Porem, finalizou o delete com 16GB.

Você também pode notar que o tamanho utilizado acompanhou o crescimento do arquivo fisico de log, e somente após a finalização do delete houve a reutilização do espaço.

Log Growth

image

O SQL Server precisou crescer o arquivo de log 31 vezes. Imagine aqui o tempo de espera para cada crescimento.

Vamos agora ao caso 2 onde realizamos o delete das informações de forma eficiente. É importante lembrar que nesse momento eu realizei um restore da base de dados para ficar exatamente igual ao momento de inicio.

Caso 2: Realizando o delete da tabela em batches

Link para download do script: http://1drv.ms/1DFeyoJ

image

Como vocês podem ver acima, o script realiza uma pequena validação para continuar a ação de delete. Alem do script também estar marcando o tempo, também estou realizando a coleta dos performance counters como no caso 1, então segue o balanço geral.

Tempo gasto.

image

8150 segundos, equivale a mais ou menos 02h15min, onde em relação a tempo obtivemos um tempo muito maior, mas vamos olhar os contadores de performance.

CPU

image

Tivemos uma maior utilização de CPU, onde a média ficou na casa de 15%, porem nosso maior pico foi de 36%, até ai nada muito preocupante se levarmos em conta que o purge de um grande volume de dados não é feito a todo momento.

Tamanho do Arquivo e Tamanho Utilizado do arquivo de dados.

image

O arquivo assim como no caso 1, iniciou com o tamanho de 1GB.

image

Porem, finalizou com o tamanho muito abaixo dos 16GB apresentados no caso 1, aqui tivemos o tamanho final de 6GB, ou seja, tivemos ai um ganho de 37,5% no espaço consumido do arquivo.

Outro fato legal é que o tamanho realmente utilizado do arquivo ficou muito próximo do alocado, até mesmo depois da rotina de delete ser finalizada. Isso nos mostra uma utilização de espaço muito melhor e mais gerenciada.

image

Log Growth

image

No caso 1 onde o SQL Server precisou crescer o arquivo de log 31 vezes, no caso 2 o numero de crescimentos diminuiu para 10, otimizando ai muito mais nossas operações de delete.

É importante ressaltar que os testes que realizei são em ambientes onde somente essa rotina estava rodando, porem tratando-se do mundo real, isso não é verdade. Para um cenário real, poderiamos adicionar os seguintes casos: Backups Full & Differencial e de Log ocorrendo, a base de dados em questão poderia ter Database Mirroring, o que dificulta ainda mais realizar deletes de muitos dados, multiplas conexões realizando INSERT & UPDATE & DELETE e SELECT, jobs rodando DBCC CHECKDB, Index Defrag, Atualização de Estatisticas e por ai vai……

Você pode falar que iss não ocorre em seu ambiente, mas sempre devemos levar isso em conta quando mensurar o problema.

Espero que tenham gostado e até a próxima!
Marcos Freccia
SQL Server MVP

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em maio 4, 2015, em Desenvolvimento, Dicas, Scripts, SQL Server, SQL Server 2012, SQL Server 2014, T-SQL, VirtualPass e marcado como , , , , , . Adicione o link aos favoritos. 4 Comentários.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: