Video: Is Setting MAXDOP and Cost Threshold for Parallelism Really Easy?

Asked and Answered

---

Thanks for watching!

Video Summary

In this video, I dive into the nuances of setting `max degree of parallelism` (MAXDOP) and `cost threshold for parallelism` in SQL Server. While many suggest these settings are straightforward—like just setting MAXDOP to the number of cores per NUMA node or cost threshold to any value higher than five—I argue that it’s not always as simple. I explore how different core configurations can affect query performance, especially with varying numbers of sockets and cores. Through practical examples on my laptop, I demonstrate the impact of these settings on query execution times and thread usage, revealing why tuning queries and indexes remains crucial for optimal performance.

Full Transcript

Howdy folks, Erik Darling here with Erik Darling Data. And now that I’ve got stickers to prove it, I think it’s a real company. I think it’s like a thing now. It’s not just like some imaginary website. It’s got like physical goods attached to it. So watch out! And today I’m here to ask an annoying question. And it’s a question, I think it’s annoying because to a lot of people it seems like pretty settled advice, at least to, you know, pretty casual observers of SQL Server, just like, you know, like kind of like aware of best practice. And like, yeah, just do this and this and you’re cool. And that question is, is setting maxed up and cost threshold for parallelism really as easy as it seems?

And I don’t think that it always is. And the reason I think that is because when you take the advice from maxed up, it’s like if you have one socket, you can sometimes leave it alone. But if you have like, you know, eight, 12, 16, but holy God, like you have like a weird VM with like a whole bunch of cores in one socket, then the advice to not set maxed up there kind of makes less sense because then you still have one query that can use a whole bunch of cores in that socket. Right. And you have more than one socket. People like, OK, well, just set it to, you know, the number of cores in a single Numa node up to eight.

And you can just kind of leave it alone after that. It’s fine. And for cost threshold for parallelism, the advice is to start at any number higher than five, like 20, 50, 75, just some number that’s for the love of God, not five. And then you can tweak from there kind of based on like the how you see queries running. Now you can maybe raise it up a little, pull it down a little bit. That’s up to you. That’s a very personal thing.

But that’s one of those things that kind of makes setting it not as easy as the the line where you want to draw. OK, I want queries this expensive to go parallel. It’s going to be way different from environment to environment. But that device is probably usually mostly fine.

But where you can kind of get screwed, and this is true for any environment, you know, if all your queries and indexes are just so horribly untuned that they all have this astronomical cost, then like setting cost threshold for parallelism for like 5000 might not even get you all that much. Like you might just have these terrible queries that just run with like a very high cost constantly.

And they’re just going to go parallel, like almost up to this crazy point. Like unless you set max stop don’t wonder, set cost threshold for parallelism to like the maximum value. You just might never get rid of those queries going parallel.

And if all your queries are just like big, huge joins across lots and lots of tables, max stop is going to be really hard to set too. Now, of course, these things do get more complicated, like even beyond that, if you start looking at like, you know, if you have a bunch of users connecting, and like they’re all doing really like doing things of really, really high concurrency, or if you have like an AG or mirroring with like a bunch of like thousands of databases or something in it, and they’re all synchronizing data because that takes up a bunch of worker threads too.

But let’s get back to just sort of a basic thing at the query level. Now on my laptop, I have eight cores visible to SQL Server, which means I have 576 worker threads. You can see that number right there. 576.

Right? So I like at any point I can run like 576 serial queries, probably, or like, you know, some divisor of parallel queries depending on how many threads they get. Now over in this window, I’ve got some interesting stuff going on.

I ran a bunch of really big queries, right? Like joining a whole bunch of tables together. And I’ve run them at different max stops. I got a max stop two.

I got a max stop four. I got a max stop six. I got a max stop eight down here, which is the highest I can go up to with it before SQL is just like, okay, you’re drunk. We can’t use more dots than we have. Right?

That’d be silly. Now, here’s where setting max stop gets weird. If we start looking at the timing differences between these queries, this one here at max stop two, that takes about 120 seconds. If we scroll down to the one that runs at max stop four, we’re down to about 42 seconds, which is a big jump from max stop two to four, right?

