Multiple DISTINCT Aggregate Performance In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into a common performance challenge in SQL Server queries: multiple distinct aggregates and their impact on row mode execution. I explore why these operations can significantly slow down your queries, especially when dealing with large datasets. We then dive into how batch mode can mitigate these issues, showcasing the differences between achieving batch mode through temporary objects or compatibility levels, and how this can drastically improve performance. Additionally, I discuss the limitations faced by Standard Edition users, suggesting alternative strategies such as using temp tables to bypass some of the inefficiencies associated with spools. By the end of the video, you’ll understand not only why these issues arise but also practical steps to optimize your queries for better performance.
Full Transcript
Erik Darling here with Darling Data. According to the weather outside, I am recording this video during the end times. It’s wild out there. Whew! Hurricane Debbie, what a gal. Anyway, sorry if that clap was loud. We should clap lower. Maybe that’s less painful for you. I’m never going to hear this, so deal with it. Alright, so let’s talk about what we’re going to talk about today. In this video, we’re going to cover how multiple distinct aggregates are tough for performance in SQL Server queries, but mostly only in row mode. We’re also going to cover how batch mode can help, but also how Microsoft has no respect for standard edition users. So we’re going to cover all the fun stuff today. As usual, this channel is free. I don’t have commercials. I don’t have any paid sponsors.
MeUndies has not beaten down my door, mostly because I don’t wear underwear. So if you want to sign up for a membership to show your love and appreciation for the channel, you can do that. There are some cheap ones. I’m going to work on better stuff for the higher price ones when I have some time to think. That doesn’t happen a lot. But when it does, boy, the gems come out.
If you’re unable to monetarily support the channel, that’s totally cool. There are free ways to show me that you care. You can like, you can subscribe, you can leave comments, all sorts of fun stuff. So if you can’t do one, think about doing the others, because I do enjoy the feedback. I like the engagement. I like knowing that the things that I write and record reach as many people as possible. That’s what makes me feel good. If you’re in need of a SQL Server consultant, you’re having some performance problem or other thing, and you need a trusted, reliable set of eyes and hands, and I’m not going to put feet on your SQL Server. That’s weird. That cost extra.
You can hire me to do that. My rates are reasonable. If you’re looking for affordable SQL Server training at the beginner, intermediate, or advanced levels, you can go to training.erikdarling.com, and you can get 75% off the whole kit and caboodle with the discount code SPRINGCLEANING.
There’s also a link for that in the old show notes. So you can click on that, and it might be a bit easier for you than remembering all those letters up there. URLs. Wow. What a thing. As far as when you can see me live and in person on this slide with a tremendous amount of white space that I have not figured out what to meaningfully do with yet, perhaps I just need a graphic floating over my head or something.
You can catch me Friday, December 6th, doing a full-day pre-con at Data Saturday Dallas. You can also catch me November 4th and 5th at Past Data Summit in Seattle, where I’ll be all week doing Seattle things. I don’t know. There’s a presidential election that week. Who knows if Seattle will survive that? So I don’t know. Whatever. Only time will tell. Let’s get on with the demos for today’s topic of interest. So when you’re working on reporting queries, there are a lot of times when you’ll need to use distinct, lest we end up counting many useless things. It’s not just for counting. It could be for summing or averaging or basically anything else that you can put in an aggregate, min, max, I don’t know, stdev, standard deviations. There are so many things you could do. So many things. But you got to do it. The problem is that when you do it in row mode, it’s very slow. The more you distinct, the more slower you get.
So let’s look at the query plan for this, which has one, two, three, four, five distinct aggregates applied to it. Now, the slow parts of the plan are fairly obvious once you start looking. The stuff that happens over here with the joins, not that big a deal. Where things get wonky, and we talked about this a little bit in the video that I did on top n% performance queries, where as soon as you need to start streaming into a, you know, because, you know, parallel spools, ha ha ha. As soon as you need to start streaming rows from multiple threads into a spool in tempdb, your life is going to be in for a awful, just a awful. Your life is going to be a awful.
A awful. This is not fun. Notice we go from 22 seconds here to a minute and 12 seconds here. That is no bueno. There are zero buenos about this. On the bueno scale, we are at absolute zero. Right? There have been no buenos. Things after that generally don’t change much. Right? Through all this, we’ve added on like another three seconds in this branch from, go from 112. Well, look, 115, and then it’s 115 all across. But then we hit three minutes and two seconds.
Por qué las embarguesas? Why? How did that happen? Well, that happened for, I mean, a few different reasons. You know, notice that we have all of these sort of headless spools. All of these spools are actually fueled by this spool, right? This is, this is, this, this is the spool that fuels the spools.
Ah, that made my mouth feel nasty. Yeah. Spool fuel. Hmm. Spoodum to fuel your spools. Spool your, fuel your spool. Uh, so each one of these spools, uh, has a whole bunch of rows in it. And each one of these spools will go out into an aggregate and, you know, we’ll, we’ll do some work in order to make whatever we’re aggregating distinct. This happens a whole bunch of times, but where this causes a real, a real kerfuffle in the query plan is specifically in this branch where we have a hash aggregate that spools for a very long time. This, my friends is again, this is negative on the Bueno scale. We have gone negative Buenos. Right? There was a, a, a, a Bueno deficit for this particular branch of the query plan. So this, this, this entire thing ends up taking again about three minutes. Now what’s funny is that this isn’t, that you can avoid a lot of these problems with batch mode. And depending on where you are in the SQL Server world, as far as version and addition and, you know, uh, compatibility level and stuff, um, you know, you, you can deal with this in different ways.
The big deal with batch mode is that you get rid of all those gigantic spools. Uh, spools in a SQL Server query plan are often a sign that you have done something drastically, tremendously wrong that needs correcting. Spools are there for your benefit, but they only benefit bad mistakes.
They don’t tend to benefit good choices. They tend to benefit bad choices, right? So they’re, they’re like a little insurance policy. They’re a little, uh, safety rail for, for your queries when you, when you do something terrible in them. So the two ways of approaching batch mode are of course, uh, to, uh, either create a, some sort of object. It doesn’t have to be a temporary object. If you’re using a SQL Server 2019 in memory, temp TB feature, uh, you can’t create a, a temporary object with a columnstore index on it.
So, you know, you can, you’re, but you’re perfectly able to create a stationary permanent object in your database, uh, that has a columnstore index on it with no rows, or you can add a non-clustered filtered columnstore index to a table that has like filters still to something that where nothing, no rows can possibly exist. And when you, when you do this, um, you will get a sort of bastardized version of batch mode and it’s bastardized because it is not fully implemented. It is not as tightly ingrained into the optimizer is when you use a higher compatibility level to get batch mode on row store. I have to be very specific about this, that, uh, fooling SQL Server into using batch mode some of the time is not the same as getting batch mode on rowstore, the intelligent query processing feature.
Uh, the intelligent query processing feature does a whole lot more, uh, is able to do affect a whole lot more operator operations in your query plan than just tricking SQL Server into using batch mode for some operators. All right. So we’re going to run these two and these will not take three minutes to run thankfully. Um, but while these execute, I do, I do want to say, I mean, that’s about seven seconds there. So, uh, well, these are, these execute. I do want to say that part of what makes spools rough is that they have like, when you load data into a temp table, uh, you know, there are all sorts of optimizations that temp table loading has gotten in SQL Server over the years. Like there were some big ones in 2014 and, you know, I’m sure some other ones in newer versions and things have gotten better over time, you know, as they sometimes do. Uh, but part of the problem is loading data into the spool. It’s a single row at a time. It’s very, very slow. You have none of the temp table optimizations behind spools that you have for temp tables. So, you know, like part of what makes that query, aside from that monstrous hash spill up, part of what makes that, those, the multiple distinct query up, they’re really slow. It’s just loading that big spool, right? Loading millions of rows into a spool is always going to be painful because of how little love and attention spools have gotten over the years. So if we look at the query plans for these two, they’re not that different. Now, this is good for us because, you know, if you’re not in a position where you can use a higher compatibility level, even for a single query, like I did for this one, uh, but you can sort of finagle with the query a little bit, either, you know, it’s a tricky SQL Server and using batch mode, you can get much, much better performance. We’re down to four and a half seconds from three minutes. And notice we don’t have any big crazy spools in this thing, right? We’re not spooling in billions of rows. We’re not hash spilling. Nothing awful is happening here. The second query where I do get batch mode on row store, that finishes about 1.3 seconds faster, right? Which is not a not a not a not meaningful improvement, right? The difference is, of course, uh, in reading from the votes table. Uh, so the votes table up here, we take 3.1 seconds to read from that. And down here, we take 1.9 seconds to read from that. The difference, of course, is that in the query where we trick SQL Server into using batch mode for some operators, we still read from the votes table using row mode, right? Because when, again, when we play these tricks on the optimizer, we don’t get the fully ingrained batch mode on rowstore experience. We get like a, like a, like, I don’t know, like, we get like the half naked show, not the fully naked show. So we, well, we do get batch mode for this hash join and for this hash aggregate. We do not get batch mode anywhere else. In neither, in neither of these queries do we read from the post table using batch mode, uh, largely because the post, oh wait, we do. Weird. That didn’t happen before. Hey, that’s funny. We got batch mode on row store here too. Usually I don’t because of the body column in the post table. Uh, maybe I’m just lucky on this one. I don’t know. So, uh, cool. But, uh, the reason why I thought I still wasn’t getting it is because there’s not a really big difference in how long it took to read from there. So, uh, I’m not going to start this video over again. We’re just going to say, cool, it can happen sometimes. Usually I don’t get batch mode on the post table though. Usually the big and VARCAR max columns in there, uh, completely to the SQL servers like, nah, nah, we’re not doing it. So, uh, yeah. So we do save a little bit of time here. Now let’s say you are, uh, on, uh, Enterprise Edition. Either one of these things will be fine for you.
These are great. These are great options if you are on Enterprise Edition and you want this query to go faster. If you are on Standard Edition, if you are cursed, if you are doomed to some bottomless existence, uh, some pit that you will never arise from, uh, using Standard Edition, one thing that you can do is you can replace the spool with a temp table by loading data into the temp table, which has all of the optimizations that you would expect. Uh, just, you know, not, not a spool, right? Uh, you, you might not want to load huge temp tables up, but hey, everyone has their own fetish. If you’re on Standard Edition, you just might fit somewhere in the S or the M world of things. Maybe, maybe even both, right? I don’t know. Uh, so we’re going to try this query out in two different ways. Now, the reason why I bring up Standard Edition here is because in Standard Edition, batch mode stuff, columnstore stuff, uh, is limited to at best a DOP of two. So if you, if you are, if you find yourself in a situation where, um, maybe you get okay performance from a DOP to, uh, batch mode plan in Standard Edition, cool. If not, you’re going to have to do something with yourself that gets you, uh, a, a higher DOP plan. Um, you know, DOP 8 is a pretty good DOP. I’ve had pretty good luck with DOP 8 over the years. Uh, um, you know, my, I am a, I am a DOP 8 proponent in most circumstances. Um, so one thing you can do is you can drop, um, all of the, the things that you want to deal with into a temp table.
Now this loading up the data into this temp table will be a lot faster than loading it into the spool. Remember the spool took like, I don’t know, 30 seconds or something. Loading it into the temp table takes about 7.3 seconds. Is that, is that super fast? No, but it’s better than like 30 seconds.
And then once we work off, once we’re working off that, things will get a bit better for us. So, uh, well, you know, these, I probably should, I probably should have started these running while I was, while I was off talking about Standard Edition and all that goofy stuff. But, uh, if you notice, uh, in this, this query plan, we no longer have all of the terrible awfulness that we had in the other query plan, right? This is the first one, which, which is, which is in row mode again, but we don’t have all of the spooling, right? Because we’re just working off, uh, this temporary object for all of them. And even though we get a spill in the same place for, uh, for this, this leg of the branch, uh, we get, it’s far less of a severe spill. This is not spilled for one minute.
And so this ends up running for about six seconds rather than about three minutes. Now, working off the, uh, temp table, uh, over here, this runs for about a second, right? So pretty good. That’s, and that’s the batch mode version. Cause this is the, in this version, I bumped, uh, the compat level up to 160, 150 would have been sufficient here on developer edition to get batch mode on rowstore. But even like the non batch mode version, working off the temporary object is a lot faster, uh, both because you take the place of the, the very inefficient spool loading in the, in the initial query. And because, uh, you don’t have, you don’t reuse that spool over and over again. And you know, the estimates in this one are better because we’ve stabilized the result set. We are no longer as prone to estimation errors because we have a stable temp table that we’re working off of not like, Oh, I don’t know what this join is going to do. Let’s all, let’s all have a weird day. Uh, so things turn out a bit better in this one.
So, uh, to recap multiple distincts in that and row mode queries, uh, can have a really nasty, we have really nasty performance side effects. Uh, I think probably the best way of dealing with them on enterprise edition is to get batch mode involved in some manner. Um, I think, you know, batch mode on rowstore, uh, does have more optimizer support for more operators than tricking SQL Server into using batch mode, but sometimes just the batch mode trick is enough. Uh, if you’re not on enterprise edition, uh, I would highly suggest the use of temp tables, uh, because when you use temp tables, uh, you take the nasty spool, the nasty, inefficient spool out of the equation and, and you can use DOP up to whatever DOP you have set for either the server database query, wherever you choose to set DOP, wherever your DOP fetish setting lies, you can, you can, you can set DOP there. Um, and, uh, you, you know, not saying it’s not worth testing the, uh, batch mode stuff on standard edition, just in my experience, the DOP two limitation really does hurt performance, uh, uh, you know, in, in many, many situations. Um, you’re, you’d be very lucky if DOP two batch mode were equivalent to like DOP eight row mode depends on some stuff worth testing, but prepare yourself for disappointment. Of course, if you’re using standard edition, you are already well positioned to deal with disappointment. So you have that going for you. Um, there, there’s always a silver lining in the, in the, in the darling data playbook. So, uh, I think that’s good for this. Um, uh, I hope you enjoyed yourselves. I hope you, hope you learned something. Uh, I hope you paid attention to me babbling about liking and subscribing at the beginning of the video, because I’m not going to do it again at the end, because let’s face it, this, this isn’t where, where any, anyone else, uh, anyone, anyone pays attention. That’s this, it’s over by now. You’ve drifted away. You’ve gone to sleep.
You’ve, you’ve hit fast forward. You’ve, you’ve, you’ve, you’ve absorbed all of the usefulness of this. And now you are just, we just had this useless husk of time left to, to, uh, to, to, to, to close things out. So, uh, I’m going to go stand in the rain for a little bit, take myself a nature shower. 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. 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.