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.
And here’s the tool tip info!
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.
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!
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…
You could have always had people switch to Oracle 😉
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?
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.
Oooh shiny