120 to 42. That’s a huge jump. If I were as an end user, I’d be like, sweet.

Good job, query tuning. Thanks for that. We go to max stop six, we get down to 31 seconds. And if we get to, when we get down to max stop eight, we’re at about 30 seconds. So not a big difference between six and eight, but you know, just a little, little difference.

And this is why it’s tricky. So at some point you have to sacrifice the number of queries that you want to run simultaneously for how fast you want them to run. So right now with the way my laptop is set up with the 576 threads, I could run 18 queries that use 32 threads.

I could run 24 queries that use 24 threads. I could run 36 queries that use 16 threads. And I can run 17 queries that use eight threads.

And now you’re thinking, well, max stop two, you only use two threads. And that’s why you’re wrong. You’re so wrong. And I’m going to show you why you’re wrong. Now in the actual execution plans for all of these, there’s a helpful little doodad.

If you hit F4 and you look at the select operator and you scroll down a little bit, you come to this part of the properties called thread stat. And thread stat is going to give you some interesting information. So for this one query, I had four branches in this query.

That means there were four branches that SQL Server said, oh, you could run at the exact same time. So that’s four branches that can run in parallel concurrently. And for each of those branches, I got two threads.

So I two times four is eight. Right. And you can see that I reserved eight threads and I used eight threads for this one query at max stop two. So we see that max stop doesn’t limit the number of threads that you get.

Max stop limits the number of threads per concurrent parallel branch that you get. I know, right? Crappy.

If we scroll down to the max stop four query and we look at that same thing. Now for our four branches, we got four threads per branch and now we’re reserving 16 threads. So this is where we just made a jump from being able to run 32, 72 queries simultaneously to being able to run 36 queries simultaneously.

And that’s a pretty big jump because that’s like, like half. And you can probably imagine that if we scroll down to the max stop six query, our brand, our thread usage is going to go up to 24. So now if we have 24 of these, I can only run 24 of them.

I can have 24 instances of this query running at once before I start running out of worker threads to use. And I know because you’re good at math. You’re better at math than I am.

If we scroll down to this last query and we look at the thread stat usage for four branches, we’re now up to using 32 threads. I wasn’t just making those numbers up. I was serious.

So what happens after you have 18 instances of this query running at once? Let’s say that, you know, 18 users log in and when they log in, this query runs to give them some information. What happens when user 19 logs in?

You do not have any more worker threads to give to user 19. And user 19 has to wait for all of these other queries to finish before it can take, it says, I would like to reserve 32 threads, please. And SQL Server says, well, you have to wait for those 32.

You can use less if it’s emergency. Well, you could wait for 32. So that’s what’s called thread pool. And that’s when SQL Server does plum runs out of worker threads to give to new queries.

And that’s why I think that when you’re going to, you know, make changes to settings like maxed up and cost resh over parallelism, the starting point advice is better than the defaults, 100% better than the defaults. But at some point when you need to tune a highly concurrent workload, you need to look at things a little bit more closely.

You need to say, okay, well, you know, when queries go big and crazy and they start running, well, you know, we get lots of thread pool weights because we have lots of queries that are trying to reserve lots of threads and go. And so that’s when, you know, making those settings changes is like lowering maxed up or raising cost threshold can be beneficial. But at the same time, you’re looking at possibly regressing query performance, right?

If you change maxed up from six to four or four to two, there’s some pretty big changes in how long those queries ran for. Users might not be so happy with you. So it’s a very, very careful thing you have to balance.

And at some point, query and index tuning does have to come into the picture. Anyway, that’s just a quick video because I was bored on a, I don’t know what day it is. I think my vitamin K said it was Thursday.

So I’m going to guess it’s Thursday. I might be wrong though. Anyway, I’m Erik Darling again with Erik Darling data. You can, I don’t know if you, if you’re watching this, you can probably figure out where to find me.

Anyway, thank you for, thank you for watching. I hope you learned something. Hope you had some fun and I will see you next time. Hopefully.

I hope I get to record more videos. If I don’t, I’ll be pretty sad. All right. Computer don’t fail me now. Thanks 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.