Adaptive Joins Archives - Brent Ozar Unlimited®

Performance Tuning

Parameter Sniffing in SQL Server 2019: Adaptive Joins

April 10, 2019

So far, I've talked about how adaptive memory grants both help and worsen parameter sniffing, and how the new air_quote_actual plans don't accurately show what happened. But so far, I've been using a simple one-table query - let's see what happens when I add a join and a supporting index:
[crayon-6a453eed1d720873870058/]
(Careful readers will note that I'm using a different reputation value than I used in the last posts - hold that thought. We'll come back to that.)

Performance Tuning

How Many Kinds Of Joins Are There?

May 30, 2018

Quite So
There are three in SQL Server, not counting the Adaptive Join which is just a placeholder for either Nested Loops or Hash Joins.

If you look closely at each of those joins, you'll see all sorts of interesting things pop up.

Performance Tuning

Adaptive Joins, Memory Grant Feedback, and Stored Procedures

February 28, 2018

Not Exactly The Catchiest Name
There's a TL;DR here, in case you don't feel like reading the whole darn thing.

Batch mode memory grant feedback works with stored procedures
It takes several runs to adjust upwards to a final number
It seems to only adjust downwards once (in this case by about 40%)

Adaptive Blog Posts

September 29, 2017

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…

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

May 19, 2017

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.

Performance Tuning

How Scalar Functions Can Stop You From Getting Adaptive Joins

May 17, 2017

If you're not aware of the performance problems scalar valued functions can (and often do) cause, well, uh... click here. We'll talk in a few days.

If you are, and you're worried about them crapping on Adaptive Joins, follow along.

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

May 16, 2017

But is it faster?
Now, I know. There are very few "always" things out there in SQL Server. This is also true for functions.

A lot of the time -- I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.

Adaptive Joins And SARGability

May 5, 2017

There's a famous saying
Non-SARGable predicates don't get missing index requests.

And that's true! But can they also stifle my favorite thing to happen to SQL Server since, well, last week?

Adaptive Joins And Local Variables

May 4, 2017

With new features I really love kicking the tires to see how they work with existing features, and if they fix existing performance troubleshooting scenarios. One issue that I see frequently is with local variables. I'm not going to get into Cardinality Estimator math here, we're just going to look at Adaptive Join plan choice…

Anatomy Of An Adaptive Join

May 3, 2017

I don't like it unless it's brand new
When new features drop, not everyone has time to jump on top of them and start looking at stuff. That's what consultants with nothing better to do are for.

I've been excited about this feature since talking to The Honorable Joseph Q. Sack, Esq. about it at PASS last October. My pupils dilated like I just found the bottom of a bottle of Laphroaig 18.

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

May 2, 2017

Cheeky
If you thought that title sounded familiar, you sure were right.

That's called a classical reference.