Busca de índice e push de predicado para baixo

Solução

Você pode usar uma FORCESEEKdica expandida com as teclas de busca necessárias:

FROM dbo.MyTable AS M
    WITH (FORCESEEK(IDX(WTID, OETID, SRID)))

Por exemplo:

IF EXISTS 
(
    SELECT T.*
    FROM
    (
        SELECT I.RowID, I.OETID
        FROM @InMemoryTableTypeTable AS I
        UNION
        SELECT D.RowID, D.OETID
        FROM @InMemoryTableTypeTable AS D
    ) AS T
    WHERE    
        NOT EXISTS 
        (
            SELECT M.*
            FROM dbo.MyTable AS M 
                WITH 
                (
                    FORCESEEK(IDX(WTID, OETID, SRID)), -- !CHANGED!
                    ROWLOCK,
                    UPDLOCK
                )
            WHERE    
                M.OETID = T.RowID
                AND M.SRID = T.OETID
                AND M.WTID = @WTID
                AND M.[Status] <> 1
                AND M.SRID > 0
        )
)
...

Explicação

Como você percebeu, a causa é a estimativa de custos. As consultas introduzidas por EXISTSvêm com uma linha goal , o que complica as coisas. Juntamente com uma consulta que contém vários outros recursos difíceis de estimar, esse é o tipo de situação em que uma dica pode ser necessária para obter consistentemente o formato do plano desejado.

Custeio e exploração

Os planos 'bons' e 'ruins' têm custos totais estimados muito baixos, de modo que o otimizador não gasta muito tempo pesquisando (observe o motivo do Plano Bom o Suficiente Encontrado para o encerramento antecipado no operador raiz).

O otimizador considera muitas alternativas que você não vê refletidas no plano final. Sem o baixo custo encontrado antecipadamente, continuaria a considerar outras estratégias como a ilustrada abaixo, que empurra a anti-semi-junção (como uma aplicação ) abaixo da união :

Independentemente disso, o otimizador não está 'confuso com UNION' - ele simplesmente não chegou ao ponto de considerar um APPLY, apenas uma junção (que poderia ser implementada como loops aninhados, hash ou mesclagem).

O plano de junção de loops aninhados tem uma busca no lado interno, mas esse é o predicado não correlacionado WTID = @WTID, que também pode aparecer em um hash ou junção de mesclagem. Os predicados restantes estão todos correlacionados, portanto, exigiriam uma aplicação para serem empurrados para baixo. Veja meu artigo, Aplicar versus Nested Loops Join se os conceitos não estiverem claros.

IF EXISTS

Normalmente, você poderia usar uma OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'))dica de consulta para desabilitar o comportamento da meta de linha, o que provavelmente também produziria de forma confiável o plano desejado.

Infelizmente, as dicas de consulta se aplicam apenas à consulta de nível superior ( IF EXISTSaqui) e não à consulta aninhada (aquela com a qual você está preocupado). Você viu planos diferentes ao adicionar dicas, mas isso ocorreu porque o texto da consulta era diferente e exigia uma nova compilação.

Usando IF EXISTS, você precisaria definir o sinalizador de rastreamento documentado 4138 para desabilitar o objetivo da linha. Teria que ser definido no nível da sessão (usando DBCC TRACEON), porque QUERYTRACEONtambém se aplica apenas ao nível superior.

Você pode evitar esse comportamento não óbvio usando um padrão como o seguinte, em vez de IF EXISTS(veja as perguntas e respostas relacionadas abaixo)

DECLARE @Exists bit;

SELECT @Exists =
    IIF
    (
        EXISTS
        (
            --
            -- The query without IF EXISTS
            --
        ),
        CONVERT(bit, 'true'),
        CONVERT(bit, 'false')
    )
-- Query hints are effective now
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

IF @Exists = CONVERT(bit, 'true')
BEGIN
    --
    -- Thing to do IF EXISTS
    --
END;

Menciono tudo isso por interesse. Como você já está usando várias dicas, expandir FORCESEEKé o caminho a seguir.

Perguntas e respostas relacionadas:

• IF EXISTS demora mais do que a instrução select incorporada

Por alguma razão pouco clara, o compilador parece estar ficando confuso com o UNIONe não empurrando os NOT EXISTSpredicados anti-junção até o fim e, portanto, não correspondendo totalmente ao índice.

As seguintes coisas parecem não fazer diferença:

• Alterando a ordem dos predicados.
• Garantir que os tipos de dados correspondam: RowIdé inte deveria ser bigint(você deve fazer isso de qualquer maneira).
• Mudando UNIONpara UNION ALL(você deve fazer isso de qualquer maneira).
• Usando um EXCEPTem vez de WHERE NOT EXISTS.
• Usando GROUP BYpara obter um conjunto externo unificado.
• Usando COUNT(*) = 0.
• Mudando para um índice não filtrado.
• Alterando o PK do tipo de tabela para clusterizado (você deve fazer isso de qualquer maneira).
• Adicionando FORCESEEKou INDEXdicas.

A única solução alternativa que consigo ver é repetir o arquivo WHERE NOT EXISTS. Você pode usar OR EXISTSou pode usar UNION ALLpara isso.

IF EXISTS (SELECT 1
 FROM @i t
 WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.MyTable m WITH (UPDLOCK, ROWLOCK)
     WHERE (m.WTID = @WTID)
      AND (m.OETID = t.OETID)
      AND (m.SRID = t.RowID)
      AND (m.Status <> 1)
      AND (m.SRID > 0)
   )
) OR EXISTS (SELECT 1
 FROM @d t
 WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.MyTable m WITH (UPDLOCK, ROWLOCK)
     WHERE (m.WTID = @WTID)
      AND (m.OETID = t.OETID)
      AND (m.SRID = t.RowID)
      AND (m.Status <> 1)
      AND (m.SRID > 0)
   )
)