January 3, 2020 at 10:00 pm
I recently experimented with MAXDOP using a pretty huge statement. Here is a summary of the results:
MAXDOP = 0. CPU = 36790 ms, elapsed = 12838 ms.
MAXDOP = 1. CPU = 20375 ms, elapsed = 21003 ms.
MAXDOP = 2. CPU = 22578 ms, elapsed = 11646 ms.
MAXDOP = 3. CPU = 22890 ms, elapsed = 7919 ms.
MAXDOP = 4. CPU = 22923 ms, elapsed = 6140 ms.
MAXDOP = 5. CPU = 29902 ms, elapsed = 9889 ms.
MAXDOP = 6. CPU = 30058 ms, elapsed = 9620 ms.
MAXDOP = 7. CPU = 31692 ms, elapsed = 10179 ms.
MAXDOP = 8. CPU = 33151 ms, elapsed = 10973 ms.
...
MAXDOP = 16. CPU = 35240 ms, elapsed = 11752 ms.
My computer has 8 cores/16 threads and my SQL Server is configured to a maximum of 16 processors. The numbers I got clearly demonstrate that letting SQL Server decide how many processors to use does not lead to the best results (here, the sweet spot is reached by using 3 or 4 processors; CPU time is low and elapsed time is very low).
Does this mean that it is a good practice, at least for some statements, to use the MAXDOP options? Any comments or suggestions?
Thanks.
January 3, 2020 at 11:02 pm
its not as easy as that - and maxdop should always be set and not left at zero or 1 unless for a particular server that has been tested and proven as being the best value.
just one example from Kendra here of good guidelines - MS and many others do point out the same - but always with the caveat that what is good on one system may not be the optimal on another - and even for workloads on same server it may be better to have one setting during "work hours" and another one for "batch processing"
taking your example - and assuming that other settings/memory isn't affecting the processing, it is possible that for another similar heavy query on your server that the best "max dop" is 8. Does this mean that one is wrong and the other is right? maybe both are wrong - if both queries run just once or twice a month and for the majority of the other queries on the server maybe the best maxdop is 6.
so what is the correct value? it is the one that after testing your system throughout all its queries and over a period of time gives the best result for all workloads - and taking in consideration the example of work hours/batch processing I mentioned above.
And potentially particular queries may need to have a hint added to optimize them - but these should be the exceptions, not the rule.
January 4, 2020 at 1:05 am
I recently experimented with MAXDOP using a pretty huge statement. Here is a summary of the results:
MAXDOP = 0. CPU = 36790 ms, elapsed = 12838 ms.
MAXDOP = 1. CPU = 20375 ms, elapsed = 21003 ms.
MAXDOP = 2. CPU = 22578 ms, elapsed = 11646 ms.
MAXDOP = 3. CPU = 22890 ms, elapsed = 7919 ms.
MAXDOP = 4. CPU = 22923 ms, elapsed = 6140 ms.
MAXDOP = 5. CPU = 29902 ms, elapsed = 9889 ms.
MAXDOP = 6. CPU = 30058 ms, elapsed = 9620 ms.
MAXDOP = 7. CPU = 31692 ms, elapsed = 10179 ms.
MAXDOP = 8. CPU = 33151 ms, elapsed = 10973 ms.
...
MAXDOP = 16. CPU = 35240 ms, elapsed = 11752 ms.
My computer has 8 cores/16 threads and my SQL Server is configured to a maximum of 16 processors. The numbers I got clearly demonstrate that letting SQL Server decide how many processors to use does not lead to the best results (here, the sweet spot is reached by using 3 or 4 processors; CPU time is low and elapsed time is very low).
Does this mean that it is a good practice, at least for some statements, to use the MAXDOP options? Any comments or suggestions?
Thanks.
What is the Threshold of Parallelism set at on your SQL Server instance where you did this test? Also, do you have code that others can use to do the same experiment?
And, I agree with Frederico on the idea of ALWAYS setting MAXDOP at the server level. Without knowing the workload for a server, my rule of thumb is to set it to 1/4 of the number of hyperthreads available with a hard max of MAXDOP = 8. If there are less than 8 hyperthreads, I set it to 2.
The "Cost Threshold of Parallelism" (CTP) is more important to me than MAXDOP, though. If you set that incorrectly, the differences you see in your results in your original post above can pale in comparison to what you'll see with the wrong CTP. The thing that CTP has in common with MAXDOP is that "It Depends" on workload and a whole lot more including the hardware you're using.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply