Pergunta de otimização de consulta

Aqui estão alguns cálculos aproximados de onde o tempo foi gasto em seu plano de execução:

• Cerca de 5 segundos para ingressar em CohortStagingTemp, #Cohort_5 e #Cohort_4 junto com junções de hash no modo em lote
• Cerca de 42 segundos para ingressar no #Cohort_1
• Cerca de 15 segundos para entrar nas 3 mesas restantes
• Cerca de 16 segundos para a inserção paralela

Portanto, há um gargalo claro na consulta: a junção com #Cohort_1. O SQL Server está estimando um tamanho de linha de 12 KB dessa tabela, então suponho que você tenha algumas colunas LOB nessa tabela. Existem algumas restrições nos dados LOB para junções de hash no modo em lote. Provavelmente é por isso que você vê algumas junções de hash no modo de lote e, em seguida, o plano muda para o modo de linha com a junção de mesclagem cara junto com outras junções de hash no modo de linha.

Em geral, eu seguiria uma abordagem: obter um plano de consulta com junções de mesclagem em modo de linha ou obter um plano principalmente com junções de hash em modo de lote. Todas as suas tabelas têm a mesma contagem de linhas e se unem na mesma coluna, portanto, as junções de mesclagem podem funcionar bem aqui, mas a consulta pode precisar ser executada em MAXDOP 1 para melhor desempenho. Recomendo obter planos de consulta reais para os seguintes casos de teste:

1. Experimente a consulta como está, mas comente a junção em #Cohort_1
2. Tente obter um plano de hash join melhor adicionando uma OPTION (HASH JOIN, LOOP JOIN)dica e reescrevendo a consulta para que #Cohort_1 junte-se no final
3. Adicione índices clusterizados a todas as tabelas na coluna PersonID e adicione uma OPTION (MERGE JOIN)dica à consulta

Você também expressou preocupação com o fato de a consulta poder levar até 40 minutos durante o ETL, o que é muito mais longo do que o observado em seus testes. A maneira mais básica de solucionar isso é habilitar o Query Store e procurar diferenças entre quais logs do Query Store e o que você viu em seus próprios testes. Os planos de consulta são diferentes? As estatísticas de espera são diferentes? Uma consulta usa mais CPU que a outra? Todas essas informações estão disponíveis no SQL Server 2019. Você não precisa adivinhar se houve ou não bloqueio. O Query Store lhe dirá isso.

Em um comentário, você disse o seguinte:

nossos administradores não nos permitem ativar a sobrecarga d/t do armazenamento de consultas, o que é decepcionante

Considero esta uma declaração ofensiva. Os DBAs não funcionam para servir o banco de dados. Eles devem trabalhar para atender os usuários finais. Os usuários finais incluem você, o desenvolvedor. Se eles quiserem assumir a posição de que não podem ativar o Query Store devido à sobrecarga (o que é quase certamente errado), eles precisarão fornecer as informações necessárias de alguma outra forma. Pode ser necessário encaminhar esse problema para a gerência ou tentar encontrar algum tipo de compromisso. Por exemplo, se a carga de trabalho ETL for executada apenas à noite, talvez o Query Store possa ser ativado apenas durante a noite. Mas garanto que se os DBAs tivessem um problema pelo qual fossem responsáveis, eles habilitariam os diagnósticos necessários em vez de dar desculpas sobre sobrecarga.

Observe o plano real para quando a consulta for demorada e as estatísticas de espera dirão o porquê.

         <WaitStats>
          <Wait WaitType="CXPACKET" WaitTimeMs="290132" WaitCount="49920" />
          <Wait WaitType="HTMEMO" WaitTimeMs="3417" WaitCount="12" />
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="2579" WaitCount="563763" />
          <Wait WaitType="RESERVED_MEMORY_ALLOCATION_EXT" WaitTimeMs="2282" WaitCount="321487" />
          <Wait WaitType="SOS_PHYS_PAGE_CACHE" WaitTimeMs="1486" WaitCount="7410" />
          <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="155" WaitCount="1976" />
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="72" WaitCount="96" />
          <Wait WaitType="LATCH_EX" WaitTimeMs="66" WaitCount="2807" />
          <Wait WaitType="LATCH_SH" WaitTimeMs="43" WaitCount="7" />
          <Wait WaitType="HTBUILD" WaitTimeMs="8" WaitCount="12" />
        </WaitStats>
        <QueryTimeStats CpuTime="195151" ElapsedTime="79659" />

Ou ative o Query Store e observe as estatísticas de espera .

Do ponto de vista do design, é estranho ter várias tabelas de preparação de coorte separadas em vez de uma mais ampla. E usar columnstore em tabelas temporárias é estranho. Eles são caros para construir e é caro reconstruir e gerar todas as linhas para um columnstore. É mais barato fazer isso com um índice heap ou clusterizado.

Com base apenas no plano de consulta, você tem sugestões sobre qual direção devo seguir? Minha intuição me diz que posso melhorar minha indexação. Percebi que ele usa correspondências de hash e meu entendimento é que as junções de mesclagem podem melhorar isso, o que significa mexer na indexação. Também estou curioso para saber se preciso tirar minhas vendas e considerar uma abordagem que não seja uma série de LEFT JOINs.

Para um plano de consulta como esse, com índices columnstore como fonte para alguns dos operadores de aquisição de dados, eu iria na direção oposta.

1. Adicione índices columnstore a quaisquer tabelas que não os possuam atualmente
2. Use OPTION(HASH JOIN)no final da consulta
3. Se 8 ou mais núcleos estiverem disponíveis no servidor, adicione MAXDOP 8também a dica

As junções de mesclagem não suportam o modo em lote - e há pelo menos um em seu plano - onde as junções de hash o fazem.

Provavelmente seria melhor operar inteiramente em modo lote, em vez de ter algumas operações em modo linha e alternar em vários pontos do plano.

O objetivo do aumento do DOP é espalhar a distribuição de linhas por thread para que mais threads tenham menos linhas para processar simultaneamente, o que provavelmente também melhorará o tempo do relógio de parede ao custo de tempo adicional de CPU.

Além do que outro disse.

i) Observe o sinal de aviso no plano de consulta e elimine o aviso de conversão. Por que converter Personidpara varchar?

ii) Embora a consulta pareça muito simples, o design da tabela parece ser falho, devido ao qual o otimizador fica sobrecarregado. Ligado e média. cada temperatura contém dados de 33L. Muitos deles também são de tipo de dados grandes.

iii) Tente outro método, insira todos os personid e outras colunas primeiro Staging.CohortStagingTemp AS A. Em seguida, atualize Project.BaseTable_STGcom cada tabela temporária usando a junção interna, um, seja um.