Muitas junções de "valor de chave" fazem com que a consulta do SQL Server diminua exponencialmente

Em geral

O tempo de compilação de uma consulta com um grande número de junções pode ser muito variável.

Uma razão para isso é que existem N! maneiras de ordenar as junções internas de N tabelas. Para N pequeno, isso não é um problema. Para N maior pode ser um problema, mesmo que o otimizador não tente uma busca exaustiva do possível espaço de planta.

Você deve ter notado que o plano de execução fornecido não acessa as tabelas na mesma ordem em que as escreveu. Por exemplo, a tabela DataMigrationEventObject (aliasada como 'obj') é acessada primeiro. Não há um único índice óbvio disponível, portanto, o otimizador cria uma interseção de índice (usando dois índices não clusterizados) e uma pesquisa de chave (buscando a coluna TargetObjectKey1 ) para implementar fisicamente essa pequena parte de sua consulta.

O otimizador também não se limita a considerar diferentes ordens de junção. Ele pode explorar diferentes algoritmos de junção (hash, merge, nested loop, apply), diferentes estratégias de indexação, posicionamento e tipo de operadores sem junção, paralelismo e assim por diante.

O otimizador possui heurísticas para evitar gastar muito tempo procurando um plano melhor se o melhor que encontrou até agora for razoável. Dada uma consulta razoavelmente direta, um design lógico de banco de dados decente e uma implementação física adequada (por exemplo, tipos de dados e índices), o otimizador geralmente encontrará rapidamente um bom plano de baixo custo. Esta parece ter sido sua experiência no passado.

Esse arranjo ainda pode dar errado de várias maneiras. Se a ordem de junção inicial escolhida pelo otimizador estiver muito longe de ser um custo razoavelmente baixo, muito trabalho de transformação precisará ser feito para chegar à linha de chegada. Se o banco de dados não tiver bons índices de suporte, o otimizador pode gastar muito tempo tentando encontrar uma estratégia decente.

Se as informações de contagem de linhas e distribuição de valor não puderem ser derivadas com precisão (não se limitando a estatísticas de tabela base desatualizadas), os candidatos a fragmentos de plano poderão ser avaliados incorretamente. Isso pode facilmente levar à violação de limites heurísticos e ao otimizador gastar uma quantidade excessiva de esforço explorando, implementando e custeando alternativas.

Quando isso acontece, às vezes a única solução é expressar o requisito de dados usando uma sintaxe diferente, um algoritmo diferente (como na sugestão do pivô) ou dividir a operação em etapas separadas mais simples com tabelas temporárias pequenas e bem indexadas como retenção áreas.

Em geral, parece mais provável que o problema no seu caso seja uma distribuição de dados específica que leva a custos iniciais imprecisos e esforço excessivo do otimizador como resultado.

Especificamente

Sua consulta e banco de dados têm vários problemas que os tornam vulneráveis ​​ao tempo excessivo de compilação do plano. Não vou listar todos eles porque um esquema completo não foi fornecido e o texto da consulta encontrado no plano de execução fornecido está truncado em um ponto crucial. A resposta seria muito longa mesmo se todas as informações necessárias fossem fornecidas. Alguns pontos:

