Adaptive Joins: When the Plan Picks Its Join at Run Time

A request for one customer and a request for every customer need different join strategies. With adaptive joins, SQL Server can postpone part of that choice until rows arrive. The actual plan tells you which branch ran.

Let the Input Decide the Branch

A hash join handles a large input by building a hash structure. Nested loops works well when a small input can find matching rows efficiently. Estimates decide between those approaches in a conventional plan. An Adaptive Join operator preserves a runtime choice between them, within one compiled execution plan.

I look for this operator when the same query handles uneven input sizes. It addresses one uncertain join decision. It doesn't redesign the entire plan after execution starts.

Join order, access paths, and other operators still reflect compilation. Keep that boundary clear before expecting it to rescue every parameter problem.

Support for adaptive joins began in SQL Server 2017 at compatibility level 140. It requires batch mode eligibility. A columnstore input is a familiar route.

Batch mode on rowstore, available in recent versions, creates other eligible cases. Eligibility still doesn't guarantee the optimizer will select this physical operator for your particular query.

Build an Example That Qualifies for Adaptive Joins

Use a disposable database for these objects. The fact table has a clustered columnstore index. Its dimension uses a primary key for a simple match.

The values below are deliberate sample input. They don't establish a row threshold or runtime result on your system. Populate representative data when evaluating a real workload.

The grouping query joins the dimension to filtered facts. Try different predicate values without changing its logical meaning. Ask for an actual execution plan in SSMS.

On my SQL Server 2025 test database at compatibility level 170, the query returned Frame 10 and Wheel 4. The actual plan showed an Adaptive Join operator, and its actual join type was Nested Loops because only three rows passed the filter.

A small demonstration can produce another legitimate operator. That doesn't show a failure. The optimizer weighs the available alternatives using the data and configuration it sees.

Check the database compatibility level before interpreting a missing feature. Also inspect existing hints, forced plans, and database scoped settings. Those influence the optimizer's choices.

Don't add a hint to manufacture an operator for a screenshot. The exercise is understanding a real decision, not collecting a fashionable plan icon.

CREATE TABLE dbo.AdaptiveProducts
(
    ProductId int NOT NULL PRIMARY KEY,
    ProductName nvarchar(40) NOT NULL
);
CREATE TABLE dbo.AdaptiveSales
(
    ProductId int NOT NULL,
    Quantity int NOT NULL
);
INSERT dbo.AdaptiveProducts VALUES (1,N'Wheel'),(2,N'Frame');
INSERT dbo.AdaptiveSales VALUES (1,1),(1,4),(2,2),(2,8);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_AdaptiveSales ON dbo.AdaptiveSales;

Read the Threshold Where Adaptive Joins Switch Branches

Select the Adaptive Join operator in the actual plan. Its Adaptive Threshold Rows property is the dividing point calculated at compilation. SQL Server counts the build input as it arrives.

That runtime count drives the branch choice. The threshold belongs to this compiled plan, not to every query on the instance.

Below the threshold, nested loops becomes the selected route. Above it, the hash route is used. The plan displays the actual join type so you can distinguish the chosen branch from available alternatives.

An unused branch still appears in the graphical plan. Its presence doesn't mean both joins processed your request fully.

I read the threshold beside actual and estimated rows on the input. That comparison explains why runtime flexibility was useful. A severe estimate error still deserves attention.

Statistics, parameter values, and data skew remain relevant. The adaptive decision reduces one consequence of an error. It doesn't make the error disappear from other operators.

SELECT p.ProductName, SUM(s.Quantity) AS TotalQuantity
FROM dbo.AdaptiveSales AS s
JOIN dbo.AdaptiveProducts AS p ON p.ProductId = s.ProductId
WHERE s.Quantity >= 2
GROUP BY p.ProductName;
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

Distinguish Batch Mode From the Join Choice

Batch mode processes groups of rows through eligible operators. It is an execution mode, not another logical join. An Adaptive Join operator uses that infrastructure for its deferred choice.

Looking only for a columnstore index misses part of the picture. Inspect the execution mode on the relevant plan operators too.

The rowstore route has its own requirements and optimizer decisions. A rowstore table doesn't force row mode forever. Likewise, adding a columnstore index doesn't force this join into every plan.

Table sizes and estimated costs matter. Use the actual plan to establish what happened rather than inferring it from table definitions alone.

When comparing two executions, keep the compiled plan identity in view. A recompile recalculates costs and the threshold. A changed plan isn't evidence of two branches inside one original plan.

Parameter-sensitive workloads deserve careful notes here. Record the parameter, plan, and actual branch together so the explanation survives beyond the current session.

Turn Off Adaptive Joins for One Statement

The USE HINT option below disables this feature for one statement. It lets you compare an alternative without changing the whole database. Run the original and hinted forms against the same representative input.

In my test, the hinted query returned the same two rows. Its plan used a plain Nested Loops join with no adaptive operator.

Inspect both plans and resource messages. Don't assume that disabling flexibility improves performance because the diagram becomes simpler.

The hint is a diagnostic and tuning choice with a cost. It changes the optimizer's available strategies. Another join and memory grant can result.

Keep the comparison narrow, including small and large parameter ranges. A result that favors one input doesn't settle the decision for a query serving different requests.

If you retain the hint, write down the reason and review conditions. A statistics refresh, new index, or changed workload alters the trade. Permanent hints without ownership become furniture nobody remembers ordering. I prefer a clear evidence trail over a guess attached to a production query for years.

SELECT p.ProductName, SUM(s.Quantity) AS TotalQuantity
FROM dbo.AdaptiveSales AS s
JOIN dbo.AdaptiveProducts AS p ON p.ProductId = s.ProductId
WHERE s.Quantity >= 2
GROUP BY p.ProductName
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));

Watch the Rest of the Plan

The hash alternative influences memory planning even when loops runs. Read the memory grant information in the actual plan. Check spills and grant usage alongside the branch selection.

A runtime join decision doesn't remove the need to provide memory. Other sorts or hashes in the query also consume that grant.

Look at predicates on both inputs. A nonselective filter or an expensive lookup can dominate either branch. Good indexes and accurate statistics still help.

If the input has far more rows than expected, follow that error upstream. The root cause usually becomes clearer before you reach the adaptive operator itself.

Which input range represents your busiest requests? Use that range in the comparison. Include executions that return little data after reading a large amount.

Output row counts alone hide work done inside the join. The right diagnostic view includes the input, the branch, and the operators feeding that branch.

Choose Flexibility With Evidence

Adaptive joins help a plan respond to an uncertain input size. Keep their role specific. They don't choose between all possible joins, and they don't repair every estimate.

The operator has a documented mechanism you can inspect. That makes it useful to explain, rather than something to accept on faith.

Save an actual plan for each representative parameter range. Record the threshold and actual join type with your own CPU and reads. Compare the statement hint only when there is a reason.

Leave the feature available when the evidence supports it. An extra choice in the plan deserves the same scrutiny as a fixed one.

Revisit the decision after changing indexes or data distribution. The compiled threshold follows the plan's cost assumptions. A better estimate can produce a different operator entirely.

Treat that as a new tuning question, not a regression based on the missing icon. Successful tuning improves the workload, regardless of which diagram wins.

Related reading on this blog: Adaptive Threshold Rows and Disable Adaptive Joins.

An adaptive join is not a second optimizer, it is a runtime choice inside one plan.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.