Resolvendo um problema de desempenho com junção BETWEEN - spool ansioso

Certifique-se de ter um índice em T1 como:

CREATE INDEX i ON T1 (dtstart, dtend) INCLUDE (col1);

Você pode inverter as colunas-chave dependendo de qual é mais seletiva com seu conjunto de dados.

O otimizador tende a introduzir um spool ansioso para uma das desigualdades e deixar a segunda em um Filtro. Reprodução:

CREATE TABLE #T1 (col1 integer, dtstart datetime, dtend datetime);
CREATE TABLE #T2 (col2 integer, col3 integer, col4 integer, col5 integer, dtcol datetime);

UPDATE STATISTICS #T1 WITH ROWCOUNT = 5000;
UPDATE STATISTICS #T2 WITH ROWCOUNT = 100000;

SELECT
    T1.col1,
    T2.col2,
    T2.col3,
    T2.col4
FROM #T1 AS T1
JOIN #T2 AS T2 ON T2.dtcol BETWEEN T1.dtstart AND T1.dtend
WHERE T2.col5 IN (1, 2)
OPTION (QUERYTRACEON 8690); -- No perf spool

Com o índice:

Naturalmente, a busca é apenas para a primeira coluna do índice. O segundo será um predicado residual.

Geralmente, as consultas de intervalo podem ser difíceis de otimizar. Dejan Sarka e Itzik Ben-Gan têm uma boa cobertura sobre o assunto:

• Consultas de intervalo no SQL Server Parte 4 - Dejan
• Consultas de intervalo no SQL Server - Itzik