• A indexação nas tabelas base não é ideal (veja os comentários sobre a interseção de índice e pesquisa acima)
• O índice clusterizado denominado pk (para chave primária?) na #attrstabela temporária não está marcado como UNIQUE. As chaves são únicas por definição. Não fornecer essa garantia crucial ao otimizador significa que ele não pode saber que um predicado de igualdade no ID e tipo do objeto retornará no máximo uma linha. Também existem outras consequências, muitas para listar, mas, como exemplo, uma junção de mesclagem teria que ser do tipo muitos para muitos, em vez de um para muitos. Essas coisas são importantes para exploração e custeio.
• A consulta usa a sintaxe de junção aninhada em torno do alias 'int_att'. Isso pode ou não ser evitável, mas torna a árvore lógica inicial gerada como entrada para o otimizador mais complexa. A complexidade desnecessária pode facilmente levar a um processamento abaixo do ideal.
• A #attrstabela tem um índice duplicado chamado 'clus'. Isso foi mencionado na pergunta, mas é um exemplo de uma escolha de indexação não óbvia apresentada ao otimizador sem ganho. Torne o índice clusterizado uma UNIQUEchave adequada.
• A consulta para carregar a #attrstabela tem um predicado duplicado DataMigrationEventID = @MigrationEventID. Isso é inofensivo, mas a atenção aos detalhes é importante ao lidar com computadores (eles são muito literais).
• O banco de dados está travado no nível de compatibilidade 130 (SQL Server 2016). A atualização para a última CU de 2019 está muito bem, mas o nível de compatibilidade substitui muitas melhorias. Se você ainda não tiver correções do otimizador habilitadas para o banco de dados, pense em ativá-las também.
• Não há nenhum benefício (apenas custo) em criar a #theClientstabela temporária usando SELECT INTOo retorno imediato de todos os resultados dessa tabela. As tabelas temporárias são uma poderosa ferramenta de ajuste quando usadas corretamente. Este não é um exemplo de tal uso.

Reprodução

Para quem deseja gerar um plano localmente, consegui inferir o seguinte a partir da pergunta e do plano de execução. Pode haver algumas omissões e imprecisões. Naturalmente, não há estatísticas precisas, apenas uma cardinalidade de tabela bruta:

Tabelas

CREATE TABLE dbo.DataMigrationEventObject
(
    DataMigrationEventObjectID integer NOT NULL PRIMARY KEY,
    DataMigrationEventID integer NOT NULL,
    DataMigrationObjectType varchar(255) NOT NULL,
    TargetObjectKey1 integer NOT NULL,
    TargetObjectKey2 integer NOT NULL,
    TargetObjectKey3 integer NOT NULL,
    IsCompleted bit NOT NULL,
    IsDeleted bit NOT NULL,

    INDEX DataMigrationEventID (DataMigrationEventID),
    INDEX DataMigrationObjectType (DataMigrationObjectType)
);

UPDATE STATISTICS dbo.DataMigrationEventObject WITH ROWCOUNT = 8413, PAGECOUNT = 841;

CREATE TABLE dbo.DataMigrationEventObjectAttribute
(
    DataMigrationEventObjectID integer NOT NULL PRIMARY KEY,
    DataMigrationEventID integer NOT NULL,
    AttributeType varchar(128) NOT NULL,
    AttributeValue sql_variant NOT NULL,
    IsDeleted bit NOT NULL,
);

UPDATE STATISTICS dbo.DataMigrationEventObjectAttribute WITH ROWCOUNT = 56900, PAGECOUNT = 56900;

CREATE TABLE dbo.#attrs
(
    DataMigrationEventObjectID integer NOT NULL,
    AttributeType varchar(128) NOT NULL,
    AttributeValue varchar(255) NULL
);

CREATE CLUSTERED INDEX pk ON #attrs (DataMigrationEventObjectID, AttributeType);

DECLARE @MigrationEventID integer = 23;

INSERT INTO #attrs
SELECT 
    dmeo.DataMigrationEventObjectID,
    a.AttributeType,
    CAST(a.AttributeValue as varchar(255)) AttributeValue  
FROM DataMigrationEventObject dmeo
JOIN DataMigrationEventObjectAttribute a
    ON a.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
WHERE 
    dmeo.DataMigrationEventID = @MigrationEventID
    AND dmeo.DataMigrationObjectType IN ('BpClient','xxxxxxxClientMapping')
    AND dmeo.DataMigrationEventID = @MigrationEventID;

CREATE INDEX clus ON #attrs (DataMigrationEventObjectID, AttributeType);

UPDATE STATISTICS #attrs WITH ROWCOUNT = 2173, PAGECOUNT = 21;

Consulta

