THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

NASTY RUMORS ABOUT MAXDOP

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

Published Tuesday, April 15, 2008 1:48 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

I have seen plenty examples where they have set it to 1

Is 1 an even number  :-)

April 15, 2008 1:58 PM
 

KKline said:

The setting 1 turns off parallelization.  The BOL says "Suppresses parallel plan generation. The operation will be executed serially."

Even so, you can certainly set MAXDOP to an odd number like 3 or 5.  However, the rumor is that it does not perform as well when set to an odd number.

I'm hoping that someone can do a test and tell us all if this bogus, true, or part-true.

April 15, 2008 3:06 PM
 

jchang said:

this is a 2 x Xeon E5430 (quad core 2.66GHz)

24GB memory,

Windows Server 2008, SQL 2008 Feb 08 CTP 32-bit

CPU and time to run the 22 TPC-H queries in milli-sec

for the second run, ie, all data in memory, some tempdb activity

CPU Dur

DOP 1 506,519 521,837

DOP 2 429,826 233,002

DOP 3 447,162 164,048

DOP 4 443,498 126,309

DOP 5 461,700 106,154

DOP 6 474,525 93,883

DOP 7 487,316 81,246

DOP 8 482,945 71,141

on big systems, I have always tested 1, 2, 4, 8, 16, 32 & 64 CPUs because I don't have time to do the off numbers before the big box is put into production, or the customer is tired of paying my hourly rate for such infor

April 16, 2008 6:22 PM
 

jchang said:

same for SQL 2005 sp2

CPU Dur

DOP 1 470,562 488,147

DOP 2 397,489 214,126

DOP 3 418,737 154,524

DOP 4 404,916 122,397

DOP 5 417,864 103,803

DOP 6 429,469 92,684

DOP 7 438,160 84,302

DOP 8 442,155 73,631

same for SQL 2000 sp4 + hf 2187

CPU Dur

DOP 1 511,948 544,520

DOP 2 547,157 285,508

DOP 3 570,946 220,855

DOP 4 612,977 191,639

DOP 5 618,670 184,473

DOP 6 661,789 178,591

DOP 7 688,495 170,236

DOP 8 727,634 166,110

interpreting parallel execution performance characteristics is not a simple matter, and it is very easy for a less experienced person to draw completely wrong conclusions

April 17, 2008 8:37 AM
 

KKline said:

Awesome stuff, Joe.

It looks like in both test runs that the even numbered DOP settings generally outperformed the odd number ones.  That is, 2 was better than 3, 4 was better than 5, 6 was better than 7, and so on.

Did I misinterprete your results?

Thanks for doing the test.

-Kevin

April 21, 2008 1:29 PM
 

Michael B said:

Here's something I'm running into when trying a query using MAXDOP, on SQL Server 2005 SP2: I get different results if I use "OPTION (MAXDOP 1)" and "OPTION (maxdop 1)", and that's on a case-insensitive server.

Also, it appears that BCP ignores it, since I'll have all processors hit 100%, even when using "OPTION (MAXDOP 1)" within a query (using queryout)

February 18, 2009 5:59 PM
 

Robert Carnegie said:

Looks like adding an odd-numbered processor to the task adds more to the overhead of cycles than adding the next even-numbered processor does, depending on the system - it isn't a visible pattern with SQL Server 2000.  It still gets the job done faster, but by using processors which maybe you should let other users share, and partly using them to process an overhead cost of parallelism.

I also feel:

- Most everyday queries aren't parallelised

- You shouldn't hog the whole server, but I already said that

- I've seen ugly queries with awful performance become livable-with with MAXDOP 1, which is probably something stupid about query optimisation.  I am not permitted to shoot developers or database sub-designers on business premises and it's too much trouble to follow them home.

- There are I don't know how many "bug fixed" in SQL service packs that refer specifically to parallel execution, so there's probably an invisible army of "bug not fixed" that are disarmed by disabling parallelism

August 10, 2009 9:43 AM
 

KKline said:

Hi Robert,

My observations are generally similar to yours.  Good points!  THanks,

-Kev

August 10, 2009 9:49 AM
 

Joshua said:

I'm about to put in an OPTION (MAXDOP 1) to limit the CPU drawn by a particular background job.

October 14, 2009 7:13 PM
 

KKline said:

Hi Joshua,  Most applications will do at least the same and possibly better when you set MAXDOP to 1.  Note that if you use the OPTION clause, it only affects the SQL statement it is associated with.  You'll have to set it at the server level using SP_Configure or at the database level using Alter Database.

Best regards,

-Kev

October 15, 2009 2:04 PM
 

Dragan Babovic said:

If hyper-threading is enabled then obviously even numbers will perform better. Otherwise it shouldn't matter. On a busy OLTP server I recommend setting MAXDOP to 1 for the server and use OPTION (MAXDOP n) in queries sparingly.  

June 9, 2010 4:35 PM
 

RHillman said:

I wasn't aware that you could assign any mdop or MDOP settings for an individual database...only for certain operations on tables, indexes,etc. That would be very cool. Could you post a reference?

I've seen mixed results with the 'max degree of parallelism' (mdop) and the MAXDOP query option.

In one example (8 core host w/ only 32Gb RAM) we saw that overall server performance was significantly better with 'max degree of parallelism' set to 1. No CXPACKET waits after the change. SharePoint was the great offender in this environment.

However, some specific queries ran much faster with MAXDOP 0. The quintessential example query was running consistently at 12s elapsed and 12s CPU (as reported by SET STATISTICS TIME ON) but with MAXDOP 0 the reported times were 3s and 12s respectively.

MS recommends that you never utilize more than 8 cores for either mdop or MDOP but I don't think it's enforced at all.

June 30, 2011 3:16 PM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement