Look Ma, Adaptive Joins

This probably won’t seem like a big deal soon

But I just got the optimizer to pick an Adaptive Join! It took a few tries to figure out what would cause some guesswork to happen, but here it is.

I hope Joe Sack has strong ribs.

And here’s the tool tip info!

Dynamic Duo

This is so cool! Now I can start picking apart the XML to add stuff to sp_BlitzCache.

In the XML, we have this information.

Papers, please

There also appears to be a whole XML path dedicated to the AdaptiveJoin, but I’m still working out what all goes on in there.

Remember that you need at least CTP2 of SQL Server 2017, and SSMS 2017 to see the new operators.

Thanks for reading!

Previous Post
SSMS 2017 Is Now Available For Download
Next Post
Status Update On Query Plan Hash Bug

5 Comments. Leave new

  • This feature almost makes me angry at its potential greatness. Half of the performance problems I’ve been fixing in the last week or so would’ve been avoided by this…

  • Hi Erik,

    nice demo. What do you think about the following? Is it important for performance tuning to know the actual join types of query executions using the new adaptive join? Upon now I could take the operators in the estimated plan literally: a hash match is going to be executed as a hash match and a nested loop is going to be executed as a nested loop. However with the new adaptive join I’m afraid that I might not know what was actually going on by looking on the information at sys.dm_exec_query_stats combined with the estimated plans. Does that make sense or am I missing something here?

    • Erik Darling
      April 28, 2017 5:35 am

      So far I’ve been able to puzzle out which join type is chosen. I have a bunch of other posts coming up on the topic in the pipeline, as they say.

  • Alex Friedman
    April 30, 2017 4:25 am

    Oooh shiny


Leave a Reply

Your email address will not be published.

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