DECLARE @MigrationEventID integer = 23;

SELECT 
    dmeo.DataMigrationEventObjectID xxObjectID,
    a_clientid.AttributeValue ClientID,
    a_tnt.AttributeValue ClientExternalID,
    a_tnt.AttributeValue TenantID,
    int_att.AttributeValue ClientInternalID,
    obj_gvn.AttributeValue GivenName,
    obj_oth.AttributeValue OtherName,
    obj_fam.AttributeValue FamilyName,
    obj_pref.AttributeValue PreferredName,
    obj_title.AttributeValue Title,
    obj_dob.AttributeValue DateOfBirth,
    obj_email.AttributeValue Email,
    obj_hphone.AttributeValue HomePhone,
    obj_wphone.AttributeValue WorkPhone,
    obj_add1.AttributeValue Address1,
    obj_add2.AttributeValue Address2,
    obj_sub.AttributeValue Suburb,
    obj_state.AttributeValue StateID,
    obj_post.AttributeValue Postcode,
    obj_sex.AttributeValue SexID
    /*,
    obj_del.AttributeValue IsDeleted,
    obj_inact.AttributeValue IsInactive,
    obj_pref_corr.AttributeValue PreferredCorrespondenceMethodID,
    obj_eth.AttributeValue EthnicityID
    obj_em_title.AttributeValue EmergencyContactTitle,
    obj_em_surn.AttributeValue EmergencyContactSurname,
    obj_em_gvn.AttributeValue EmergencyContactGivenName,
    obj_em_add.AttributeValue EmergencyContactAddress,
    obj_em_sub.AttributeValue EmergencyContactSuburb,
    obj_em_post.AttributeValue EmergencyContactPostcode,
    obj_em_phone.AttributeValue EmergencyContactPhone,
    obj_em_phone2.AttributeValue EmergencyContactPhone2,
    obj_em_rel.AttributeValue EmergencyContactRelationship,
    obj_nok_title.AttributeValue NextOfKinContactTitle,
    obj_nok_surn.AttributeValue NextOfKinContactSurname,
    obj_nok_gvn.AttributeValue NextOfKinContactGivenName,
    obj_nok_add.AttributeValue NextOfKinContactAddress,
    obj_nok_sub.AttributeValue NextOfKinContactSuburb,
    obj_nok_post.AttributeValue NextOfKinContactPostcode,
    obj_nok_phone.AttributeValue NextOfKinContactPhone,
    obj_nok_phone2.AttributeValue NextOfKinContactPhone2,
    obj_nok_rel.AttributeValue NextOfKinContactRelationship*/
