MDOP

Question

MDOP

Tara-1044200

SSCoach

Points: 15951
More actions
July 24, 2010 at 9:23 am

#139930

I have 8 CPU's by default on all our sql servers and MDOP is set to "0" by default, we have expereicned 100% CPU many times as our is DW environment. Do you think changing the value of MaxDOP will improve performance ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Hi Tara

SQL Server does a good job managing the Max Degree of Parallelism (MDOP=0). MDOP is not a good option to enable in OLTP systems. Large no of parallel queries result in CXPacket waits which causes excessive blocking.

Instead of enabling it at server level, you can use MDOP hint in individual queries to have more control on MDOP.

Thank You,

Best Regards,

SQLBuddy

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

if i want to enable them at query level, does any user has that previalge or only admin. IF all users can uses that but still what wud be the setting at server level, can i still keep it 0

Adiga One Orange Chip Points: 27284 More actions · Answer 3

Any users can use the MAXDOP query hint. If the value specified in the MAXDOP hint can be more than the value in sp_configure.

Please refer MSDN documentation here.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

So I think i can configure MAX DOP to 7 as i have 8 and if required when necessary people can use query hint so that they can use max CPU's, am i right ?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Tara-1044200 (7/25/2010)
So I think i can configure MAX DOP to 7 as i have 8 and if required when necessary people can use query hint so that they can use max CPU's, am i right ?

Server level setting can be overridden at the statement level, yes.

Note that if you have a very good IO system that can keep up with the CPU cores you have (a true rarity in my consulting experience) then DW queries really can eat a box quickly because you won't get the usual CXPACKET waits which cause cores to be idle when they should be working. But dropping from 8 to 7 cores isn't really going to release back much of the box either, espcially if you have multiple queries going at one time. You may want to consider going to 6 or even 4.

Don't forget that if/when you upgrade to SQL 2008+ you could use resource governor if you are on enterprise edition.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

I agree with you but i am cocnered about the perofrmance if i drop to 6 as the other 2 cpu's will be idle unless some one queries using query hint (MaxDOP)

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 7

Tara-1044200 (7/26/2010)
I agree with you but i am cocnered about the perofrmance if i drop to 6 as the other 2 cpu's will be idle unless some one queries using query hint (MaxDOP)

Simply throwing CPU's at a problem is absolutely no guarantee of performance. In fact, for some improperly written queries, parallelism can be quite detrimental. You'll find that performance is gained mostly in the code... not hardware.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)