TSQL Slow Query, não usando o índice conforme o esperado

Parece que você editou manualmente o XML e o tornou inválido, principalmente adicionando caracteres inválidos como <e >. Depois de corrigir alguns problemas, consegui carregar o plano no SSMS e no Plan Explorer:

Isso mostra que você tem um índice chamado [IX__dbo_table1__DateRange]—não mencionado na pergunta. A julgar pelo predicado de busca, esse índice tem pelo menos Col1e ValidTonas chaves de índice.

Outro problema é o uso de IF EXISTS. Isso introduz um objetivo de linha , que faz com que o otimizador favoreça uma solução de loops aninhados. Veja as perguntas e respostas relacionadas IF EXISTS demorando mais do que a instrução select incorporada .

Dito isso, encontrar qualquer possível intervalo de sobreposição é um problema difícil de resolver completamente com um índice b-tree, consulte Resolvendo um problema de desempenho com BETWEEN join - spool ansioso .

Sem conhecer a definição completa da tabela, índices e distribuição de dados, é difícil sugerir uma solução adequada. Se você quer apenas algo rápido e fácil de tentar sem alterar muito os índices ou a consulta de origem, tente uma dica de junção de hash:

IF EXISTS (
    SELECT  1/0 
    FROM dbo.table1 src
      INNER HASH JOIN dbo.table1 tgt -- hint added
        ON tgt.Col1 = src.Col1
    WHERE tgt.ValidFrom <= src.ValidTo
    AND tgt.ValidTo >= src.ValidFrom
    AND tgt.RecordId <> src.RecordId  
)
 BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
 END ;

Isso fará a varredura completa do índice duas vezes, mas isso pode não ser tão ruim se o seu sistema puder lidar com os requisitos de memória ou de E/S e se o paralelismo ou a execução em modo de lote estiver disponível. Isso funcionará melhor se houver um número razoável de Col1valores diferentes.

Supondo que as sobreposições não sejam permitidas, minha preferência seria evitar que a situação ocorresse em primeiro lugar usando restrições. Consulte Qual é a maneira correta de garantir entradas exclusivas em um design de banco de dados temporal?

Ou, como ypercubeᵀᴹ sugeriu no chat:

IF EXISTS
(
    SELECT 1/0
    FROM
    (
        SELECT 
            T.ValidFrom,
            PrevValidTo =
                LAG(T.ValidTo) OVER ( 
                    PARTITION BY T.Col1
                    ORDER BY T.ValidFrom)
        FROM dbo.table1 AS T
    ) AS T1
    WHERE 
        T1.PrevValidTo >= T1.ValidFrom
)
BEGIN
    RAISERROR('Overlap detected in dbo.table1', 11, 1); 
END;

Com um índice como:

CREATE NONCLUSTERED INDEX [IX__table1] 
ON dbo.table1
    (Col1, ValidFrom) 
INCLUDE 
    (ValidTo, RecordId);

Atualmente todos os registros serão lidos porque você está consultando os registros onde tgt.Col1 = src.Col1, que é a tabela completa.

Você deve tornar o índice mais seletivo adicionando ValidFrome/ou ValidToao índice.

Ao adicioná-los ao índice, essas colunas podem ser removidas após as partes INCLUDE.