Coding Gorilla's questions

Eu tenho um banco de dados relativamente pequeno, talvez algumas centenas de milhares de linhas, e temos um procedimento armazenado que se destina a limpar um 'Dealer' (a raiz agregada do aplicativo) e todos os dados relacionados. O banco de dados reside em uma instância v12 do Azure SQL e, quando atinge uma tabela específica, consome 100% da DTU/CPU da instância e leva muito tempo para ser concluído.

Acredito que o problema é que a tabela tem uma tonelada de chaves estrangeiras, provavelmente cerca de 30. Observando o plano de execução, você pode ver que está fazendo uma tonelada Nested Loopde varreduras de índice para encontrar linhas dependentes. Todas essas linhas já foram excluídas antes da tentativa de excluir as linhas dessa tabela específica, portanto, esses loops são realmente inúteis.

Minha pergunta é, seria mais eficiente desabilitar as restrições de chave estrangeira nessas tabelas dependentes, fazer a exclusão e reativar as restrições? Haveria algum efeito negativo em fazer isso, além do fato de ser tecnicamente possível que alguns dados ruins sejam inseridos enquanto as restrições estão desabilitadas. Ou existe uma maneira ainda melhor de realizar o que eu quero realizar?

ATUALIZAÇÕES Este é um banco de dados SQL do Azure v12, não é o SQL Server 2012 completo em uma VM. Não estou agrupando nenhuma exclusão e a instrução incorreta faz parte de um SPROC maior. Identifiquei essa exclusão de tabela em particular separando o sproc e executando pedaços dele separadamente. Quando chegou a esta mesa, levou literalmente 15 minutos para ser concluído. Isso afetará a ordem de centenas de linhas, variando dependendo do estado do 'Dealer' específico que estamos limpando.

Não podemos fazer exclusões temporárias neste caso porque geralmente estamos limpando esse revendedor para que ele possa ser substituído (exclusões temporárias causariam duplicação e exigiriam muito código para contornar). Eu investiguei outros tipos de bloqueio e estados de espera e não encontrei mais nada que parecesse estar afetando negativamente isso. Como mencionei, esse banco de dados é relativamente pequeno e é usado principalmente para operações CRUD muito simples.

Aqui está o plano de execução, é enorme.

A versão XML do plano de execução pode ser encontrada aqui: https://gist.github.com/CodingGorilla/6cf7a87df9257d5f93e0d545af9839c2

Eu gerencio um aplicativo que tem um back-end de banco de dados Oracle muito grande (quase 1 TB de dados com mais de 500 milhões de linhas em uma tabela). O banco de dados realmente não faz nada (sem SPocs, sem gatilhos ou qualquer coisa), é apenas um armazenamento de dados.

Todos os meses, somos obrigados a limpar os registros das duas tabelas principais. Os critérios para a eliminação variam e são uma combinação de idade da linha e alguns campos de status. Normalmente, acabamos limpando entre 10 e 50 milhões de linhas por mês (adicionamos cerca de 3 a 5 milhões de linhas por semana por meio de importações).

Atualmente, temos que fazer essa exclusão em lotes de cerca de 50.000 linhas (ou seja, excluir 50.000, confirmar, excluir 50.000, confirmar, repetir). A tentativa de excluir todo o lote de uma só vez faz com que o banco de dados não responda por cerca de uma hora (dependendo do número de linhas). Excluir as linhas em lotes como este é muito difícil para o sistema e normalmente temos que fazer isso "conforme o tempo permitir" ao longo de uma semana; permitir que o script seja executado continuamente pode resultar em uma degradação de desempenho que é inaceitável para o usuário.

Acredito que esse tipo de exclusão em lote também degrada o desempenho do índice e tem outros impactos que eventualmente causam a degradação do desempenho do banco de dados. Existem 34 índices em apenas uma tabela, e o tamanho dos dados do índice é realmente maior do que os próprios dados.

Aqui está o script que um de nossos funcionários de TI usa para fazer essa limpeza:

BEGIN
LOOP

delete FROM tbl_raw 
  where dist_event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;

  exit when SQL%rowcount < 49999;

  commit;

END LOOP;

commit;

END;

Este banco de dados deve estar em 99,99999% e só temos uma janela de manutenção de 2 dias uma vez por ano.

Estou procurando um método melhor para remover esses registros, mas ainda não encontrei nenhum. Alguma sugestão?