Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

I think I’ve mentioned that the most fun part of new features is testing them with old ideas to see how they react.

It occurred to me that if Adaptive Joins didn’t work with APPLY, I might cry.

So, here goes nothin’!

Cross

Simple Cross Apply…ies can use Adaptive Joins, though at first glance there’s nothing special or different about this plan from the regular Inner Join version.

Backwards pants

I do have to point out that Cross Apply used to only be implemented as a Nested Loops Join. I learned that many years ago from one of the best articles written about Cross Apply by Paul White. That changed recently — it’s possible to see it implemented with a Hash Join in at least 2016. I’ve seen it crop up in Cross Apply queries without a TOP operator.

It may have been in previous versions, but… Yeah. ColumnStore before 2016.

Anyway, the Cross Apply with a TOP operator does appear to skip the Adaptive Join and favor Parallel Nested Loops, as you can see in this particularly Machanic-y query.

PNL4LYFE

Outer

Outer Apply suffers a rather gruesome fate, where neither implementation gets an Adaptive Join.

No one likes you anyway

Sad face. I still haven’t gotten anything to show up in my Extended Events session for why Adaptive Joins were skipped for certain queries.

 

Exists

Exists not only doesn’t get an Adaptive Join, but… MY EXTENDED EVENTS SESSION FINALLY SORT OF TELLS ME WHY!

I mean, it doesn’t make any sense, but it’s there.

Like your alcoholic aunt.

I’m going to skip showing you a non-Adaptive plan, because you’ve seen enough of those.

Here’s what the Extended Events session shows. The query in the XE session and the query I ran are slightly different because I was testing different permutations (I paid like $7 for that word) to see if it made any difference.

Ho hum.

Got that? eajsrUnMatchedOuter.

I’m going to say that the next time a bartender tries to cut me off.

Not Exists

Would you believe that Not Exists gets an Adaptive Join, but Exists doesn’t?

And… would you believe that there’s a bug in the query plan that makes the lines all woogy?

Fire Hydrant

Sir, I’m going to need you to step out of the vehicle.

Territory

I just love looking at and experimenting with this stuff! I hope you’ve been enjoying reading about it.

Thanks for still doing that!

, , ,
Previous Post
Building a Faux PaaS, Part 2: Choosing and Testing a Cloud Vendor
Next Post
What Do You Want to See in a Free Query Store Tool?

6 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}