A Little About Cardinality Estimation Feedback In SQL Server 2022
Thanks for watching!
Video Summary
In this video, I dive into cardinality estimation feedback—a feature introduced in SQL Server 2022 that not many people are talking about yet. With prerequisites like Query Store turned on and being in Compat Level 160, it’s a bit of an underdog right now. However, I demonstrate how this feature can help correct cardinality estimation issues by showing a query that initially had wildly incorrect estimates but eventually got fixed after running multiple times. By the end of the video, you’ll learn about different hints and considerations for enabling this feature, as well as when it might become more widely adopted in future SQL Server releases.
Full Transcript
Erik Darling here with Darling Data, home of the most reasonable rates in the SQL Server universe. Actually, finally acknowledged by BeerGut Magazine to have the most reasonable rates of any SQL Server consultancy that has any competence whatsoever. I’m sure you can find someone much cheaper who sucks at everything. So enjoy that experience with your life. In today’s video, we’re going to talk about cardinality estimation feedback. Why does it say CE feedback? Well, you’ve got cardinality which starts with C, and you’ve got estimation which starts with E, and then you’ve got feedback. So it all makes sense, I promise. This was a feature that got introduced to SQL Server 2022 that not a lot of people have talked about. Mostly because there’s probably not a lot of people on SQL Server 2022. Some other prerequisites is you have to have Query Store turned on and you have to be in Compat Level 160. So if you’ve got all three of those moons aligned around you, maybe you’re getting this and you just don’t know it. I don’t know. But I don’t really hear anyone shouting from the rooftops how great their cardinality estimation feedback is. So I don’t know. Maybe this will help open your eyes to just how okay cardinality estimation feedback can be. So I don’t know. Maybe this will help open your eyes to just how okay cardinality estimation feedback can be.
So before we do that, let’s talk about you and me feedback. And let’s say if you enjoy this channel content, if you like SQL Server stuff, maybe you just like looking at me. Maybe you like the sound of my voice. I don’t know. Whatever it is that keeps you coming back here doing things. If you would like to support my endeavors with this channel, you can sign up for a membership. It’s about four bucks a month at the low end. You can, of course, choose to be more generous if you’d like. But, you know, four bucks a month, not bad. All right, depending on how much, how many bucks a month you have. If your exchange rates are currently just too much for you, even if you’re an American and inflation exchange rates are just too much for you, you can like, you can comment, you can subscribe.
And you can fill my heart with joy as I answer YouTube comments to fill in all the lonely, lonely gaps in my life. If you need help with your SQL Server, and I guarantee you all do, and you would like someone with very reasonable rates to come work some magic on your SQL Server. Be like a little SQL Server masseuse and give your SQL Server a nice time. I’m available to do all of these things and my rates are reasonable. If you could use some high quality, low cost SQL Server training, you can get beginner, intermediate and expert level stuff from me, all for about 150 US dollars for life.
So long live you. You can use that link and that coupon code or you can just click the link in the video description and guess what? It’ll bring you right there. It’s amazing. Advanced hyperlink technology, ladies and gentlemen. Wave of the future. Live and in person, of course, coming up in, oh geez, just about a month. November 4th and 5th, I will be in Seattle, Washington at Pass Data Summit with Kendra Little.
I thought I was going to say Cat Lady Kendra Little. I think she just has dogs though. But I’ll be there co-hosting two days of performance tooting mastery and magic and that’ll be fun. And if there’s an event nearby to you, your heart, your soul, some family friendly part of you, and you would like me to show up there to talk about SQL Server, tell me what that event is so that I can try to go there.
Let’s make sure that they’re family friendly parts because as evidenced just moments ago, this is a family friendly channel. And now with that out of the way, let’s start our SQL Server party. This is not a slumber party. We’re going to be wide awake for this one.
We are going to do this one good. So I’ve got this query here and I have run this query 17 times. And if we look at this query, well, hopefully if everything works the way it has worked the last three times I’ve run through this, we will have the same execution plan all 17 times.
And we can see looking through all 17 of these executions, the SQL Server made an oopsie. It made a boo-boo, made a boom-boom. Look what happened.
It was off by a lot here, almost 8,000%. It was off by a lot here. Well, actually, that’s almost the same number of percent that that’s wrong, right?
That makes sense, though, since it’s a key lookup. And then over here, well, it’s still off by the same amount because that’s just the nested loops joined from this key lookup. And then that index seek to post, well, we guessed 10,001.
We got zero. So clearly, cardinality estimation has gone amok here. How amok has it gone?
Well, enough for SQL Server to care about it. Enough for SQL Server to want to give us some feedback on our cardinality. Now, of course, you can do that. You can see which queries are affected by cardinality estimation feedback and all sorts of other feedback mechanisms in SQL Server with my free amazing store procedure, SP Quickie Store.
And if we run this query just like this, notice that we get a whole bunch of stuff back. But the important stuff that we get back is up here. We have this query.
We can see it. It ran. There’s a query plan for it. But look in here. Feature description, CE feedback. We have feedback data. We have some feedback hints.
Well, I don’t think there’s anything else in there at the moment, though. It’s just some lonely empty JSON. And we have this pending validation. So we ran this 17 times, and it’s pending validation.
I don’t think there’s anything else in here. Nope, that’s empty. So what we’re going to do is we’re going to come back, and we’re going to run this one more time. All right?
And what happened on that final time? We got a different query plan. Good Lord. Look at that. Cardinality estimation has been fixed.
We started scanning the votes table. And, well, I don’t know. We still got some bad cardinality feedback there. We still have some bad cardinality estimates on this. We guessed 104, 535, and we got zero.
But you know what? It’s okay, because we got that one right up there. Maybe we just need another round of cardinality estimation feedback. Maybe we need one on the join now.
I don’t know. Maybe SQL Server is going to keep working on it. I’m not going to run this thing another 17 times to find out, though. So now if we go look at Query Store via SP Quickie Store, and we look at what happened, now we have two query plans for this.
We can see that Query ID 3 has now generated a Plan 3 and a Plan 4. So SQL Server has formally acknowledged the new plan. And if we look in here, we’ll see that we have for our CE feedback that SQL Server has disabled a row goal and that verification has passed.
And that’s what gave us the new query plan. And we can see that SQL Server applied the hint. Option use hint.
Disable optimize our row goal. Isn’t that all fantastic? We didn’t have to do anything. SQL Server just finally looked at a query plan and went, you know, it’s just a lot going on.
We’re really messing up. Maybe we should try a different strategy. And it finally applied one. Now, this is cool because it did work eventually. It just took, you know, what, 17 executions.
And then on the 18th execution, SQL Server was like, we got this. We’re doing this. We’re going the whole way.
So it’s cool that it does eventually work. So I’m psyched on that. So, you know, in five, six years when everyone gets to SQL Server 2022, and just like when it’s on the cusp of being end of life or end of support or whatever it’s called, and maybe you’ll be in compat level 160, but probably not, which I understand.
But maybe you’ll see this too. I don’t know. I mean, SQL Server 2022 is already like going on three years old. Wow.
Makes you feel old, doesn’t it? Crusty. But there’s pretty good documentation about how this all works and things that are in play. And I kind of want to just scroll down to the different hints that it can apply.
So, of course, there is the feedback correlation, which it uses the correlation model assumptions. These are the different correlation models.
We have fully independent, partially correlated, and fully correlated. I don’t know why it’s not fully independent, partially independent, and fully correlated. I guess it doesn’t really matter.
Maybe there should be a fourth step where it’s partially independent and then partially correlated, because you don’t know what percentage is partial. Right?
Is it 50-50? The 75-25? 60-40? We don’t know. There might need another step in there. You know? Moon phases. There’s feedback join containment, which goes off either simple containment, which is the old cardinality estimator model of doing it, or base containment, which is the new cardinality estimation model of doing things.
And here’s the one that we got, the query optimizer row goal. Yeah, buddy. This is the one that kicked in for us and did all sorts of wonderful things for our query, some sort of.
And then down here, we have some considerations for cardinality estimation feedback. And this is all the stuff that you need to have compat level 160. You have to have this not turned off.
This will turn it off. You need to turn it on or have it turned on. You need to not use the disable feedback hint. And you need to have query store turned on so that SQL Server can track these things. There’s some more stuff in here.
I don’t know if you’re… Oh, there’s some queries. Wow. Microsoft wrote queries for us. I bet they’re great. Thanks, Microsoft. Ah, look at that.
Okay. Well, that’s enough web browsing for one day. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
I’m going to scroll away from those queries. They’re hurting my eyes. I do hope that eventually… Well, I don’t know. I have a feeling that sometime… Sometime…
Is it September 20-something here? I have a feeling that sometime in the next… Let’s see.
Three to five or so months, we should start seeing SQL Server the next CTPs rolling around. That’s probably a reasonable thing. Probably a reasonable timeline on that.
So I have a good feeling about that. So hopefully we start seeing that soon. And I don’t know. Maybe SQL Server 22 will just be kind of one of those in-betweener versions that no one touched, like SQL Server 2014 or SQL Server 2017. Because 2016 and 2019, people seem to touch.
And SQL Server 20 v.next? Maybe that’ll be the one. Maybe 2022 is just… It’s kind of a cursed release.
I don’t know. But anyway… That’s probably when most of you will probably start seeing this feature kick in a little bit more for you. We hope.
By we, I mean me. Because I like fixes that do things automatically. And I like when they work correctly. So this one appears to do okay sometimes.
So good job, Microsoft. Investing in the future. Anyway, I can’t remember what I already said. So I’ll say it again, just in case.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you over in the next video, which will have equally reasonable rates as this video. All right.
Goodbye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.