A Little About Intelligent Query Processing Limitations In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the complexities and limitations of Microsoft’s intelligent query processing features in SQL Server. Erik Darling shares his experiences working with clients where these advanced features often fail to kick in when they should, leading to suboptimal query plans. I illustrate this through examples involving batch mode on rowstore and adaptive joins, highlighting how various factors such as the version of SQL Server, compatibility levels, and even scalar UDFs can impact performance optimization. The video also delves into using extended events to track down these issues, offering a glimpse into the intricate heuristics that determine whether or not these features are utilized.
Full Transcript
Erik Darling here with Darling Data. I have no funny introduction to this video at all. Nothing. I got nothing. I am recording this video shortly before dinner, so two things are going to be true about it. One is I’m going to really want to get to the end. And two is I might be a little distracted because I’m thinking about some steak here. Once, once Erik Darling has steak on the brain. It’s tough, tough to get it, tough to get it off the brain, really. Unless you have a, unless you use a lot of butter. I apologize to any vegans in the house. I am not one of you. It’s, it’s cool that you’re, you’re, you, just not my thing. So we’re going to, I want to talk about some of the other things. I want to talk in this video a little bit about the fickleness and the limitations of some of Microsoft’s intelligent query processing features. You know, Microsoft loves to put its, put its hands on its tips and stick its chest out and act like it has this, you know, fully automated performance fixing database system. But in reality, there are a lot of limitations to these things. And a lot of the times, man, it’s, they just don’t kick in. These things don’t kick in when they should.
So, uh, we’re going to look at two things. Uh, we’re going to look at, uh, an example of where batch mode on row storm should have kicked in, but didn’t. And an example of, and then I’m going to show you like some of the extended events you can use, uh, just a short, um, a short list of things you can use to try and like, just to give you an, show you like the, the, the amount of stuff that has to go on in order for SQL Server to actually engage these intelligent query processing features. So, uh, for the first demo, I have, uh, I have a votes table indexed in two ways. Once on vote type ID and creation date, and then once on creation date and vote type ID. And I’ve hinted three queries to use, uh, the clustered index and then those two nonclustered indexes up there. And the idea of the query is, uh, there’s the index hint. That’s the only thing that’s different about the three iterations here, is to find the min and max creation date grouped by vote type ID, right? Right in there. And so we look at these query plans, uh, there’s going to be one, the, the one in the middle is different. And the one in the middle is different because, uh, it did not use batch mode on rowstore. The first one right here, uh, this is in batch mode, right?
You can see the batch right there, batch mode on rowstore. Good for us. This hash aggregate also executes in batch mode. And just as a sort of like SQL jeopardy note here, uh, in up until batch mode came along, the only way that you could do a global aggregate like this was with the stream aggregate. You couldn’t use a hash aggregate for it. Um, so that, that was written in like a whole bunch of SQL Server literature for like, you know, a very long time because most of it, or actually all of the good, all of the good SQL Server books were written way before batch mode came out. Actually, no, that’s not true.
The last good one was 2012 when batch mode first crept out of existence in SQL Server. It wasn’t, but batch mode wasn’t very good then. So, um, yeah, 2012 was about the cutoff for good SQL Server books with the notable exception of Great Post, Eric, available on Amazon. Um, I forget how much it is. It’s like, whatever. Don’t, it’s a good book. Buy it. If you buy it, I’ll sign it. How about that? Uh, but anyway, uh, if we look at these plans, uh, and this isn’t the end of the world for this query, right? It’s not, this doesn’t, it doesn’t make a huge difference here. Uh, the, what the query that doesn’t use batch mode on rowstore, uh, takes 2.1 seconds. The queries that do use batch mode on rowstore take about 1.8 and 1.7 seconds.
So it’s not a dramatic end of the world here. It’s just one of those crappy, it’s one of those crappy things where like, you know, you do your job and you create a good, useful index and SQL Server, like, because of, you know, various costing things, uh, does not choose like, you know, like what would be a, you know, overall like a time saving query plan. This is a very small example of this happening. Uh, I work with clients all the time where there are much, much bigger examples of, uh, these intelligent query processing features not kicking in when they should. And, you know, like me having to like, like to play all sorts of tricks and do all sorts of weird stuff in order to get SQL Server to, you know, intelligent query process something.
Right. Which isn’t a good thing because I dropped out of high school the day I turned 16. Uh, I, I, I’m turning 44 at past summit this year. It’s been a long time. This year I’m celebrating my birthday at past summit when I was 16, I celebrated my birthday by not going to high school anymore. Um, so it’s not a good sign when this guy has to go in, in, in an intelligent query process SQL Server because, uh, it shouldn’t be that way. I should be giving SQL Server wedgies and smoking cigarettes in a gas station parking lot. What should be happening?
So, uh, this is just like a, you know, kind of a crappy limitation here, uh, or kind of a crappy example, but SQL Server not, you know, doing something that it should because like we’ve got cost it out. Right. So like the, for the second query that doesn’t use batch mode on rowstore, uh, we use the even Steven index, right? And the even Steven index, if we look up here has vote type ID first and then creation date. And what having vote type ID first in the index does, of course, because you watch all my videos, I don’t want to say religiously, um, dutifully. No, no, that sounds gross too. Uh, uh, enthusiastically. Yeah. There we go. Um, cause you have a bad day if you didn’t spend it with me, wouldn’t you? Okay. Uh, so because this, this index puts vote type ID in order, right? The orders, the vote type ID column, leading column of the index vote type ID is in order because this column is already in order. It’s, it’s, it’s SQL Server thought it would be really cheap to just stream it right into the stream aggregate because the stream aggregate expects ordered data.
That’s why things were a little bit less things were a little bit choppier on these ones. And we didn’t choose batch mode or rowstore because it was just like, well, I, I, I, I, I stream aggregate. I have to sort 53 million rows. Not that SQL Server has been smart enough in the past to not sort 53 million rows. Uh, there was that merge join video from a little while back, but anyway, a digression here. Uh, so that, that, that, that’s this thing, right? And there are all sorts of, um, extended events related to, uh, to the intelligent query processing features to either tell you like sort of about what they do or like why they weren’t used or, you know, what, what needs to happen for, uh, for them to, uh, come into play. And you can usually track down these extended events. And, uh, you know, so this top one here is bat is the batch mode on rowstore heuristics.
And there are a bunch of reasons. There are a bunch of things that SQL Server looks for. And holy cow. What? This is when every other thing is true, but you still dinner. Ah, the Welsh keyboard kicked in, uh, uh, did not cross the, did not cross the road.
Oh, did not cost the, cross the cost threshold. Oh man. You should have stapled it to a chicken. Anyway, uh, so this is the list of stuff where, um, you know, that, that would lead SQL Server down the path to choosing, um, to choosing batch mode on rowstore.
Uh, the second query result is a list of, list of reasons why, oops, sorry. I touched the wrong thing there. Oh, dear me.
All right. Now, this is a list of reasons why SQL Server might skip using an adaptive join. Uh, these are not particularly, uh, legible to human beings. Um, I do have, uh, a DBA, uh, sorry, I have a DBA.
I have a, uh, Q, self-answered Q and A on Stack Exchange where I do give examples of a lot of the, what these things mean. Uh, because at the time I was quite fascinated by them. Um, and then, uh, this bottom section, uh, just talks a little bit about memory grant feedback stuff and, like, the steps it goes through.
Uh, and, oh, look, we have deprecated things in here. Um, replaced with current execution count and adjustment count. Okay.
So, so these things replace those two other things. So, don’t pay attention to these things, apparently. These things, no good. Uh, pay attention to the other things. They are not deprecated. Great.
Uh, but this, like, talks a little bit about, you know, the, like, like, the, the process that memory grant feedback goes through as it’s just in query memory. So, there’s a lot of stuff in here. And the, and these, these heuristics and limitations are, of course, hinged on, uh, or predicated on a bunch of other stuff.
Just, like, for example, you have to be on SQL Server 2019 or better or some cloudy equivalent. Don’t use, don’t use managed instance. Uh, some cloudy equivalent where, like, you’re on an enterprise-y thing.
You need, uh, to be in a modern compatibility level, like 150 or, uh, 160 for a lot of these. Uh, some of them are earlier, uh, like, adaptive joins are, like, 140 for SQL Server 2017. Uh, there might be a couple of few from 2016, but you do have to be in, like, 130, 140, 150, 160.
Something rather new. And each, and for each version of SQL Server that introduces new intelligent query processing features, you have to be in a higher and higher compatibility level in order for those to be unlocked.
That’s why for a lot of queries, when I want to differentiate things, you’ll see me use the, the use hint that sets the, the, the, the query compatibility level to a higher compat level so that I can experiment with the batch mode on rowstore stuff and see, like, the, like, the before and after. It’s a lot easier to do it that way than it is to, um, do it in the reverse, at least for me. So there’s all sorts of, like, so, yeah, back to what I was saying.
Compatibility, enterprise edition compatibility levels, version of SQL Server. So there’s, there’s, like, a lot of stuff that has to, you know, already be in place before any of these intelligent query processing features will even think about, thinking about anything. So if you’re, if you’re out there in the world and, um, you know, you’re, you’re having a hard time with SQL Server and you’re, you’re wondering why all of these, like, awesome intelligent query processing things aren’t just making your life so much easier and better.
It’s probably because either Microsoft doesn’t think you spent enough money on SQL Server, which, you know, if you’re on standard edition, you get what you get. You don’t get upset. You pick the cheap one, right?
Not me. You may not be on a modern enough version of SQL Server. You know, 2019 and 2022 have the most up-to-date intelligent query processing features. And then lastly, you, your database might not be in a compatibility level that allows for any of these intelligent query processing features to be used.
And all of these things are going to hold you back. And there are various reasons why you might be in that, that condition, right? It could be a vendor thing where they’re like, no, you can’t use a newer version of SQL Server.
No, you can’t use a higher compatibility level. We haven’t certified, blah, blah, blah. You know, there’s just all sorts of crappy reasons in the world for that stuff. You know, you might be able to affect some things by using query hints like, like I have on these queries to use a different compatibility level just at the query level.
That might be useful enough for you. That might get you across the finish line in a few places. But, you know, there are times when I see entire workloads where everything is right.
The SQL Server 2022, Compat Level 160, you know, the Enterprise Edition, not managed instance. And still, these heuristics, these intelligent query processing features are either, or rather, the IQP features do not meet the heuristic goals that they need to, to kick in. Or there is some limiting thing in the query itself that prevents any of the, some of the IQP features to kick in.
I think, you know, adaptive joins, we looked at a bunch of reasons there. But, you know, I think, you know, probably the biggest one for most people is the scalar UDF inlining, where the list and the number of restrictions on it seems to grow with every cumulative update. And that, you know, that’s one that I think a lot of people in SQL Server world had very high hopes for, because scalar UDFs really are the damn devil when it comes to performance tuning.
And, you know, having those not be as big a devil as they are would be nice. But, you know, we can’t have nice things. So, anyway, that’s about that.
I do, I do hope that if you, if you, if you need help with these sorts of things, you will, you will, you will think of your, your humble, young, handsome consultant, Erik Darling. Because I, I do, I do, I do tend to help pretty well with these things, despite, despite being a high school dropout. So, because they’re tough problems, and it seems like every time Microsoft adds a knob, 17 things become far more complicated to track down and pinpoint.
And it gets, it gets to be rather cumbersome. So, anyway, I’m going to go eat dinner now, so I can get the steak off my brain and into my belly. And then I can record stuff that makes probably more sense and is more coherent.
And, uh, no, I’m going to go eat dinner now. A little more focused. That’s the word, focused. So, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope that you will like me. Love me.
I hope that you will like this video and subscribe to my channel. And join the nearly 4,000 other data darlings who have, who have, who have done that. Who have subscribed to my channel.
It’s probably the same, like, five people who like everything. That one, one, one person who, who dislikes everything. It’s okay. I still like you. I still like you.
I will still, still give you a hug. Just for, just for showing up. Just, just for giving me the view. Even though you gave me a thumbs down. Even though you gave me the view. I’ll, I’ll give you a hug for that.
Nice, big hug. So, anyway. Yeah, it’s steak time. I’m out. Goodbye. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.