Query Plan Patterns For Windowing Functions: Aggregate Function Atrocities
Thanks for watching!
Video Summary
In this video, I delve into the fascinating and sometimes perplexing world of aggregate window functions in SQL Server query plans. After wrapping up a trilogy on these complex queries, I explore some of the more peculiar behaviors you might encounter, such as unexpected range clauses and disk-based window spools that can significantly slow down your queries. I also discuss how to leverage batch mode for better performance and introduce non-clustered columnstore indexes as a powerful tool for optimizing paging operations. By understanding these nuances, you can improve query efficiency and overall database performance.
Full Transcript
Erik Darling, one man party here, Darling Data. In this video, we’re going to round out our Peter Jackson-esque trilogy on window function query plans by talking about some weird stuff that you might see when you start looking at aggregate window functions. So stuff like count, sum, average, stuff like that. when you add windowing clauses to them. But before we do that, my spiel of appeals, if you like my channel enough to subscribe, you can do that for like four bucks a month. If you don’t have four bucks a month, or you just hate me, which I understand, you can engage with me in other ways, like liking and commenting and subscribing. All great ways to let me know you hate me. The best way to let me know you hate me is to give me money, though. So if you really don’t like me, sign up for lots of money. If you’d rather spend more money hating me, you can hire me to do actual factual in person SQL Server consulting. Not exactly in person, kinda on Zoom. But I mean, I would show up to offices if anyone had offices anymore. Which is sort of a funny thing to think about. But if you need health checks, performance analysis, hands on tuning, or you’re having some sort of SQL Server emergency, or your developers are causing SQL Server emergencies, and they need training, you can hire me for any of those things. If you just want some cheap training, you can get 24 hours of it for about 150 bucks with that discount code. It’s a great deal. 150 bucks over the rest of your life unless you are ancient or terminally ill, or you sail a little too close to the winds with your personal habits.
That 150 bucks will go a long way. If any of those other three things apply to you, though, what do you care about what you do with 150 bucks? You’re on your way out anyway. I’ll probably be around for at least 150 bucks more worth of fun. Might be nice to leave something behind. If you want to see me in person, I have live events coming up with more ChatGP. JPT created images. Friday, September the 6th, I will be at Data Saturday Dallas doing a full day pre con and then talking way too much of the regular event. And November 4th and 5th, I will be at Pass Data Summit in Seattle, Washington, where you can catch me and Kendra Little coming together to give two days together co hosting SQL Server performance tuning pre con fun. So with that out of the way, let us begin the partying with Erik Darling, the one man party.
All right. We managed to hit the escape button almost flawlessly. I think we’re off to a good start. We can probably just call it here, right? I should have put that down first. So I’ve got a slightly different index here than I had previously. So this index is on user ID, creation date and score because we are doing this with our windowing function and we are summing this.
And sometimes it helps to have data in order for things like aggregates, right? Ordered data goes into an aggregate pretty quickly. Kind of a nice touch. But what you should know is that by default, when you use aggregate window functions, you get this range thing happening.
And the range thing is usually not what anyone wants when they’re using these windowing functions because it sets up weird, you know, sort of, what do you call them? Like segments of stuff. I’ve always found it counterintuitive what range does. At least, you know, by default, it’s always been kind of odd to me.
So it also, when you use range, you have this nasty side effect. And that is that you use a disk-based window spool to do your work. And those disk-based window spools can be quite slow.
If we look at the query plan up here for this first query, and this is a query that I use a parallel hint on because without a parallel hint, I get a single threaded plan that runs for about two minutes. Now, granted, this thing running at DOP 8 for 30 seconds is not like a completely linear scaled-up improvement, right? 33 seconds at DOP 8 versus two minutes at DOP 1.
Those seven DOP threads may be not pulling their weight so great in this one, right? But we have, in this query plan, we have this window spool right here, which is really a large part of our issue. As you can see, we jump from about 17 seconds here to about 30 seconds there.
So this disk-based window spool is not our friend. Another time you will see the disk-based window spool pop up is if you use rows, but you have an absolutely gigantic, enormous framing of those rows, you will probably never need this. I think this is where Microsoft was like, stop here.
We’re going to punish you if you go beyond this. So if you look at the query plan for this query, it is equally as ucky as the last one at 38 seconds. Well, actually, it’s uckier by about six seconds, I guess.
And this query has not one, but two window spools in it. We have one window spool all the way over here. Yeah, no good.
And we have one window spool all the way over here. No good. Now, what’s funny is if you write your query the way most normal human beings would expect to see the data that comes out of this, rows between unbounded proceeding and current row. So not 9,999 in current, but unbounded proceeding and current.
Unbounded. We are rows everywhere. It’s wonderful. You still get a window spool, but it’s not a crappy one.
It’s not the disk-based window spool that adds a whole bunch of time to your query. There’s no real physical indicator about what kind of window spool you’re getting. The only indicator is crappy performance.
So if you get really crappy performance, you’re probably getting a disk-based window spool. If you get mildly acceptable performance, you are probably getting a not disk-based window spool. So windowing functions, man.
World’s a crazy place. Be careful out there. Now, batch mode, once again, what you should be doing with windowing function queries, utilizing batch mode as much as possible, does help with the range issues. If we run this query and we allow SQL Server to use a parallel plan in the most recent, most highest compat level available currently, we will get batch mode on rowstore, and this query will not suck.
This query will not suck because we go from using a crappy disk-based window spool to using a wonderful window aggregate, and the whole thing takes about 1.3 seconds. If the best you can do in row mode is like 30-something seconds for a query that you’ve got things pretty well set up for, you should be using batch mode. It’ll probably be better.
Unless you’re on standard edition and you haven’t paid Microsoft’s fee for liking you and you only get .2 batch mode queries. Isn’t that nice of them? What batch mode will not fix, though, is this query.
I’m not going to rerun the whole thing and make you sit here for nearly 40 seconds. I’m just going to show you that this query plan, the estimated plan for this, is the same as the actual plan for the one that we did run. Batch mode did not change this.
We are forcing, we are saying SQL Server, pretty please here. We do not get any joy there. Another thing where batch mode is a real savior is if you need to do count over. Now, I’m specifically hinting the parallel planting here because I need to show you just how big of an effect count over can have on queries.
Where I see people use count over the most is when they’re doing paging queries. They put count over somewhere in the select list because they want to know the total number of rows that qualified so they can say, you’re on page whatever of whatever that has whatever many results.
So they stick count over in there and then they wonder why performance stinks. So this took 30 seconds. And the reason why it took 30 seconds is because count over happens in a serial zone in the query plan.
Much like if you use a recursive CTE and you have a serial zone in your plan, this is almost exactly the same concept as that. All of these operators run in serial.
Run single threaded. They don’t run in serial like your breakfast cereal is on the table and they’re just like stomping through it. They all run single threaded. Big problem in here, 25 seconds in this lazy table spool.
Why is it so lazy? We don’t know. Impossible to say. But this thing takes about 30 seconds.
And then, of course, when we’re done doing all that hard work, we distribute streams and go parallel. And it has absolutely no effect on it. No effect on making things better.
So that sucks. And you shouldn’t use count over anywhere. Of course, you know, batch mode is very useful when we’re doing count over stuff. Because batch mode fixes nearly everything with crappy windowing function problems.
If you look at this, we have 319 milliseconds versus 33 seconds from before or whatever. Much better arrangement here, I think. So, once again, batch mode to the rescue.
If you are doing page inquiries and you would like to improve the performance of them almost immediately, one of the best things you can do is add a non-clustered columnstore table to whatever, like, the main query, the main table that you do your paging from.
I realize that many page inquiries have joins that, like, maybe even dynamic joins to other tables that might not always get touched. But if you have, like, that one main, like, users table or something like that, stick a non-clustered columnstore index on it.
Encourage SQL Server’s query optimizer to use batch mode or batch mode on rowstore. And you will get not only much better performance from the query, but for the windowing function stuff like count over, or if you’re using row number to do the paging, you will also get better performance almost immediately from that.
All right? Now, there are some, like, maintenance considerations and there are some, you know, design considerations around using columnstore indexes. I’m not going to get into all that here because that’s a pretty big subject.
But most of the time, you can just YOLO a non-clustered columnstore index on a table and not have to think twice about it. It’ll really help queries that do paging and stuff because batch mode is a much better execution engine for, you know, again, large amounts of data and especially anything that requires windowing functions because it really speeds those up by introducing the batch mode sort and window aggregate.
And you can really help those queries along by doing that. You also might find yourself in a particularly cheery scenario where you can get rid of a lot of the non-clustered rowstore indexes that you’ve created over the years to support different variations on your dynamic paging queries because you just don’t need to account for all that anymore.
The columnstore index can just take the place of all of those where SQL Server can read from the individual columns rather than having to use all sorts of different index configurations to help different queries be fast at different times. So, we have now completed our three-part series on windowing function query plans.
If you notice the names of some of the other tabs up here, you can see that we have many other query plan patterns to talk about. These will not take three parts, though.
These will all be one-parters. Some of these videos are not even going to be terribly long because it is just such a fun, obvious thing what happens when different things in query plans go on. So, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope that when you watch, by the time you’re watching this video, I hope that you are fabulously wealthy.
I hope that, maybe you already are fabulously wealthy. I don’t know. If you are, you should definitely sign up for a membership, though, because the fabulously wealthy have no excuse. The day someone fabulously wealthy watches my YouTube videos, that’ll be something.
I don’t know. Perhaps you’re rich in love. I don’t know. Anyway, it’s time for me to go do some actual work now, so I’m going to go do that, and I will start recording some of these other videos when I’ve finished my works.
My works are vast. All right. Cool. Thank you for watching.
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.