Imagine writing a query where sometimes you join a handful of rows and other times you process millions—traditionally the optimizer had to pick one strategy up front, often leading to suboptimal plans. Adaptive joins, introduced in SQL Server 2017 and refined in SQL Server 2019 as part of Intelligent Query Processing, change that by deferring the choice of join algorithm until runtime. After scanning the build input, the engine compares the actual row count against an Adaptive Threshold Rows value and then dynamically picks either a batch-mode hash join or a row-mode nested loops join—all within a single cached plan learn.microsoft.com.
This runtime flexibility not only helps avoid the classic parameter sniffing pitfalls—since one adaptive plan can handle a wide range of cardinalities—but also delivers more consistent performance across diverse workloads. By switching to the most efficient join method on the fly, adaptive joins boost throughput and stability for your critical queries brentozar.com.
SELECT a.StateName
,a.CityName
,sum(flights)
FROM Airports a
INNER JOIN normalization_index n ON a.AirportSeqId = n.OriginAirportSeqID
WHERE a.AirportSeqId > 1474703
GROUP BY a.StateName
,a.CityName
SELECT a.StateName
,a.CityName
,sum(flights)
FROM Airports a
INNER JOIN normalization_index n ON a.AirportSeqId = n.OriginAirportSeqID
WHERE a.AirportSeqId = 1451202
GROUP BY a.StateName
,a.CityName
SELECT a.StateName
,a.CityName
,sum(flights)
FROM Airports a
INNER JOIN normalization_index n ON a.AirportSeqId = n.OriginAirportSeqID
WHERE a.AirportSeqId > 1474703
GROUP BY a.StateName
,a.CityName
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'))
Please Consider Subscribing
