Adaptive joins let the optimizer choose between a Hash Join and a Nested Loop join at runtime, which can be fantastic for performance when row count estimates are variable. Recently, when Erik Darling taught two days on TSQL at PASS Community Data Summit, a student asked why a query plan where an adaptive join used a Nested Loop at runtime ended up with a large memory grant anyway.

I didn’t remember the answer to this, but the great thing about co-teaching is that Erik did: adaptive joins always start executing as Hash Joins, which means they have to get memory grants upfront. Even if the query ultimately switches to a Nested Loop at runtime, that memory grant was already allocated. This has real implications for memory usage, especially in high-concurrency environments.

What Are Adaptive Joins?

Adaptive joins were introduced in SQL Server 2017. This join allows the query optimizer to choose between a batch mode Hash Join and a row mode Nested Loops Join at runtime, based on the actual number of rows processed in the Hash Join build phase.

This is a departure from traditional query optimization, where the optimizer makes a one-time decision during plan compilation. With adaptive joins, the optimizer can “change its mind” during execution if the row counts it encounters indicate that a hash join isn’t needed and it can do something more lightweight for that execution of the query.

I’m going to stick to the basics in this post, but there are some excellent deep dives on how adaptive joins work listed at the end of this post.

Understanding Hash Join Build and Probe Phases

Let’s talk about how Hash Joins work. Hash Joins have two phases: build and probe.

Imagine Erik and I are giving a training event. We hire a person to stand at the door and check people in. They know exactly who should be there, and they’re a lot tougher than they look.

Build phase: Before the event starts, we give the door person a list of all registered attendees. The information is in an Excel table (the world’s most popular database). In SQL Server terms, we have built a hash table in memory of all the people who are allowed in.

Probe phase: As people arrive, the door person checks each person’s name against the list– they probe the list to see if each person’s name has a matching value in the hash table. If your name is on the list, they let you through. If not, you’re turned away.

In a hash join, SQL Server does the same thing:

• Build phase: Takes one incoming dataset (usually the smaller one) and builds a hash table in memory, organizing rows by their join key
• Probe phase: Takes the other dataset it is joining and, for each row, looks up the join key in the hash table to find matching rows

The build phase happens first and requires memory to store the hash table. The probe phase happens second and uses that hash table to quickly find matches / check who is “on the list.”

All Adaptive Joins Are Born as Hash Joins

Key points to remember are:

1. Hash joins require memory grants upfront - The optimizer needs to allocate memory for the hash table before execution begins
2. Row mode apply operations don’t need memory grants - They can work with minimal memory, processing rows one at a time
3. The decision point happens after the build - Once the build side of the hash join completes, SQL Server evaluates whether to continue with the batch mode hash join or switch to a row mode apply

As Paul White emphasizes in his article on the adaptive join threshold:

One thing I want you to bear in mind throughout this piece is that an adaptive join always starts executing as a batch mode hash join. This is true even if the execution plan indicates the adaptive join will transition to apply.

Even if the adaptive join ultimately switches to a row mode apply strategy (which is more efficient for small row counts), it still had to start as a hash join and request the memory grant upfront. The threshold calculation is based on cost estimates and the actual row count from the build phase.

Memory Grant Implications

This is where adaptive joins get interesting from a resource management perspective.

Every execution of a query with an adaptive join must request a memory grant for the hash join, even if the query ultimately switches to a row mode apply at runtime. This follows directly from Paul White’s point that adaptive joins always start executing as batch mode hash joins - the memory grant is requested before SQL Server knows whether it will need to switch strategies.

This means:

1. Memory grants are requested on every execution - Even if the adaptive join switches to row mode apply, the memory grant was already allocated for the hash join
2. Overall memory usage can increase - If you have many queries using adaptive joins, your total memory grant usage will be higher than if those queries used row mode apply directly
3. Memory pressure considerations - In environments with limited memory or high concurrency, adaptive joins can contribute to memory pressure

When to Optimize with Temp Tables and Dynamic SQL

Because adaptive joins always request memory grants, sometimes it’s still worth optimizing queries using temp tables and dynamic SQL to:

• Simplify optimization - Break complex queries into simpler parts that the optimizer can handle better
• Reduce overall memory grants - Smaller, simpler queries may not need adaptive joins and can use more efficient join strategies that don’t require memory grants
• Improve plan stability - Temp tables with statistics can lead to more predictable plans

This doesn’t mean you should avoid adaptive joins entirely - they’re a powerful feature that often improves performance. But understanding the memory grant implications helps you make informed decisions about query optimization strategies.

Example Query with Adaptive Join

