A Follow Up On Fixing Parallel Plan Row Skew In SQL Server
Video Summary
In this video, I delve into the issue of parallel skew and share a practical solution to address it in SQL Server queries. Parallel skew can be a tricky problem, especially when dealing with highly skewed data distributions that lead to inefficient thread utilization. I demonstrate how creating an artificial distribution by using a `VALUES` clause and joining it back to the main table can help achieve better thread distribution while also making the query more batch-friendly. This approach not only improves performance but also showcases the importance of considering parallelism in query optimization, particularly when natural solutions are insufficient. Join me for the next video where we’ll explore other aspects of SQL Server query optimization!
Full Transcript
Erik Darling here with Darling Data, and in today’s video, we are going to talk about how to fix parallel skew, or rather how I fix parallel skew for a particular query. All right. I can’t pretend this is going to be the thing that you do every single time you have this problem, because this is hopefully a rare problem, at least in today’s day and age, but you can still run into it plenty of times. Lord knows I do. And I find it interesting, and I’m going to talk about this. I’m going to be a quick-enough to talk to you about, and I’m going to be able to tell you a bit about it. And I’ll talk to you about it. So, if you find this interesting, well, stay tuned. Down in the video description, all sorts of helpful links. Perhaps you have lots of parallel skew, and you would like a young, handsome consultant with reasonable rates to come fix it for you. You can find all sorts of helpful links to do that. You can also buy my trainings. You can also support this channel with money, not just with various forms of internet applause. You can ask me office hours questions. That is a totally free endeavor for you. And I will answer five of those every Monday. And of course, if you enjoy this content, please do like, subscribe, tell a friend or two, tell your AI girlfriend who hopefully wasn’t too terribly affected by the recent AWS outage. We pray for her well-being.
I will be at Pass Data Community Summit in Seattle, November 17th to the 21st, giving two days of the best T-SQL pre-connery that has ever existed with Ms. Kendra Little. And then, I don’t know, well, hopefully I can talk about my other upcoming stuff soon. It’s just, people are very slow to announce things. Apparently, they just like to wait for excitement to boil over. Anyway, let’s talk about this parallel skew stuff. I’m going to make sure I go to the right copy of Management Studio here. So, let’s start off on this tab, because this tab is going to show us why we have such skewy problems with these.
So, what I’ve done with the votes table is something kind of mean. And so, to give a little background on this, when you have very skewed data, that can lead to very skewed parallelism. And in the public version of the stack overflow database, swallowed a bug. Most of the voting history has been anonymized, of course, because voting is a private endeavor. It should not be made public.
And so, what I’ve done is I’ve made a copy of the votes table. And I’ve set the user ID column equal to whatever the vote type ID was. So, vote type ID is a number from like 1 to 8 or something that has various types of voting that people can do on questions, comments, and answers, and whatnot. Well, I guess not comments in this table. But, so what I’ve done is I’ve effectively made a very skewed user ID column.
And I’ve created an index and all this other good stuff. But, this is the way that the user ID column breaks out. So, for vote type IDs between 1 and 4, these are the most common vote types. Well, actually, three of them are very common. One of them is not terribly common.
But, vote type of 2 is an upvote. Vote type of 1 is accepting an answer as the answer, the little green checkbox. And a vote type of 3 is a downvote. So, sorry, upvotes, accepted answer, downvote if I have my voting history correct here.
And 4 is like offensive or something. So, not too many offensive flags. Because Stack Overflow is a place for friends. It’s also going the way of MySpace, a place for friends at this point.
But, at some point, I’m going to be giving demos on Stack Overflow. And someone’s going to be like, what is Stack Overflow? I’ve never heard of it. It’s going to be funny.
Anyway. Anyway. Yeah. So, this is the way that these integers spread out. And these are the numbers for those. Because we have a group buying account and whatnot.
So, 37 million, 3.7 million, 3.5 million, and 733. All right. So, when we run a query that does, like this, that does this outer apply and whatnot. And we look at the query plan.
You may remember this from the original video that I did. And we come over and we look. I mean, not there. The tooltip is utterly useless for discerning what our problems are here. If we look at the way that rows were distributed across our DOP 8 plus 1 threads, you’ll see that they all ended up on thread 3.
Right? That number there would be the total count of the 37 million plus the two around 3.5 millions plus the 733. They all ended up.
So, the parallel page supplier runs this hashing function. And depending on how things hash out, that’s how the rows get distributed to threads. It’s dealing out all these rows. And unfortunately, our hashing function kept hashing these things out this way.
Now, sometimes when you do this, you can set DOP to different numbers, like especially odd numbers, in order to get the different thread distributions. But I tried that for every DOP between 2, which got me a serial plan all over again. So, theoretically, in reality, it was really between like 3 and 16 in order to get a different spread here.
But they all ended up, I mean, not always on thread 3, but always all on a single thread. And you can sort of visualize how this happens a little bit differently if you rewrite the query to turn this from one seek into, I guess, four different seeks. So, if you do one select for vote type ID 1, one select for vote type ID 2, and one for 3, and one for 4, and so on.
And then you get the dense rank over those. Then you get a slightly, well, you get a very different execution plan, but it helps you to sort of visualize things a little bit, right? So, if you look at these, we get one, two, right?
You can tell that’s 37 million, or sorry, I think that’s, yeah, that’s 3.7 million. That’s 37 million, probably. That’s 3.5 million.
And there’s our 733, right? So, we can see all four of the individual seeks when we do this. And if we go look, again, not at the tooltip, which keeps really making its presence felt here. We’ll see all of the rows end up on thread 1 here, right?
Which is, you know, not great still. And if we look down here, we’ll see all 37 million rows, well, they all ended up on thread 1. And then down here, there’s 3.5 million rows all on thread 1.
And over here, there’s 733 rows all on thread 1. So, all of these ended up hashing out to the same thread. Right?
Just in different seeks. And then if we come over about here and look, we’ll see thread 1 gets all 44 million some odd threads, rows on one thread. So, things do not end up better when we do this.
SQL Server does not like, unfortunately, SQL Server does not like spread those out better or do anything else smarter. It all just sort of ends up a complete washout. Now, the one way that I was able to rewrite this query in order to get better thread distribution was to do this.
So, rather than correlate on the four vote type IDs that I cared about, remember, because in the original query, what I had done was written where vote type ID is between 1 and 4 here. Right? So, we kind of narrowed it down.
We kind of forced ourselves to have a crappy parallel distribution on this one. But it’s a lot easier to do that intentionally than it is to get it to happen by accident. So, we kind of forced the issue on that one.
But what I was able to do to get this one worked out was to sort of nest things a little bit and put the numbers 1 through 4 into a values clause. Right? And call that vote type.
Alias that column that comes from these as vote type ID. And then left join the select here and join that on vote type ID there and say where user ID equals user ID out here. Right?
So, oh, it’s still logically a correct query. And when we look at the query plan for this one, this is kind of at the intersection of fixing parallel skew and being batch mode friendly. Because what you’ll notice in this plan, which is going to be different.
So, like, just to come back to these a little bit. You see we have a parallel nested loops here. And for this one, we have a parallel nested loops here as well, if I’m remembering correctly. Ah, I was.
Wonderful. Yeah. So, parallel nested loops. Good, good, good. Everything correct there. Now, we get something different. We get hash joins. Right? We have two hash joins instead. And if we look, we have this constant scan.
So, this constant scan is going to be the four rows, the numbers one through four that I put into that values clause. And if we look, I mean, there’s obvious signs that we got batch mode, like the window aggregate here. The important thing is this query finishes in about four seconds.
And the other queries over here, this one finished in 29 seconds. And this one finished in 59 seconds. So, I guess writing the four seeks out as multi-seeks was at least somewhat helpful by about 20 seconds.
But probably not where we want things to be anyway. But this query, if we come over and we look at the index scan over here, again, not the tooltip. We’ll see that the rows ended up hashing out very nicely on this one.
Right? I mean, there’s a little outlier here. Like, this is maybe not some, maybe not perfect, but way better than all 44 million rows ending up on a single thread. So, this is sort of at the intersection of fixing parallel skew and writing batch mode friendly queries, right?
Somewhere in there. But, yeah. So, you know, obviously, this is happening in batch mode. If you look up there, this sort will be happening in batch mode.
And since this sort is a child aggregate of this batch mode window aggregate, this sort operator is a child operator of the batch mode window aggregate. There we go. Batch mode sort, blah, blah, blah, blah, blah.
Then the window aggregate can read from the batch mode sort using parallel threads. Usually, the producing side of a batch mode sort will be single threaded, but not when there’s a window aggregate involved. And so, we get batch mode pretty much across this whole thing.
And this is exactly what we want. It’s just batch, batch, batch all the way down. And we get a much faster query with much better thread distribution. So, when you’re dealing with parallel skew, particularly like the kind that you cannot resolve naturally, maybe by forcing a different dop.
Again, particularly odd number dops tend to fix stuff like this. But it didn’t happen for me here. So, I was forced to rewrite the query in sort of a weird way.
But, you know, I’ve used the values trick in other contexts before in order to get different query transformations from things. Usually, you would take values and cross-apply to them. But doing cross-apply here, or it would be outer-apply because it’s a left join.
Using outer-apply here produced the same skewed parallel nested loops plan as before, which didn’t buy me anything. But doing the join, that means you can’t correlate inside of this. You have to do it outside of this.
So, you actually force SQL Server to do all this stuff and then join onto it here and then correlate to the user ID that gets produced here after that. So, it was a much more efficient use of threads and times. And, of course, getting batch mode involved was a very big help in making this thing go much, much faster.
Because we are processing a lot of rows, right? 44 million rows. You want batch mode for that many rows. That’s a good number of rows to batch up and not deal with on a row-by-row basis.
Because internally, that’s what row mode is. One row at a time. And batch mode is, of course, multiple rows, SIMD instructions, CPUs, and all that. So, this was just a fun way to rewrite this query to, A, fix the parallel skew, and as a happy side effect.
A very happy side effect. There are no fiascos, just happy screw-ups. I’m going to skip the alliteration on that one.
But this is just a happy accident that batch mode also got involved when I changed the format of the query. Anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you over in tomorrow’s video where we will abandon all parallel skewness. All right. 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.