Adaptive Blog Posts

Last Updated 7 years ago

It turns out I can be dumb

In a previous blog post about Adaptive Joins, I thought that EXISTS wasn’t supported by the new feature. It turns out that EXISTS is totally 100% supported, as long as your indexes support EXISTS.

To show this, I need to show you a query that gets an Adaptive Join plan. After I show you a query that can’t get one.

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.CreationDate >= '20160101';

1

2

3

4

5

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.CreationDate >= '20160101';

Forget about the Users table. With no index on the Posts table that has OwnerUserId as the leading column, there’s no join choice.

There is no Dana, there is only hash join.

If I add an index that makes join choices possible, I get an Adaptive Join plan.

CREATE INDEX ix_ADAPT_THIS_JOIN ON dbo.Posts (OwnerUserId) INCLUDE (Score)

1	CREATE INDEX ix_ADAPT_THIS_JOIN ON dbo.Posts (OwnerUserId) INCLUDE (Score)

Re-running the same query, I get El Adaptivo Plana.

Now, I’m going to run two slightly different queries. The main difference is that I’m now selecting PostTypeId. The where clause date changes a bit to show different types of plans with the new column.

SELECT uc.Id, uc.Reputation, p.Score, p.PostTypeId
FROM dbo.Users_cx AS uc
JOIN dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE uc.CreationDate >= '20160101'


SELECT uc.Id, uc.Reputation, p.Score, p.PostTypeId
FROM dbo.Users_cx AS uc
JOIN dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE uc.CreationDate >= '20161201'

1

2

3

4

5

6

7

8

9

10

11

12

SELECT uc.Id, uc.Reputation, p.Score, p.PostTypeId

FROM dbo.Users_cx AS uc

JOIN dbo.Posts AS p

ON p.OwnerUserId = uc.Id

WHERE uc.CreationDate >= '20160101'

SELECT uc.Id, uc.Reputation, p.Score, p.PostTypeId

FROM dbo.Users_cx AS uc

JOIN dbo.Posts AS p

ON p.OwnerUserId = uc.Id

WHERE uc.CreationDate >= '20161201'

The two plans that result from these two queries aren’t Adaptive at all.

What Happened?

Well, that first plan looks just like the original plan. It uses an index where OwnerUserId isn’t the leading column, so no other plan choice is available. It has to hash.

That second plan, though. It uses the right index to give us an Adaptive Join, but it seems like the Key Lookup and downstream Nested Loops join does the whole caper in.

Since I decided to stop being dumb, I started up my Extended Events session to capture reasons why Adaptive Joins aren’t used.

The reason it shows for that is eajsrUnMatchedOuter.

There are a number of reasons listed in that XE session for why Adaptive Joins might be skipped:

eajsrExchangeTypeNotSupported
eajsrHJorNLJNotFound
eajsrInvalidAdaptiveThreshold
eajsrMultiConsumerSpool
eajsrOuterCardMaxOne
eajsrOuterSideParallelMarked
eajsrUnMatchedOuter

I’ve tried some other methods to trigger other reasons, like hinting Merge Joins, generating Eager Spools, and joining to one row tables. So far nothing else has made something pop up.

Oh well. At least I’m less dumb now.

Thanks for reading!

4 Comments. Leave new

Aaron
September 29, 2017 11:29 am

If I blogged every day, I’d need dedicated posts to correct all of the errors. “Errata” would be the biggest in my word cloud. Thanks for producing all of this content.

Reply
- Erik Darling
  September 29, 2017 11:33 am
  
  We specialize in Errataca over here.
  
  Reply
Esther
October 2, 2017 1:05 pm

You’re usually great with posting links. Can you add a link to the words previous post?

Reply
- Erik Darling
  October 2, 2017 1:32 pm
  
  Golly and Gosh. How did I miss that? Here’s the link — also adding it to the post.
  
  Thanks!
  
  Reply