Here’s an example query using the StackOverflow2013 database that gets an Adaptive Join on my laptop instance running SQL Server 2025.

USE StackOverflow2013;
GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO

EXECUTE dbo.DropIndexes;
GO

CREATE NONCLUSTERED INDEX
    ix_Posts_CreationDate_Score
ON dbo.Posts
(
    CreationDate,
    Score
)
INCLUDE
(
    OwnerUserId,
    PostTypeId,
    CommentCount
)
WHERE PostTypeId IN (1, 2)
WITH
(
    DATA_COMPRESSION = PAGE,
    SORT_IN_TEMPDB = ON
);
GO

CREATE OR ALTER PROCEDURE
    dbo.Adaptive
(
    @Start datetime,
    @End datetime
)
AS
BEGIN
    SELECT
        u.DisplayName,
        u.Reputation,
        p.Score,
        p.CreationDate
    FROM dbo.Posts AS p
    JOIN dbo.Users AS u
        ON u.Id = p.OwnerUserId
    WHERE p.PostTypeId IN (1, 2)
    AND   p.CreationDate >= @Start
    AND   p.CreationDate < @End
END;
GO

/* This generates a plan with an adaptive join */
EXECUTE dbo.Adaptive '20130101', '20130514';
GO

/* This reuses the plan and chooses to use the nested loop strategy
on the adaptive join */
EXECUTE dbo.Adaptive '20130101', '20130102';
GO

The actual query plan for the second execution looks like this:

Memory grant information is not available in detail per operator, but you can see how much was granted for the query. In this case it is on the SELECT operator.

Detail on how much of the memory grant was used and what was involved in “negotiating” the request is visible in the properties pane for the operator or in the XML of the plan.

Useful Adaptive Join Query Plan Operator Properties

When you look at properties of an adaptive join operator, you’ll see:

Limitations of Adaptive Joins

Adaptive joins have some limitations:

• Edition requirements: Batch mode adaptive joins require Enterprise Edition (or Developer/Evaluation editions which include Enterprise features). They’re not available in Standard Edition, Web Edition, or Express Edition.
• Compatibility level requirements: Database compatibility level 140 (SQL Server 2017) or higher is required.
• Batch mode only: Adaptive joins only work with batch mode execution, which requires either:
  • A columnstore index, or…
  • Batch Mode on Row Store (SQL Server 2019+, Enterprise Edition only, compatibility level 150 or higher).
• Limited join transitions: Adaptive joins only support transitions from batch mode hash joins to row mode apply (correlated nested loops) joins. They don’t support transitions to other join types like merge joins or row mode hash joins.
• Threshold calculation sensitivity: The adaptive join threshold depends on the original cardinality estimate of the build input. As Paul White explains, this can lead to parameter sensitivity issues where the same cached plan behaves differently based on the initial cardinality estimate used during optimization.
• Query structure requirements: The query must qualify for batch mode execution, and the join must be eligible for the adaptive join optimization. Joins that would require a Key Lookup on the inner side are not eligible. Queries with certain constructs like CROSS APPLY with TOP or OUTER APPLY may not qualify. As Erik Darling notes in his detailed answer, there’s an Extended Event called adaptive_join_skipped that can help you track when and why adaptive joins are skipped, if you like rocket science.
• Not supported in natively compiled modules: Natively compiled T-SQL modules (In-Memory OLTP) don’t support adaptive joins.
• Memory overhead: As we’ve discussed, adaptive joins always request memory grants for the hash join, even if they switch to row mode apply at runtime.

Enabling and Disabling Adaptive Joins

Adaptive joins are enabled by default when the requirements are met, but you can control them at the database or query level.

Database scoped configuration (affects all queries in the database):

• SQL Server 2017:
  • To disable: ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON; (yes, ON means disable - it’s a double negative)
  • To re-enable: ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;
• SQL Server 2019+:
  • To disable: ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = OFF;
  • To re-enable: ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ADAPTIVE_JOINS = ON;

Query hint (affects only the specific query):

• OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));

The query hint takes precedence over the database scoped configuration setting.

Learn More

Adaptive joins are a powerful feature that can improve query performance by choosing the right join strategy at runtime.

However, they ain’t free: they come with memory grant implications that are important to understand, especially in high-concurrency environments or systems with limited memory.

The fact that adaptive joins always start as batch mode hash joins means they always request memory grants, even if they ultimately switch to row mode apply. These joins can be fantastic, but you probably don’t want them running all the time in a highly concurrent system.

• Read Paul White’s deep dive on the adaptive join threshold
• Watch Erik Darling’s video on adaptive joins in SQL Server
• Check out Hugo Kornelis’ page explaining the adaptive join operator