Como deletar registros de uma tabela de uma forma mais rápido
Estou com uma dúvida que tenho de desfazer de alguns registros e outros tenho de deixar, onde até pensar em criar uma nova e pegar os dados necessários, mais isto não me atende neste caso especifico, pois este processo vou precisar executar outras vezes, então o caminho seria apagar mesmo e como tem diversos registros, qual seria a melhor maneira de apagar esses registros de uma forma mais rápida, onde este Servidor tem bastante memória e o espaço em disco da para executar alguns procedimentos com uma certa rapidez, então teria algo que pudesse aproveitar esses recursos que tenho no momento, para apagar esses registros de uma forma mais rápida ?
sexta-feira, 3 de janeiro de 2020 11:34
Você poderia destacar também:
1 - Qual o volume, ou seja, quantidade de registros que deseja apagar desta tabela?
2 - Estamos se referindo a qual versão de SQL Server?
3 - Qual é o modelo de recuperação do banco de dados?
4 - Quantos arquivos de dados este banco possui?
Gostaria de antecipar um procedimento que utilizei a algum tempo para um cliente que desejava excluir aproxidamente 40 bilhões de registro. Dividimos o processo de exclusão em partes, fazendo uso da opção opção LOCK_ESCALATION = TABLE, implementanda a partir do SQL Server 2012, para possibilitar ao SQL Server aplicar o controle de lock na camada de tabela mesmo que ocorram processam que dependam dela sem provocar bloqueios para os outros objetos dentro de uma transação.
Outro detalhe importante, antes de colocar o processo de exclusão em prática, alteramos o modelo de recuperação do banco de dados para Bulk_Logged.
Tenho um exemplo de código elaborado pelo meu amigo também MVP Fabiano Amorim em 2013 para um evento que participamos, se você tiver interesse me avise.
Editado Junior Galvão - MVPMVP sexta-feira, 3 de janeiro de 2020 15:46
Marcado como Resposta neibala sexta-feira, 3 de janeiro de 2020 20:53
sexta-feira, 3 de janeiro de 2020 15:46
"neibala", a abordagem a adotar depende de vários fatores, como quantidade de linhas da tabela; se há cláusula ON DELETE CASCADE; se há procedimentos de gatilho (trigger) associado ao evento DELETE; percentual de linhas a apagar em relação ao total de linhas da tabela; dentre vários outros.
Como você descartou a opção de "criar uma nova tabela com as linhas que permanecerão e após apagar a tabela atual", então a forma mais rápida de apagar linhas me parece que é executar o comando DELETE, bloqueando a tabela:
DELETE tabela with (TABLOCK)
Entretanto, enquanto as linhas estiverem sendo apagadas, a princípio os demais processos que necessitem alterar a tabela ficam suspensos ou até mesmo são cancelados. Recentemente escrevi o artigo "Apagar conjunto de linhas em tabelas enormes" que trata justamente de como apagar linhas em tabelas grandes minimizando os efeitos negativos em outros processos simultâneos.
Às vezes a melhor abordagem é apagar as linhas em blocos, aos poucos, evitando que o mecanismo de lock escalation atinja o nível TABLE, quando ocorre o bloqueio da tabela e então os demais processos que tentem alterar a tabela entram em modo de espera ou são cancelados.
No capítulo 3 do artigo é tratado de lock escalation, ghost cleanup, READPAST, arquivo de log de transações etc.
Como consta no artigo, não há "a melhor" maneira de apagar linhas mas sim um elenco de técnicas dentre as quais deve-se escolher aquela que retorne melhores resultados para cada caso. Em suma, DEPENDE! :)
o que contém a tabela?
qual o motivo de ter que apagar rapidamente?
qual o número aproximado de linhas a apagar?
Editado José Diz sexta-feira, 3 de janeiro de 2020 23:07
sexta-feira, 3 de janeiro de 2020 11:47
Acabei lembrando de algo não tão normal para o meu dia-dia, mais pelas suas dicas o caso do with (tablock) neste caso especifico me atenderia em muito e revendo novamente as suas dicas e vendo as comparações que você fez o caso de transação deu um tempo bem legal, onde depois estarei analisando e testando em meus processos, pois tinha feito outros testes e de outras formas e até o momento não tinha testado com transação (commit).
Então de momento isto já me ajudou bastante mesmo.
sexta-feira, 3 de janeiro de 2020 20:48
As suas informações foram muito legais, pois algumas acabei tirando a dúvida novamente pelas dicas do José Diz e outras que tinha testado e o exemplo que você comentou teria como me enviar por e-mail (neibala@gmail.com).
sexta-feira, 3 de janeiro de 2020 20:53
Que bom, espero que possa lhe ajudar mesmo.
Estou enviando o arquivo.
sexta-feira, 3 de janeiro de 2020 22:02
Junior Galvão / José Diz
Quero deixar novamente registrado as informações que recebi, pois me ajudou em muito, pois tinha feito diversos testes e o teste com transação superou as minhas expectativas, pois nunca tinha testado com uma grande quantidade de registro e foi além das minhas expectativas mesmo, então valeu mesmo.
terça-feira, 7 de janeiro de 2020 18:41
Espero que o script que lhe enviei tenha sido útil.
terça-feira, 7 de janeiro de 2020 18:43
"neibala", as técnicas descritas no artigo "Apagar conjunto de linhas em tabelas enormes" são úteis, pois atendem diversos casos. Como citado nas observações finais do artigo