meme (imported from SE)
Batch Mode Adaptive Joins are part of a [family of features][1] in the 2017 query processor that consists of:

 - Batch Mode Adaptive Joins 
 - [Interleaved Execution][2]
 - [Batch Mode Memory Grant Feedback][3]

So how do Adaptive Joins work?


  [1]: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-batch-mode-adaptive-joins/
  [2]: https://topanswers.xyz/databases?q=672
  [3]: https://topanswers.xyz/databases?q=669
Top Answer
meme (imported from SE)
**Batch Mode Adaptive Joins**

For Batch Mode Adaptive Joins, the goal is to not pin join choice to a specific type at compile time. 

When available, Adaptive Joins allow the optimizer to choose between Nested Loops Joins and Hash Joins based on row thresholds at run time. 

At this time, Merge Joins are not considered. Pure speculation is that needing data to be sorted, or needing to inject a sort into the plan would add too much overhead when changing the course of a query.

**When Do Batch Mode Adaptive Joins Occur?**

At this time, Batch Mode query processing requires the presence of a ColumnStore index. They also require, well, a join, and an index that allows for the choice of a Nested Loops or Hash Join.

**How do I know if my Join is Adaptive?**

Query plans for adaptive joins are quite distinctive. 

[![NUTS][1]][1]

The Adaptive Join operator is new to SQL Server 2017, and currently has the following properties in actual execution plans.

[![NUTS][2]][2]

 - Physical Operation: Adaptive Join

 - Actual Join Type: Will be Hash match or Nested Loops

 - Adaptive Threshold Rows: Signifies the tipping point when the join type will switch to Hash Match

 - Is Adaptive: True for Adaptive Joins

 - Estimated Join Type: Rather self-explanatory!

In an estimated or cached plan, there is rather less information:

[![NUTS][3]][3]

Most notably, the Actual Join Type is missing. 

**What breaks Batch Mode Adaptive Joins?**

To monitor this, there's an Extended Event session called `adaptive_join_skipped`, and it has the following reasons for skipping a Batch Mode Adaptive Join:

 - eajsrExchangeTypeNotSupported
 - eajsrHJorNLJNotFound
 - eajsrInvalidAdaptiveThreshold
 - eajsrMultiConsumerSpool 
 - eajsrOuterCardMaxOne
 - eajsrOuterSideParallelMarked
 - eajsrUnMatchedOuter

Aside from those, Batch Mode Adaptive Joins may be skipped for other reasons. Take these two queries for example:

    /*Selecting just integer data*/
    SELECT uc.Id, uc.Reputation, p.Score
    FROM   dbo.Users_cx AS uc
    JOIN   dbo.Posts AS p
    ON p.OwnerUserId = uc.Id
    WHERE  uc.LastAccessDate >= '20160101';
    
    /*Selecting one string column from Users*/
    SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
    FROM   dbo.Users_cx AS uc
    JOIN   dbo.Posts AS p
    ON p.OwnerUserId = uc.Id
    WHERE  uc.LastAccessDate >= '20160101';

They're identical except that the second query selects the `DisplayName` column, which has a type of `NVARCHAR(40)`.  

[![NUTS][4]][4]

The Batch Mode Adaptive Join is skipped for the second query, but no reason is logged to the XE session. It appears that string data remains the steadfast enemy of ColumnStore indexes.

There are other query patterns that fail to get Adaptive Joins, that also do not trigger events.

Some examples:

- `CROSS APPLY` with a `TOP`
 
- `OUTER APPLY`

**eajsrExchangeTypeNotSupported**

One thing that will trigger this event appears to be the presence of a Repartition Streams operator. In this query, the partitioning type is Hash Match. Special thanks to the Intergalactic Celestial Being masquerading as a humble blogging man known as [Paul White][5] for the bizarre query.

	SELECT uc.Id, uc.Reputation, CONVERT(XML, CONVERT(NVARCHAR(10), p.Score)).value('(xml/text())[1]', 'INT') AS [Surprise!]
	FROM   dbo.Users_cx AS uc
	JOIN   dbo.Posts AS p
	ON p.OwnerUserId = uc.Id
	WHERE  uc.LastAccessDate <= '2009-08-01'
	OPTION ( USE HINT ( 'ENABLE_PARALLEL_PLAN_PREFERENCE' ));
	GO 

**eajsrHJorNLJNotFound**

No queries have triggered this XE yet. 
What doesn't work:
 
- Merge Join hint

- Query patterns that rule out join type, for example Hash and Merge joins require at least one equality predicate. Writing a join on `>=` and `<=` does not trigger the event.

**eajsrInvalidAdaptiveThreshold**

This event can be triggered by various `TOP`, `FAST N`, and `OFFSET/FETCH` queries. Here are some examples:

	SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
	FROM   dbo.Users_cx AS uc
	INNER JOIN   dbo.Posts AS p
	ON p.OwnerUserId = uc.Id
	WHERE  uc.LastAccessDate <= '2008-01-01'
	OPTION ( FAST 1);
	GO 

	SELECT TOP 1 uc.Id, uc.DisplayName, uc.Reputation, p.Score
	FROM   dbo.Users_cx AS uc
	INNER JOIN   dbo.Posts AS p
	ON p.OwnerUserId = uc.Id
	WHERE  uc.LastAccessDate <= '2008-01-01';
	GO 

