Tenha paciência comigo, tenho muita preparação antes de chegar às perguntas.
SQL Server 2016, 13.0.7050.2 (mas isso também aconteceu em compilações anteriores). Acho que é o SQL Enterprise Edition (mas pode ser Standard). O servidor é o Windows 2016(?) com montes de RAM, um monte de núcleos e armazenamento SAN de alto desempenho (desculpe, não tenho todos os detalhes sobre isso, mas provavelmente não importa - não é limitado por recursos).
Banco de dados grande - mais de 1e12 bytes. A maior tabela tem mais de 1e9 linhas. DBCOMPAT é 130 e a estimativa de cardinalidade legada está ON. Este banco de dados foi atualizado há mais de um ano de uma versão anterior do SQL Server, provavelmente 2012 ou 2014.
Um processo ETL é executado - o aplicativo envia linhas para tabelas de preparação (via BulkCopyC#), então invoca um procedimento armazenado para enviar os dados para as tabelas de produção. As tabelas de preparação são limpas (via delete) antes de cada lote ser carregado. O tamanho do lote varia de zero linhas a vários milhões de linhas. Este procedimento armazenado tem quase 20 anos de uso, este banco de dados em particular está se aproximando dos 10 anos e teve bilhões de linhas inseridas e excluídas ao longo dos anos.
Alguns meses atrás, começamos a ter alguns lotes que demoravam muito para carregar - como horas para um lote de apenas algumas centenas de linhas. Os lotes imediatamente anteriores e imediatamente subsequentes tinham milhões de linhas cada e eram carregados normalmente.
Uma conclusão óbvia seria que esses lotes às vezes são executados com um plano ruim, devido a estimativas de cardinalidade, etc. Um rastreamento do SQL Profiler de um desses lotes lentos foi capturado e mostrou que uma consulta que foi executada por mais de uma hora, na verdade, foi executada em 6 segundos, com o restante do tempo sendo gasto na compilação da consulta (de acordo com os dados visíveis no arquivo XML .sqlplan).
Isso está em um ambiente de produção em uma grande instituição, então os experimentos estão em algum lugar entre difícil e impossível de fazer. Depois de muitas semanas de obstáculos, finalmente conseguimos obter uma cópia do banco de dados de produção em um ambiente de não produção onde poderíamos experimentar. Encontramos algumas coisas interessantes:
- Todas as consultas que tiveram tempos de execução ruins tinham uma tabela em comum -
ItemStage(usada exatamente como o nome sugere - para preparar itens durante um lote ETL). As consultas em questão não são complexas - simplesinsert ... from ...com algumas junções (todas são pesquisas de chave em chaves primárias da tabela de destino). - A tabela não tem chave primária ou índice clusterizado - é uma tabela heap.
- A tabela tem cerca de uma dúzia de relacionamentos de chave estrangeira de saída, mas todos eles são
NOCHECK. Nenhuma dessas chaves estrangeiras faz referência a tabelas grandes - talvez 40.000 linhas no máximo. update statistics ItemStagecausou 500+ mb/s sustentados de E/S de banco de dados (em uma tabela de algumas 100 linhas). Nós paramos antes que terminasse.dbcc checkallocnão encontrei problemasdbcc checkcatalognão encontrei problemasdbcc checktable(ItemStage)nunca concluído. Paramos depois de mais de 15 minutos. A tabela neste ponto tinha 842 linhas.- Tentamos adicionar uma chave primária agrupada, mas o tempo desta operação expirou. Não foi possível criar a chave primária.
- Nós descartamos a tabela e a recriamos com a definição que ela tinha o tempo todo - ainda uma pilha.
- O problema não é mais reproduzível: remover e recriar a tabela de preparação aparentemente resolveu o problema.
Então, perguntas...
- Esse problema vai reaparecer mais tarde? Em teoria, as operações que realizamos são um "no-op" lógico (mas, é claro, na prática, excluir e recriar a tabela fez com que sua alocação fosse liberada e então reconstruída, então não é um no-op no nível físico).
- Alguém já viu algo assim?
- Alguma teoria sobre o que realmente estava acontecendo?
- Alguma teoria sobre como evitar que isso aconteça novamente?