Resolving a performance issue with BETWEEN join - eager spool
Ensure you have an index on T1 like:

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


You might reverse the key columns depending on which is more selective with your data set.

The optimizer tends to introduce an eager spool for one of the inequalities and leave the second one in a Filter. Repro:

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




With the index:



Naturally, the seek is only for the first index column. The second will be a residual predicate.

Generally, interval queries can be hard to optimize. Dejan Sarka and Itzik Ben-Gan have some good coverage on the topic:


Interval Queries in SQL Server Part 4 - Dejan
Interval Queries in SQL Server - Itzik