SQL执行计划异常：请求更多行反而更快，如何修复？

有一个包含CTE连接的TOP N查询，出现了异常的性能差异：

• 执行TOP 2431时耗时约30秒，执行TOP 2432（仅多1条）耗时不到2秒
• 最初发现是统计信息过期，更新统计信息并清理计划缓存后，TOP 2431恢复正常（耗时<2秒），但TOP 1500仍耗时29秒
• 快速执行计划使用Hash Match，慢速计划使用Lazy Table Spool，两者输入数据源完全一致
• EnquiryLegs表扫描使用的是Booking列索引（Bookings表的外键）

完整查询如下：

WITH EarliestDepartureDates AS (SELECT EnquiryLegs.Booking,
                MIN(DATETIMEFROMPARTS(
                    Legs.DepartureYear,
                    Legs.DepartureMonth,
                    ISNULL(Legs.DepartureDay, 1),
                    ISNULL(Legs.DepartureTime, 0) / 100,
                    ISNULL(Legs.DepartureTime, 0) % 100, 0, 0)) AS [Date]
            FROM dbo.EnquiryLegs
            JOIN dbo.Legs ON Legs.ID = EnquiryLegs.ID
            GROUP BY EnquiryLegs.Booking)
SELECT TOP 1500 Bookings.ID, Bookings.foo, ... 
, CustomerContacts.ID, CustomerContacts.bar ...
, BaseContacts.FriendlyId, BaseContacts.baz...
FROM Bookings
LEFT JOIN CustomerContacts ON CustomerContacts.ID = Bookings.CustomerContact
LEFT JOIN BaseContacts ON BaseContacts.ID = CustomerContacts.ID
INNER JOIN EarliestDepartureDates ON EarliestDepartureDates.Booking = Bookings.ID
ORDER BY Bookings.CreatedAt DESC

已执行的统计信息更新操作：

sp_updatestats

UPDATE STATISTICS Bookings WITH FULLSCAN
UPDATE STATISTICS EnquiryLegs WITH FULLSCAN
UPDATE STATISTICS BaseLegs WITH FULLSCAN

DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE ('SQL Plans');

1. 查询优化器的阈值误判：SQL Server查询优化器会根据预估返回行数选择执行计划。当TOP N的数值落在特定临界区间时，优化器会认为Lazy Table Spool（将中间结果暂存到tempdb，逐行按需读取）的成本更低，但实际数据分布导致该计划的IO开销远超预期，性能急剧下降。
2. 统计信息预估偏差：即便更新了统计信息，优化器对CTE聚合后的行数、以及与Bookings表关联后的行数预估仍可能存在偏差。尤其是当Bookings.CreatedAt的排序顺序与Booking外键的数据分布不匹配时，优化器误判了Lazy Spool的实际运行成本。
3. Lazy Table Spool的固有局限：当需要快速获取大量排序后的记录时，Lazy Spool会因反复回源读取数据、加上tempdb的额外IO开销，导致性能远不如Hash Match——后者能更高效地处理较大数据量的关联操作。

以下是几种可行的修复方式，可根据实际场景选择：

1. 强制使用Hash Match连接提示 #

在关联CTE的语句中添加连接提示，直接指定优化器使用Hash Match：

INNER JOIN EarliestDepartureDates WITH (HASH JOIN) ON EarliestDepartureDates.Booking = Bookings.ID

2. 改用临时表存储CTE结果 #

将CTE的聚合结果存入临时表，并为Booking列创建索引，让优化器能基于更明确的数据分布选择高效计划：

SELECT EnquiryLegs.Booking,
       MIN(DATETIMEFROMPARTS(
           Legs.DepartureYear,
           Legs.DepartureMonth,
           ISNULL(Legs.DepartureDay, 1),
           ISNULL(Legs.DepartureTime, 0) / 100,
           ISNULL(Legs.DepartureTime, 0) % 100, 0, 0)) AS [Date]
INTO #EarliestDepartureDates
FROM dbo.EnquiryLegs
JOIN dbo.Legs ON Legs.ID = EnquiryLegs.ID
GROUP BY EnquiryLegs.Booking

CREATE CLUSTERED INDEX IX_Temp_EarliestDepartureDates_Booking ON #EarliestDepartureDates(Booking)

SELECT TOP 1500 Bookings.ID, Bookings.foo, ... 
, CustomerContacts.ID, CustomerContacts.bar ...
, BaseContacts.FriendlyId, BaseContacts.baz...
FROM Bookings
LEFT JOIN CustomerContacts ON CustomerContacts.ID = Bookings.CustomerContact
LEFT JOIN BaseContacts ON BaseContacts.ID = CustomerContacts.ID
INNER JOIN #EarliestDepartureDates ON #EarliestDepartureDates.Booking = Bookings.ID
ORDER BY Bookings.CreatedAt DESC

DROP TABLE #EarliestDepartureDates

3. 创建覆盖索引优化CTE聚合 #

为EnquiryLegs和Legs的关联查询创建覆盖索引，减少扫描开销，同时让优化器更准确地预估行数：

CREATE NONCLUSTERED INDEX IX_EnquiryLegs_Booking_ID ON dbo.EnquiryLegs(Booking) INCLUDE (ID)
CREATE NONCLUSTERED INDEX IX_Legs_ID_Departure ON dbo.Legs(ID) INCLUDE (DepartureYear, DepartureMonth, DepartureDay, DepartureTime)

4. 强制重新生成执行计划 #

在查询末尾添加OPTION (RECOMPILE)，让优化器针对当前TOP N的数值重新计算最优计划，避免复用低效的缓存计划：

...
ORDER BY Bookings.CreatedAt DESC
OPTION (RECOMPILE)

内容的提问来源于stack exchange，提问作者Brondahl