INTO #theClients
FROM DataMigrationEventObject dmeo 
  INNER JOIN #attrs a_clientid 
  ON a_clientid.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
  AND a_clientid.AttributeType = 'ClientID'
  INNER JOIN #attrs a_clientextid 
  ON a_clientextid.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
  AND a_clientextid.AttributeType = 'ClientExternalID'
  INNER JOIN #attrs a_tnt 
  ON a_tnt.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
  AND a_tnt.AttributeType = 'TenantID'
  INNER JOIN (
      #attrs int_att
      INNER JOIN DataMigrationEventObject obj 
      ON obj.DataMigrationEventObjectID = int_att.DataMigrationEventObjectID
      AND obj.DataMigrationObjectType = 'BpClient'
     ) 
  ON int_att.AttributeType = 'InternalID'
  AND obj.DataMigrationEventID = dmeo.DataMigrationEventID
  AND obj.TargetObjectKey1 = cast(a_clientextid.AttributeValue as int)  
      INNER JOIN #attrs obj_gvn 
      ON obj_gvn.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_gvn.AttributeType = 'GivenName'
      INNER JOIN #attrs obj_oth 
      ON obj_oth.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_oth.AttributeType = 'OtherName'
      INNER JOIN #attrs obj_fam 
      ON obj_fam.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_fam.AttributeType = 'FamilyName'
      INNER JOIN #attrs obj_pref 
      ON obj_pref.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_pref.AttributeType = 'PreferredName'
      INNER JOIN #attrs obj_title 
      ON obj_title.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_title.AttributeType = 'Title'
      INNER JOIN #attrs obj_dob 
      ON obj_dob.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_dob.AttributeType = 'DateOfBirth'
      INNER JOIN #attrs obj_email 
      ON obj_email.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_email.AttributeType = 'Email'
      INNER JOIN #attrs obj_hphone 
      ON obj_hphone.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_hphone.AttributeType = 'HomePhone'
      INNER JOIN #attrs obj_wphone 
      ON obj_wphone.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_wphone.AttributeType = 'WorkPhone'
      INNER JOIN #attrs obj_add1 
      ON obj_add1.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_add1.AttributeType = 'Address1'
      INNER JOIN #attrs obj_add2 
      ON obj_add2.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_add2.AttributeType = 'Address2'
      INNER JOIN #attrs obj_sub 
      ON obj_sub.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_sub.AttributeType = 'Suburb'
      INNER JOIN #attrs obj_state 
      ON obj_state.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_state.AttributeType = 'StateID'
      INNER JOIN #attrs obj_post 
      ON obj_post.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_post.AttributeType = 'Postcode'
      INNER JOIN #attrs obj_sex 
      ON obj_sex.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      AND obj_sex.AttributeType = 'SexID'
      --INNER JOIN #attrs obj_del 
      --ON obj_del.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      --AND obj_del.AttributeType = 'IsDeleted'
      --INNER JOIN #attrs obj_inact
      --ON obj_inact.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      --AND obj_inact.AttributeType = 'IsInactive'
      --INNER JOIN #attrs obj_pref_corr
      --ON obj_pref_corr.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      --AND obj_pref_corr.AttributeType = 'PreferredCorrespondenceMethodID'
      --INNER JOIN #attrs obj_eth
      --ON obj_eth.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
      --AND obj_eth.AttributeType = 'EthnicityID'
WHERE 
    dmeo.DataMigrationEventID = @MigrationEventID
    AND dmeo.IsDeleted = 0
    AND dmeo.DataMigrationObjectType = 'xxxxxxxClientMapping';

Essa demonstração não reproduz o tempo de compilação excessivo em minha instância do SQL Server 2019 no nível de compatibilidade 130, mas isso não é inesperado, dada a falta de estatísticas precisas e provavelmente configuração diferente de hardware e instância (principalmente memória), o que afeta a escolha do plano.

Um tempo de compilação mais longo (e um plano bem maluco) pode ser reproduzido usando o modelo de cardinalidade original. Adicione a seguinte dica à consulta final:

OPTION (MAXDOP 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))

Eu usei MAXDOP 1desde que sua instância está configurada dessa maneira.

Não tenho certeza se isso resolverá seu problema, mas você já tentou dinamizar o dicionário #attrs e, em seguida, ingressar nele? Acho que, às vezes, adotar uma abordagem diferente pelo menos fará com que funcione. Algo como o abaixo.

;WITH CTE_PVT AS
    (
    SELECT DataMigrationEventObjectID
        , ClientID 
        , ClientExternalID
        , TenantID
        , GivenName
        , OtherName
        , FamilyName
        , PreferredName
        , Title
    FROM [#attrs] AS A
        PIVOT (MAX([AttributeValue]) FOR [AttributeType] IN (ClientID, ClientExternalID, TenantID, GivenName
                                                                , OtherName, FamilyName, PreferredName, Title)) AS pvt 
    )
SELECT dmeo.[DataMigrationEventObjectID]
    , P.ClientID 
    , P.ClientExternalID
    , P.TenantID
    , P.GivenName
    , P.OtherName
    , P.FamilyName
    , P.PreferredName
    , P.Title
FROM DataMigrationEventObject dmeo
    LEFT OUTER JOIN CTE_PVT AS P ON P.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID