January 6, 2014 at 5:40 pm
I'm not trying to solve a specific problem, rather, I'm trying to gain a better understanding of the underlying SQL engine.
BOL and many online articles talk about MAXDOP, and various recommended practices. Some articles recommend setting it to the total physical cores, some articles recommend setting it to 1 and using hints to override if necessary. Some articles talk about trying a query both at 1 and at a higher value and seeing which one is better.
Having read these articles, what I'd like to find more about, is WHY lowering a MAXDOP to 1 would improve performance. I ran into this recently, and solved a performance issue by setting the MAXDOP to 1. But I found myself wondering why this would result in an improvement. I'm sure there is overhead in the parallelism of the query, but when the performance benefit can be as much as a 100 times, I realize I don't really know what's going on under the hood.
Can anyone point me in a direction where I can fill in the blanks?
Thanks,
Jeffrey Kretz
January 18, 2014 at 5:44 am
Hello Jeffrey,
this is quite a difficult topic. As you mentioned there are many many many articles about this.
I don't really want to make suggestions but I tell you what I personally have learned:
In general there is no sense in setting MAXDOP = 1 on a server level. Why bying an expensive multicore server when not using it?
The query optimizer does quite a good job. It does too when deciding using parallism or not.
Using lots of query hints to overrule the optimizer is not a good choice. Procedures might get recomiled when statistics are updated and totally new query plans might be created. Using too much query hints limits the optimizer doing his job.
Of course we all know that the optimizer is not always perfekt and we think a query could use a better plan.
And that's were it all leads to: you cannot say it's good or not to use parallelism or not.
You really have to look at any single query and its execution plan to decide if there is a better solution then the optimizer suggests.
As always there are so many szenarios I don't think anyone can cover them all.
I personally visited a "SQL performance tuning" workshop a few weeks ago and learned a lot of new stuff, consider this too.
For your understanding I give you some examples where parallelism or not can influence performance:
1) Queries that alter data in a table variable (insert, update or delete of table variables) are never execute parallelized. If the query does many reads on several tables it could be better running parallelized. In this case you would need to replace the table variable by a temporary table.
2) A query with a table join and a top operator, if the join is realized by a merge operator.
Immagine each table read done by a seperate core, each table will be fully read (or partially using the where-clauses etc.). Then the merge join will be done until the number of top x rows is found.
When using maxdop=1, the query will stop running when there are top x rows found that match the join criteria. This can reduce readings and thus improve performance.
3) I learned that index rebuilds should be done with maxdop 1. If parallelized each core reads and writes some data independently. The result is that the table is still fragmented in the end as each core wrote its data somewhere else maybe.
If rebuilding with maxdop 1 the fragmentation decreases. Reading operations will be faster.
4) In fact, there is a query sometimes that simply has more time spending in handling gathering the parallel processes than it would need if executed withou parallelism. But still there is no rule what queries this might be.
As you see there are many possibilities how parallelism can affect performance. Unfortunately I cannot recommend you anything more than this.
January 31, 2014 at 12:23 am
Hi,
Found a piece of article written by kendra regarding Maxdop, which i think would answer your question.
http://www.brentozar.com/archive/2013/12/q-can-high-maxdop-make-a-query-slower/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply