Início / dba / Perguntas / 345198
Accepted
CarlDaniel
Asked: 2025-02-11 02:58:49 +0800 CST

SQL 2016 Pequenos lotes ETL carregam muito lentamente enquanto grandes lotes carregam normalmente em banco de dados terabyte com tabelas de giga-row

  • 772

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 - simples insert ... 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 problemas
  • dbcc checkcatalognão encontrei problemas
  • dbcc 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?
sql-server

1 respostas

  1. Best Answer

    Parece que a tabela de heap ItemStage tinha um grande número de páginas alocadas (mas vazias).

    Heaps geralmente não gerenciam bem o espaço. Páginas de heap vazias geralmente não são desalocadas por a a DELETEmenos que um bloqueio de tabela seja especificado ou mantido, e mesmo assim, nem sempre.

    De DELETE(Transact-SQL) na documentação:

    Quando linhas são excluídas de um heap, o Database Engine pode usar bloqueio de linha ou página para a operação. Como resultado, as páginas esvaziadas pela operação de exclusão permanecem alocadas ao heap. Quando páginas vazias não são desalocadas, o espaço associado não pode ser reutilizado por outros objetos no banco de dados.

    Para excluir linhas em um heap e desalocar páginas, use um dos seguintes métodos.

    • Especifique a TABLOCKdica na DELETEinstrução. Usar a TABLOCKdica faz com que a operação delete tome um bloqueio IX no objeto em vez de um bloqueio de linha ou página. Isso permite que as páginas sejam desalocadas. Para obter mais informações sobre a TABLOCKdica, consulte Dicas de tabela (Transact-SQL) .

    • Use TRUNCATE TABLEse todas as linhas devem ser excluídas da tabela.

    • Crie um índice clusterizado no heap antes de excluir as linhas. Você pode descartar o índice clusterizado depois que as linhas forem excluídas. Esse método consome mais tempo do que os métodos anteriores e usa mais recursos temporários.

    Observação

    Páginas vazias podem ser removidas de um heap a qualquer momento usando a ALTER TABLE <table_name> REBUILDinstrução.

    Como outro exemplo , uma definição de tabela que permite a possibilidade de LOBalocação ROW_OVERFLOWde unidades desabilita a desalocação de páginas do heap, mesmo sob um bloqueio de tabela.

    Observe que os dados na tabela não precisam realmente criar LOBou ROW_OVERFLOWalocar unidades; a mera possibilidade é suficiente.

    A desalocação sob um bloqueio de tabela também não funciona se o banco de dados tiver um nível de isolamento de versão de linha habilitado ( READ COMMITTED SNAPSHOT ISOLATIONou SNAPSHOT ISOLATION). Novamente, eles não precisam estar em uso — estar habilitados é suficiente.

    Isso pode soar como um recurso ruim, mas é por design. Tabelas de heap simplesmente não têm as mesmas habilidades de localização de entrada que um índice. Qualquer coisa além de casos muito simples exigiria uma travessia completa de toda a estrutura usando as páginas do IAM como a única informação disponível sobre quais páginas pertencem à tabela.

    Você precisaria monitorar o tamanho total da tabela (mesmo quando aparentemente vazia!) e REBUILD(ou descartá-la e recriá-la) quando ela se tornasse excessiva. Reconstruir provavelmente seria mais conveniente. Você pode ver o tamanho físico atual usando sys.dm_db_index_physical_stats.

    Você pode muito bem decidir que é mais fácil dar à tabela um índice clusterizado. Certifique-se de que você não está dependendo de nenhum comportamento somente de heap antes de fazer isso (inserções paralelas, por exemplo).

    TRUNCATE TABLEnão é uma possibilidade no seu caso devido às chaves estrangeiras (mesmo com NOCHECK).

    • 3

relate perguntas