In some circumstances, they can also be triggered by paging-style queries:

	WITH pages
	AS ( SELECT TOP 100 uc.Id, ROW_NUMBER() OVER ( ORDER BY uc.Id ) AS n
	     FROM   dbo.Users_cx AS uc ),
	     rows
	AS ( SELECT TOP 50 p.Id
	     FROM   pages AS p
	     WHERE  p.n > 50 )
	SELECT u.Id, u.Reputation
	FROM   pages AS p
	JOIN   dbo.Users AS u
	ON p.Id = u.Id;

**eajsrMultiConsumerSpool** 

No known query patterns have triggered this event yet. 

What hasn't triggered it so far:
 
- Recursive CTEs
 
- Grouping sets/Cube/Rollup
 
- PIVOT and UNPIVOT
 
- Windowing functions

**eajsrOuterCardMaxOne**

A couple different types of queries have triggered this event. A derived join with a `TOP 1`, and a join combined with a WHERE clause with an equality predicate on a unique column:


	SELECT uc.Id, uc.Reputation, p.Score
	FROM   dbo.Users_cx AS uc
	JOIN   (SELECT TOP 1 p2.OwnerUserId, p2.Score FROM dbo.Posts AS p2 ORDER BY Id) AS p
	ON p.OwnerUserId = uc.Id
	WHERE  uc.LastAccessDate <= '2009-08-01';
	GO 

	SELECT p.Id, p.ParentId, p.OwnerUserId
	FROM dbo.Posts AS p
	JOIN dbo.Users_cx AS uc
	ON uc.Id = p.OwnerUserId
	WHERE p.Id = 17333;
	GO 

**eajsrOuterSideParallelMarked**

One type of query that can trigger this event is a Recursive CTE.

	WITH postparent AS 
		(
		SELECT p.Id, p.ParentId, p.OwnerUserId 
		FROM dbo.Posts_cx AS p
		WHERE p.Id = 17333

			UNION ALL

		SELECT p2.Id, p2.ParentId, p2.OwnerUserId
		FROM postparent pp
		JOIN dbo.Posts_cx AS p2
		ON pp.Id = p2.ParentId
		)
		SELECT pp.Id, pp.ParentId, pp.OwnerUserId, u.DisplayName
		FROM postparent pp
		JOIN dbo.Users AS u
		ON u.Id = pp.OwnerUserId
		ORDER BY pp.Id
		OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

The reason here appears to be that the recursive portion of the CTE, which causes a serial zone in the plan, disallows Batch Mode Adaptive Join choice.

**eajsrUnMatchedOuter**

This is the most common, and seems to occur when an index is used for the join that cannot support a seek. For instance, this query causes a Key Lookup:

	SELECT uc.Id, uc.Reputation, p.Score, p.LastActivityDate 
	FROM   dbo.Users_cx AS uc
	JOIN   dbo.Posts AS p
	ON p.OwnerUserId = uc.Id
	WHERE  uc.LastAccessDate >= '20080101'
	       AND uc.DisplayName = 'harrymc'
	       AND p.Score > 1;

[![NUTS][6]][6]


The resulting query chooses a Row Mode Nested Loops join to execute both the Key Lookup and the table joins, which triggers the event.

Another example is a query that skips the narrow nonclustered index in favor of the PK/CX. In this case, the PK/CX does not lead with `OwnerUserId`, so the only join choice is a Hash Join.

In both cases, the "unmatched outer" seems to indicate that the index chosen does not sufficiently cover our query.

	SELECT uc.Id, uc.Reputation, p.*
	FROM   dbo.Users_cx AS uc
	JOIN   dbo.Posts AS p
	ON p.OwnerUserId = uc.Id
	WHERE  uc.LastAccessDate >= '20160101';

**Do Batch Mode Adaptive Joins work with multiple joins?**

Yes, but as of this writing, there appears to be a limitation:

Joining from one ColumnStore index to multiple Row Store indexes will yield multiple Adaptive Joins, whereas a join between multiple ColumnStore indexes will not be Adaptive.

For example, these two queries

	SELECT uc.Id, uc.Reputation, p.Score
	FROM   dbo.Users_cx AS uc
	JOIN   dbo.Posts AS p
	ON p.OwnerUserId = uc.Id
	JOIN dbo.Comments AS c
	ON c.PostId = p.Id
	AND  c.UserId = uc.Id
	WHERE  uc.LastAccessDate >= '20160101';
	
	SELECT uc.Id, uc.Reputation, p.Score
	FROM   dbo.Users_cx AS uc
	JOIN   dbo.Posts AS p
	ON p.OwnerUserId = uc.Id
	JOIN dbo.Comments_cx AS c
	ON c.PostId = p.Id
	AND  c.UserId = uc.Id
	WHERE  uc.LastAccessDate >= '20160101';

The first query joins one ColumnStore index (on Users) to two Row Store indexes on Posts and Comments. This yields two Adaptive Join operators.

The second query joins two ColumnStore tables (Users and Comments) to one Row Store table (Posts), and yields one Adaptive Join.

[![NUTS][7]][7]

**Is there any overhead to Batch Mode Adaptive Joins?**

Yes, all Batch Mode Adaptive Join plans receive a memory grant. This isn't always the case with Nested Loops join, unless they receive the Nested Loops prefetch optimization. The memory grant is to support a Hash Join should the plan require one based on row thresholds. 


  [1]: https://i.stack.imgur.com/nxaLE.jpg
  [2]: https://i.stack.imgur.com/8f5iv.jpg
  [3]: https://i.stack.imgur.com/c6sjP.jpg
  [4]: https://i.stack.imgur.com/FbZ7e.jpg
  [5]: https://dba.stackexchange.com/users/1192/paul-white
  [6]: https://i.stack.imgur.com/bSMNB.jpg
  [7]: https://i.stack.imgur.com/3XCqv.jpg

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.