SQL Server 2022 Degree of Parallelism Feedback Is Wack
Video Summary
In this video, I dive into my thoughts on a feature in SQL Server 2020-22 that I find personally disappointing: the degree of parallelism feedback. I explain why I believe it’s not very useful for most users and discuss how it only tests downgrades to DOP 2, which is often unnecessary given that many servers have their DOP configured correctly. Additionally, I suggest a better approach would be to allow SQL Server to explore parallel plans for queries that do not break the cost threshold for parallelism setting, helping to mitigate risks associated with adjusting this setting. The video covers four main reasons why I find this feature lacking and concludes by recommending improvements or alternative settings that could make query optimization more effective.
Full Transcript
Erik Darling here with Darling Data, and I’m taking a little break from my recording about various community tools and my little bit about series, which, I don’t know, I was going to call that Tip Jar SQL because it’s sort of like small little tidbits about things, but got lazy. Just sort of forgot to go down that path. Anyway, I’m here to talk a little bit about, I think, a really disappointing feature in SQL Server 2020-22, and that is the degree of parallelism feedback. I’m going to talk about the four reasons why I find it personally disappointing, and then I’m going to talk about some things that I think would make better additions to the intelligent query processing set of features that have the intelligent query, and that have the intelligent query, and that have permeated SQL Server since 2016 or so.
It’s a really interesting feature that have permeated SQL Server since 2016 or so. Maybe 2017, who knows? One of those years.
Sometimes it’s hard to tell the difference between 2016 and 2017 in retrospect. So, the first of the things that I dislike about the feature is that it only tests the data, and it’s not just the data, but it’s not just the data, but it’s not just the data. Where you have datatrue that knows that folder has its protection for its operational assessment that has done which changes when the database can IRS Liaultにな place.
Debenstrom app has five, seven views as the data, and all of those areas are connected to that. So, you know, that’s useless at best for most people. Most people have DOP moderately correctly configured on their server, and testing minor downgrades to that is quite a waste of SQL Server’s time and of your money.
You’re paying $7,000 a core for SQL Server to figure out if a query can run at the same speed at a lower DOP by like 2 or 4 or something. It only tests downgrades to DOP 2, which means that you will have at best a minorly parallel plan. SQL Server will not test if going down to a single-threaded plan would not detract from query performance at all.
So that’s kind of silly. And I’ve been given, I think, some reasoning on that, and that would require a recompile, which is just nonsense because when SQL Server comes under sufficient CPU pressure, you will see a parallel plan, but the query itself will only be running at DOP 1.
I have plenty of demos that show that. They do not require a recompile. You still see a parallel plan for it, but behind the scenes, a query only gets one CPU thread. Its DOP is indeed one.
It doesn’t test DOP upgrades, meaning that if you have, let’s just to make numbers easy because I am not particularly good at math. Let’s say that you have a query running at DOP 4 that’s hitting a 100 million row table. Let’s go to 100 million rows.
Let’s really scale this thing up. You have four threads suffering with 25 million rows apiece. It does stand to reason, at least in my experience with query parallelism, that if we were to alleviate some of the stress from those four cores and maybe bump it up to eight cores, and we would have roughly 12.5 million rows per thread, each of those threads would be under far less stress and strain.
We could probably have a more efficient query, but degree of parallelism feedback will not test that for you. You don’t get that. You get the same dumb DOP 4 plan without any hope of a parallel upgrade, which is pretty goofy.
Want my honest feedback? That’s just plum goofy. It also won’t test upgrades from a serial plan to a parallel plan. That doesn’t happen.
Probably gets it more malarkey about needing to recompile. But we all know that’s not particularly true. Yeah.
And so, like, if you have queries that are running for, I don’t know, 5, 10, 20, 30 seconds, and there is nothing specifically inhibiting them from going parallel, then why not test and upgrade to a higher DOP to bring the duration of those queries down?
Since this is something that is not permanent, it is impermanent, as some might say, why not just test it and see if there’s any dramatic decrease in overall query duration at higher DOPs, like, say, 4 or 8?
8’s a pretty good number for most DOPs, as long as you have the CPU cores to back it up. So, that’s a pretty disappointing thing there. Now, this feature really feels like there was no adult in the room at the time when it was decided on.
This feels like developer pizza, where, you know, they decided that they wanted to, you know, pick out, like, the grossest pizza, and you’re like teenagers sitting there with a menu being like, ew, tuna fish and gravy or something, like, just like picking out intentionally gross pizza toppings.
This is just not a very good spec for this thing. I can’t imagine that there was very good telemetry involved in deciding on this one. It just kind of feels like a what if.
Like, developers get one wish per release, and this was the wish. Now, for a very, very long time, SQL Server has had the cost threshold for parallelism setting, and this setting has led many, many people to think that query cost is a very important metric.
And I guess it is in the, you know, because it does, you know, if a query costs under your cost threshold for parallelism setting, and it would be reasonable for that query to go parallel, but it does not get considered for parallelism because it does not have a high enough cost to go over your cost threshold for parallelism setting, then, you know, you could have some issues.
But quite far more often, I find that, you know, servers running at the default cost threshold for parallelism setting have far more problems than folks who have raised it up and might have a few straggler queries that could use some help. Now, way back when, the cost threshold for parallelism setting is, or rather, meant seconds on one guy’s computer named Nick in, like, 1997 or something.
That’s the, at least that’s the folklore around it. And there are still people I talked to today who think that cost threshold for parallelism means seconds, which is kind of weird. But I think that, well, two things.
One, a nitpick is that, you know, the cost threshold for parallelism setting should be part of the SQL Server installer. MacStop is in there, has been in there for a few releases now. You know, and I think it would be a generally useful thing since most sane and rational people, after they install SQL Server, will set cost threshold for parallelism away from the default.
Whatever they set it to is better than the default unless they set it lower for some reason. That would be a weird choice. You know, most folks I know will start at 50 and then adjust as necessary from there, assuming that 50 is not a successful change for them, or not a completely successful change for them.
I think that a much better addition to SQL Server than degree of parallelism feedback, which only focuses on downgrading parallelism, would be a setting that would allow you to allow SQL Server to explore parallel plans for queries that do not break the cost threshold for parallelism setting, and look at their overall duration, perhaps their weights as well, if that’s the kind of thing that you care about, and figure out if a parallel plan would be better.
It would also be good if we could test parallelism at higher DOPs up to some upper limit, you know, whether that’s configurable or that’s just based on, you know, some internal math, and look at the number of cores and all that good stuff.
Cores and sockets and things, things that people really think hard about when they’re setting maxed up and all that, figuring out NUMA, getting crazy in there. But I think a much better setting would be, you know, to figure out if, figure out what threshold you would want to explore a parallel plan for a query that was deemed not expensive enough by the optimizer to consider a parallel plan for, because that would take a lot of the risk out of adjusting cost threshold for parallelism.
You know, just to throw a number out there, let’s say that you change cost threshold for parallelism to 50, and there’s some really important query that has an estimated subtree cost of 49.9 query bucks, does not quite break that threshold, and you have a regression to a parallel plan.
Well, this would take a lot of the sting out of that, because if that thing ran for, let’s just say, 10 seconds, the optimized, you know, the SQL servers internals looked at that and said, oh, 10 seconds?
Well, that doesn’t seem very, that’s not very fast. Let’s try .4. Okay, you know, now let’s just say that it, like the parallelism scaled linearly, and now the whole thing runs in like 2.5 seconds.
Well, that was, that was a successful introduction of parallelism to a query. I realize that there is inherent risk with introducing parallel queries or upgrading parallelism. Downgrading parallelism does not, does not have, you know, the whole issue of, you know, parallel queries and worker threads and all that stuff, you know, there is risk in there.
But I think that is a risk that most workloads do not have. And the fact that this setting is one that you can turn on and off is one that you can use to mitigate those risks a bit for anyone out there who has a workload where they are at risk of that.
But I think most people who have workloads where they are at severe risk of that are at least fairly well managed as far as DBA staff and all that stuff goes. So anyway, those are my thoughts on the degree of parallelism setting.
It’s not a terribly good feature. I’ve seen the demos for it. It’s not very interesting.
DOP goes down, doesn’t go up. And that’s about it. I don’t know. Maybe this will be one of those things that just no one ever turns on and it dies on the vine and that would probably be the best thing for it unless significant improvements are made or other settings are introduced to allow you to better control parallelism on the server, specifically going from a serial plan to a parallel plan where it would be more efficient to do that.
Anyway, that’s it. Thank you for watching. You may not have learned anything, but hopefully you enjoyed yourselves anyway.
I’m going to go eat lunch now, I think. Brush my teeth after that. Make my dentist happy.
I don’t know. Maybe then I’ll go for a walk. We’ll see what happens. Anyway, thank you for watching. Remember, like, subscribe if you’re into that sort of thing, if you’re that kind of YouTube fanatic and also that kind of SQL Server fanatic.
And I will see you in another video that will be back to more of the regularly scheduled content. Anyway, thank you again for watching. Thank you.
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.
One thought on “SQL Server 2022 Degree of Parallelism Feedback Is Wack”
Comments are closed.