Cheeky
If you thought that title sounded familiar, you sure were right.
That’s called a classical reference.
So here we are, three whole years later, and yet another improvement to the engine promises to fix performance issues forever and ever.
While this isn’t exactly an entirely new cardinality estimator, it’s an entirely new branch in the cardinality estimation process.
Adaptive Joins
Or as I’m going to call them, the Mr. Destiny Join.
You see, in every plan, you see both possible paths the optimizer could have chosen. Right now it’s only limited to two choices, Nested Loops and Hash Joins.
Just guessing that Merge Joins weren’t added because there would have been additional considerations around the potential cost of a Sort operation to get the data in order.
Fun, right? Ever see a Sort in a query plan where you didn’t write an ORDER BY? Sometimes SQL will act like your mom and say “I’ll just straighten up your room for you” but then she finds the box under your bed and, well, anyway.
I’m 36 and I’m finally not grounded.
Why this is awesome
After the second Council Of Trent Brent decided that SARGable is a word, a formal edict was issued that all all queries must be SARGable in order to perform well.
With Adaptive Joins, the optimizer is way less “that’s not an iceberg” about the way it chooses query plans. It’ll go ahead and run some tests to make sure that it is, in fact, not an iceberg, before steering plan choices in a particular direction. This can be particularly helpful for non-SARGable queries, where the optimizer would usually way overestimate rows, and ask for all sorts of memory and CPU and other knick-knacks and whirlygigs to compensate.
I’m going to be kicking the wheels on Adaptive Joins and blogging about it as I go. Stay tuned!
Thanks for reading!
Brent says: when I first heard about Adaptive Joins, I thought, “This is amazing!” but I still had no idea about how complex it would get. Reading Erik’s upcoming posts, I’m even more excited about the potential this holds for down the road. Granted, it’s only for columnstore indexes right now, but still, this is gonna make 2017 a heck of a release for performance tuners.
12 Comments. Leave new
Am I the only one that immediately thought of Shooter McGavin when reading the title? Does that make me a bad database guy?
I was really excited about this, but when I realized it was only on columnstore indexes, that just gives it such a small surface area that it goes into the category of something really cool that I will never get to use.
It was probably too difficult to implement this feature in the legacy row store execution code, but that is my workload.
Jeez, this is just v1. Don’t be so down on it.
I’m kind of in the columnstore sad panda camp, too–although, seeing this post, i thought of something slightly devious: what happens when you join *two* row-store tables (that you really care about) to a “dummy” CCI table? would there be a way of tricking the optimizer into trickling down the effects? i fully admit to being at the very limit of my engine internals “knowledge”, and perhaps this is patently the dumbest question ever posted here, but i’m willing to bet if there’s a way of jux-ing it, mr. darling is up to the task. I have in mind something along the lines of other old tricks like SELECT TOP (KaJillion) to force parallelism.
“Council of Brent” – Nice. Cya, and raise with another bit of conciliar trivia: it was the Second (not First, as is commonly supposed) Platteran Council which held that all magnetic hard drives should thenceforth be known as “spinning frisbees of death”. True story.
Yeah, you can do that — see Paul’s answer here (along with the link he provides).
I’ll take your compliment from the 23rd Council of Niceidea.
whoa, cool! i no longer feel like an idiot. thanks for the info.
the workload at 23rd Niceidea? Creed-write, of course (not creed-only, as is commonly supposed).
Your eminence, sp_Blitzeth hath warned us that we are in full recovery model with no scroll backups, and a scribe from the offshore team truncated several scriptures.
I fear the invoking of the Cardinality Estimator Breakfast post may prove an ill omen…put it this way, I don’t have to look up TF 9481 any more, it is seared into my mind for eternity. (But I’ll be damned if I’m switching back to 110 compat!!!)
This plus the automatic tuning, “force last good plan” thing are my favorite upcoming features, to be sure…
Heh heh heh
Does anyone know a way to force adaptive joins when the optimizer desides not to use one? We have the capability to force hash joins / nested loops using query plan hints – but there doesn’t seem to be a way to do adaptive joins?
I wonder how the query store can “enforce” an adaptive join in the plan. There must be a way to do it.
Christo – for questions, head to https://dba.stackexchange.com.
Thanks Brent – will do.