NASTY RUMORS ABOUT MAXDOP

Filed under: Internals and Architecture, Tuning and Optimization — KKline at 11:53 am on Tuesday, April 15, 2008

You’d think an article called “NASTY RUMORS ABOUT MAXDOP” would have something to do with Britney Spears or maybe Robert Downy Jr, but in that case it’d be total fiction (at least, it would be coming from my pen).

 

So, I was en route to the 2008 Microsoft MVP Summit yesterday and I had a chance to catch up on my reading.  You may have heard some rumors that you should only ever set MAXDOP (maximum degrees of parallelism) to an even number.  I can neither confirm nor deny these rumors since I haven’t had time to test this yet myself.  But I’m curious if any readers out there have definitive information one way or the other.

 

Evidently, there are two roles involved in parallel processing, a writer and a consumer.  Naturally, when two roles are at work, the MAXDOP setting doesn’t have to share resources when it is set in increments of two.

 

Hope this helps,

 

-Kevin

2 Comments »

Comment by Mario

April 17, 2008 @ 11:23 am

Hmm. Why would that be? If these rumours don’t come with clear reasons we should maybe just ignore them. Just like ‘rules of thumb’ and ‘best practices’ without proper explanation :-)

BTW you say: ‘two roles: writer and consumer’. Do you maybe mean reader and consumer? Or were you thinking about loading something in parallel, not a parallel Select?
I think the whole idea about PQ is not to overlap in resources but to process different resources in parallel. If you simply have an odd number of CPUs you get a odd maxdop anyway I think. There could be other valid reasons to have an odd number for maxdop. odd numbers of files or partitions to scan maybe?

Comment by KKline

April 23, 2008 @ 7:44 pm

Hi Mario,

You’ll note that my post is asking for feedback on whether this is true or not. It is not promoting this as a truism that everyone must stick to.

As for the two roles, with parallelism the query must be split out (a writer role) and then the results must be merged back together (a consumer role). Again, this rumor doesn’t state that an odd number for MAXDOP doesn’t work, only that it’s not as efficient as an even number.

Please refer to my entry on http://sqlblog.com where Joe Chang has done some extensive testing in response to my post.

Thanks,

-Kevin

RSS feed for comments on this post. TrackBack URI

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>