January 27, 2017 at 8:12 am
Hi guys,
I have a question that I have read and heard differing opinions on in the past.
Cost threshold for parallelism - is it worth changing this as a matter of course from the default of 5? I have heard opinions from "If your CPU isn't being thrashed then don't bother", all the way to "Definitely change it, 5 was a default years ago and is no longer relevant!"
In addition, I'm struggling to understand why having a lower value for the option could cause things to slow down, if your CPU isn't under pressure? Surely if the CPU can cope, it's better to have queries go parallel than not in terms of execution times?
January 27, 2017 at 8:22 am
george.p - Friday, January 27, 2017 8:12 AMHi guys,I have a question that I have read and heard differing opinions on in the past.
Cost threshold for parallelism - is it worth changing this as a matter of course from the default of 5? I have heard opinions from "If your CPU isn't being thrashed then don't bother", all the way to "Definitely change it, 5 was a default years ago and is no longer relevant!"
In addition, I'm struggling to understand why having a lower value for the option could cause things to slow down, if your CPU isn't under pressure? Surely if the CPU can cope, it's better to have queries go parallel than not in terms of execution times?
Yes, I'd change it to something between 30 and 50 and monitor from there. The problem with having it set low is that even very small queries get parallelised. That can be just as much of an issue as large queries not being parallelised, because of the extra work that has to be done in splitting the workload between the parallel threads.
John
January 27, 2017 at 8:23 am
John Mitchell-245523 - Friday, January 27, 2017 8:22 AMgeorge.p - Friday, January 27, 2017 8:12 AMHi guys,I have a question that I have read and heard differing opinions on in the past.
Cost threshold for parallelism - is it worth changing this as a matter of course from the default of 5? I have heard opinions from "If your CPU isn't being thrashed then don't bother", all the way to "Definitely change it, 5 was a default years ago and is no longer relevant!"
In addition, I'm struggling to understand why having a lower value for the option could cause things to slow down, if your CPU isn't under pressure? Surely if the CPU can cope, it's better to have queries go parallel than not in terms of execution times?
Yes, I'd change it to something between 30 and 50 and monitor from there. The problem with having it set low is that even very small queries get parallelised. That can be just as much of an issue as large queries not being parallelised, because of the extra work that has to be done in splitting the workload between the parallel threads.
John
Isn't that only the case if the CPU is under pressure? If the CPU can cope wouldn't the extra overhead be a non-issue?
January 27, 2017 at 8:27 am
John
January 27, 2017 at 8:29 am
george.p - Friday, January 27, 2017 8:12 AMHi guys,I have a question that I have read and heard differing opinions on in the past.
Cost threshold for parallelism - is it worth changing this as a matter of course from the default of 5? I have heard opinions from "If your CPU isn't being thrashed then don't bother", all the way to "Definitely change it, 5 was a default years ago and is no longer relevant!"
In addition, I'm struggling to understand why having a lower value for the option could cause things to slow down, if your CPU isn't under pressure? Surely if the CPU can cope, it's better to have queries go parallel than not in terms of execution times?
Worth changing? It depends on your workload; however, that value of "5" is fairly nonsensical nowadays. Once upon a time, it was decided that a query that took over 5 seconds to run on a particular developer's machine would be a candidate for parallelification. That's what the 5 is. That was over 20 years ago, and server power has...increased somewhat since then. Seriously, knock it up to 25, or 50 or something.
The reason for increasing CTfP? To prevent queries going parallel. When a query goes in parallel, there's overhead associated with splitting the workload across multiple cores, and then bringing the result sets back together. If the threads manage to get data that becomes unbalanced, then there could be additional overhead to rebalance the workload.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 27, 2017 at 8:39 am
So are you guys saying that in effect the queries will take longer to run and consume more CPU if they are parallel vs non-parallel? (For instances of small queries)
If the query is tiny anyway, won't the overhead of the parallelism be tiny as well? So small it might barely make a difference? So if I was looking at a server where there were hundreds of thousands of tiny queries, all that overhead could contribute to a major slowdown?
The bit I still don't really understand is, does this matter if your CPU is already coping okay? If so, the additional overhead shouldn't matter overall? If we're saying the bad-side of a low value for the option is that smaller queries go parallel, and that the issue is the additional workload imposed on the CPU, what if I tell you that my CPU is already coping absolutely fine? Where would the performance gains be there?
January 27, 2017 at 8:45 am
george.p - Friday, January 27, 2017 8:39 AMSo are you guys saying that in effect the queries will take longer to run and consume more CPU if they are parallel vs non-parallel? (For instances of small queries)If the query is tiny anyway, won't the overhead of the parallelism be tiny as well? So small it might barely make a difference? So if I was looking at a server where there were hundreds of thousands of tiny queries, all that overhead could contribute to a major slowdown?
The bit I still don't really understand is, does this matter if your CPU is already coping okay? If so, the additional overhead shouldn't matter overall? If we're saying the bad-side of a low value for the option is that smaller queries go parallel, and that the issue is the additional workload imposed on the CPU, what if I tell you that my CPU is already coping absolutely fine? Where would the performance gains be there?
Yes, if there are many small queries running in parallel then the small differences will add up to a large difference. It may not have an effect on the overload on the CPUs, but it will have an effect on the performance of the individual queries.
John
January 28, 2017 at 4:19 pm
george.p - Friday, January 27, 2017 8:39 AMThe bit I still don't really understand is, does this matter if your CPU is already coping okay?
Yes, it does. Change it. The default is a stupidly low value for modern queries and systems.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 30, 2017 at 7:29 am
GilaMonster - Saturday, January 28, 2017 4:19 PMgeorge.p - Friday, January 27, 2017 8:39 AMThe bit I still don't really understand is, does this matter if your CPU is already coping okay?Yes, it does. Change it. The default is a stupidly low value for modern queries and systems.
What effect would I see on a system not under pressure if I changed it? If thing's aren't broken I'd prefer not to change them.
January 30, 2017 at 7:35 am
george.p - Monday, January 30, 2017 7:29 AMWhat effect would I see on a system not under pressure if I changed it? If thing's aren't broken I'd prefer not to change them.
You display a healthy scepticism, sir! The only way you're going to convince yourself is by running a few tests. Find out what queries run most often on your system, and then run them over and over again on a test server that's as similar as possible to your live one. Then adjust maxdop, repeat and see what the effect is.
John
January 30, 2017 at 7:39 am
John Mitchell-245523 - Monday, January 30, 2017 7:35 AMgeorge.p - Monday, January 30, 2017 7:29 AMWhat effect would I see on a system not under pressure if I changed it? If thing's aren't broken I'd prefer not to change them.You display a healthy scepticism, sir! The only way you're going to convince yourself is by running a few tests. Find out what queries run most often on your system, and then run them over and over again on a test server that's as similar as possible to your live one. Then adjust maxdop, repeat and see what the effect is.
John
This may be the case.
I think I'll take a workload using profiler and replay it on the test box to make sure I'm replicating as close as possible. I'll replay the workload twice on the test system, once with the option set to 5, then once at 25/50/some other value. That way I can get a baseline.
Thanks a lot for the idea.
January 30, 2017 at 11:27 am
george.p - Monday, January 30, 2017 7:29 AMGilaMonster - Saturday, January 28, 2017 4:19 PMgeorge.p - Friday, January 27, 2017 8:39 AMThe bit I still don't really understand is, does this matter if your CPU is already coping okay?Yes, it does. Change it. The default is a stupidly low value for modern queries and systems.
What effect would I see on a system not under pressure if I changed it? If thing's aren't broken I'd prefer not to change them.
Simple queries will no longer run in parallel and hence will run faster and use less CPU.
Parallelism has overhead, time and CPU. For expensive queries, the overhead is smaller than the time saving for parallelism, making it desirable, for simple queries it's not, meaning they run for longer in parallel than they would serially.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply