Adaptive Blog Posts

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.

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.

Probably more Canadian regulations

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

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

Ain’t It Fun?

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.

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

Human Being

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.

Cryptic Walk

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!

Previous Post
SQL Server 2017 Showplan Schema Is Available
Next Post
First Responder Kit Release: Still Totally Lacking A Servicing Model

4 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.