A seção de custo mais alto no Plano de Execução Real não mostra nenhuma leitura lógica?

O % de custo ainda é apenas uma estimativa, mesmo em um Plano de Execução Real. Você precisa observar as estatísticas de nível de origem da linha aqui para saber quanto tempo cada etapa levou.

A julgar pelo XML, 274ms foram levados até o ponto do hash join no nó 7, 72ms disso foi apenas a varredura de cluster do Pk_TeamMembers(nó 8) e 35ms foi até o Left Semi Join (nó 9). Isso significa que impressionantes 167ms foram usados ​​apenas para executar a etapa Hash Join (provavelmente atribuindo memória para as 263.600 linhas de Pk_TeamMembers).

Parece que você já tentou segmentar essa junção antes:

LEFT JOIN TeamMembers tm on AF.CompletionUserID = tm.UserID AND AF.TeamID = tm.TeamID AND tm.TeamID = t.TeamID -- Overloaded to help SQL find indexes

Você provavelmente deseja fazer um loop aninhado (externo) para esse índice. O planejador de consulta decidiu usar uma junção de hash porque acha que você obterá 59.000 linhas do outro lado dessa junção, quando na verdade você obtém 3.

Você tem escolhas

• Dica para que uma junção de loop aninhada seja usada
• Descubra por que o planejador acha que terá tantas linhas e ajude-o (estatísticas, simplifique a filtragem)

A dica seria como

LEFT LOOP JOIN TeamMembers tm on AF.CompletionUserID = tm.UserID AND AF.TeamID = tm.TeamID AND tm.TeamID = t.TeamID

A segunda escolha será particularmente difícil devido à complexidade de seus filtros de direção. Uma maneira de fazer isso seria inserir as linhas que correspondem aos seus principais filtros de condução e, em seguida, juntar-se a essa tabela temporária em sua consulta principal. Aqui, parece que você precisa juntar 3 das 5 tabelas primeiro para utilizar seus @userId@filtros de forma justa:

FROM ActivityFeed AF
    JOIN ActivityType A ON AF.ActivityType = A.ActivityType
    LEFT JOIN Teams t on AF.TeamID = t.TeamID
WHERE 
    AF.SearchIndexUpdateDate IS NULL -- Only get items that aren't in the search index
    AND
    AF.Active=1 
    AND 
    A.Active=1
    AND
    (A.AdminOnly = 0 OR AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE x.IsAdmin=1))
    AND
    ( 
      (
        A.TeamOnly=1 
        AND AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE A.HideWhenOwnerless = 0 OR x.IsOwnerless = 0)
        AND (A.ShowSelf=1 OR AF.CompletionUserID <> @UserID)
        AND ISNULL(t.IsDeleted,0) = 0 AND t.active=1
        AND (A.IncludeOnsite = 1 OR EXISTS(SELECT 1 FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE x.TeamID = AF.TeamID AND x.IsOnsiteOwner = 0))
       )
       OR 
      (
        A.UserOnly=1 
        AND AF.UserID=@UserID
      )
    )
    AND
    -- Don't included moderated activity (owner moderation actions are in Group activity view API_GetActivityFeedByTeam)
    AF.Moderated = 0 
    AND
   -- don't show completions or plan completions to other team members if opted out (tm.ShowUserActivityInFeed)
    (ISNULL(A.ShowOnlyWhenUserActivityActive,0) = 0 OR (A.ShowOnlyWhenUserActivityActive = 1 AND tm.ShowUserActivityinFeed = 1))
    AND
    (AF.TeamID IS NULL OR AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser as x))

Você também tem uma pesquisa de chave na tabela TeamMembers, isso está consumindo 14% do tempo de execução. Você deve tentar criar um índice de conversão melhor para evitar esse tipo de operador em um plano de execução.

A coluna ausente que poderia, por exemplo, ser adicionada no INCLUDE é IsSiemensOnsiteAdmin

Além disso, você deve considerar modificar este código para usar CTE em vez de fazer 2 vezes a mesma consulta para colunas diferentes

        (SELECT 
            L.Locale,
            AFC.LocalizedMessage
        FROM 
            ActivityFeedCache AFC 
            JOIN Locales L ON AFC.LocaleID=L.LocaleID 
        WHERE ActivityFeedID=AF.ActivityFeedID
        FOR JSON PATH) AS MessageJSON,
        (SELECT 
            L.Locale,
            AFC.LocalizedTeamMessage AS LocalizedMessage
        FROM 
            ActivityFeedTeamCache AFC 
            JOIN Locales L ON AFC.LocaleID=L.LocaleID 
        WHERE ActivityFeedID=AF.ActivityFeedID
        FOR JSON PATH) AS TeamMessageJSON