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
Como você observa, o plano mostra muitas varreduras de índice[1], alguns dos índices várias vezes, como
Facilities.IX_CustomerID. É por isso que ele está girando o recurso da CPU: esses índices estarão no pool de buffers, portanto, pouca E/S é necessária e as CPUs estão sendo feitas para mastigá-los, provavelmente em sua totalidade, repetidamente.Se isso for de uma instrução simples
DELETEe todas as outras atividades forem o mecanismo que impõe restrições de chave estrangeira, eu verificaria as chaves estrangeiras sem oferecer suporte a índices. Por exemplo, uma das varreduras de[Facilities]" Index="[IX_CustomerID]tem o predicado de pesquisa[DataMateWeb].[dbo].[Facilities].[SurfaceCleanerID]=[DataMateWeb].[dbo].[Chemical].[ChemicalID], o que sugere que uma cobertura de índice[Facilities].[SurfaceCleanerID]não está presente ou, por algum motivo, não pode ser usada. Quando você define uma chave estrangeira, um índice não é criado automaticamente para acompanhá-lo, o que pode surpreender as pessoas e isso explicaria uma varredura da tabela/cluster em vez de várias buscas em um índice. Em todos os casos em que esses índices são os índices de suporte para as chaves estrangeiras, certifique-se de que eles estejam definidos corretamente e não tenham estatísticas desatualizadas.Se a operação de exclusão não for tão simples, certifique-se de que quaisquer junções e subconsultas usadas para decidir quais linhas serão excluídas sejam suportadas por índices apropriados e que os predicados de junção e filtragem sejam organizados de forma que possam ser sargáveis sempre que possível.
Falhando o acima (ou seja, todos os FKs e qualquer outra atividade de filtragem e junção parecem bem em relação a ter índices que eles poderiam buscar), o próximo canudo que eu escolheria é: se você estiver excluindo muitas linhas de uma só vez (
DELETE WHERE IN (<a result that could be a long list>)) então eu vi que fazer o SQL Server escanear o índice em vez de usar várias buscas. Isso parece um pouco desagradável[2], mas: verifique isso tentando confirmar o comportamento com apenas uma linha e, se isso alterar pelo menos algumas das verificações para buscas (e melhorar o desempenho como resultado), considere percorrer as linhas que precisam ser removidos e fazendo-os um de cada vez.[1] Como são varreduras de índice clusterizado , são efetivamente varreduras de tabela, o que para tabelas grandes significa que muitos dados estão sendo tocados.
[2] Porque é desagradável. Mas se é desagradável e funciona, ainda funciona!
Se você não tiver a opção de desabilitar o FK ou colocar o banco de dados offline, poderá excluir em lotes com uma espera (suspensão) e manter a CPU inativa. Vai demorar mais, mas isso pode não ser um problema.