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.
|
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.
|
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.
|
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
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.
We specialize in Errataca over here.
You’re usually great with posting links. Can you add a link to the words previous post?
Golly and Gosh. How did I miss that? Here’s the link — also adding it to the post.
Thanks!