MAXDOP

Question

Post reply

MAXDOP

Chinna-703814

SSC Enthusiast

Points: 133
More actions
May 6, 2010 at 2:53 am

#234664

COuld some one please tell me what is MAXDOp amd how do we set it for sql server 2005

Viewing 14 posts - 1 through 13 (of 13 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

refer this link

http://qa.sqlservercentral.com/articles/Configuring/managingmaxdegreeofparallelism/1029/

http://support.microsoft.com/kb/329204

http://technet.microsoft.com/en-us/library/ms181007(SQL.90).aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

In addition to the links provided and the information there, just remember, setting MAXDOP is all well and good, but you need to worry more about the parallelism threshold. The default value of 5 is, in most system, way too low. By default, on OLTP systems, I'll change it to 25 and then make adjustments up or down from there depending on the behavior of the system.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Bhuvnesh

SSC Guru

Points: 59351

May 6, 2010 at 7:49 am

#1163211

Chinna-703814 (5/6/2010)

how do we set it for sql server 2005

and more important why we need to set it up ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Leo.Miller SSChampion Points: 13035 More actions · Answer 4

MAX Degree of Parallelism. Or the number of processors SQL will assign to a query. Opinion varies on what it should be set to, but on SQL2K and SQL2K5 OLTP servers that SHOULD all have short queries and where you can't afford to have one query hog all your CPUs I would set it to 1.

This way no one query can consume all the CPUs and block other processors.

Leo

Yes!

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Bhuvnesh (5/6/2010)

Chinna-703814 (5/6/2010)

how do we set it for sql server 2005

and more important why we need to set it up ?

As well as what Leo said, sometimes the costs associated with parallelism are greater than the benefit. That's why you'll usually see people recommend MAXDOP 1 and not MAXDOP 2.

--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)
Intro to Tally Tables and Functions

Leo.Miller SSChampion Points: 13035 More actions · Answer 6

Bhuvnesh (5/6/2010)

Chinna-703814 (5/6/2010)

how do we set it for sql server 2005

and more important why we need to set it up ?

In SQL 2005, Managment Studio

Right Click the server -> Server properties

select Advanced in the left pane

Set Max Degree of Parallelism in the right pane, near the bottom.

Select OK

This is dynamic, so you don't need a restart but beware, all the buffers are cleared and reloaded when you do this.

Leo

Yes!

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Leo.Miller SSChampion Points: 13035 More actions · Answer 7

Jeff Moden (5/6/2010)

Bhuvnesh (5/6/2010)

Chinna-703814 (5/6/2010)

how do we set it for sql server 2005

and more important why we need to set it up ?

As well as what Leo said, sometimes the costs associated with parallelism are greater than the benefit. That's why you'll usually see people recommend MAXDOP 1 and not MAXDOP 2.

To elaborate; Sometimes the cost of working out the parallel query plan costs more than a non-parallel plan would have cost. With MAXDOP = 1 SQL doesn't even consider a parallel plan, potentially saving time.

Leo

Yes!

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

Everyone turning off parallelism is ignoring the fact that some queries can benefit. I strongly recommend you explore increasing the cost threshold of parallelism before you simply switch it off. This, even on OLTP systems. For that matter, even on reporting systems.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

Grant Fritchey (5/7/2010)

Everyone turning off parallelism is ignoring the fact that some queries can benefit. I strongly recommend you explore increasing the cost threshold of parallelism before you simply switch it off. This, even on OLTP systems. For that matter, even on reporting systems.

I actually agree with that.. a lot. Except when parallelism would possibly cause code to work incorrectly (Quirky Update comes to mind as an example), my recommendation is... "Forget about parallelism being on or off... write better code." 😀

--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)
Intro to Tally Tables and Functions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 10

Jeff Moden (5/7/2010)

Grant Fritchey (5/7/2010)

Everyone turning off parallelism is ignoring the fact that some queries can benefit. I strongly recommend you explore increasing the cost threshold of parallelism before you simply switch it off. This, even on OLTP systems. For that matter, even on reporting systems.

I actually agree with that.. a lot. Except when parallelism would possibly cause code to work incorrectly (Quirky Update comes to mind as an example), my recommendation is... "Forget about parallelism being on or off... write better code." 😀

Not that I ever argue with you, but I'm sure not going to argue with that statement.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Chinna-703814 SSC Enthusiast Points: 133 More actions · Answer 11

Is it fine to just set the MAXDOP as the number of processors?

is it mandatory to use the MAXDOP query hint?

does the 2 options combinations have any impact?

as i say

we have a server with 4 processrors

our configuratios are set to MAXDOP 4 as an idle case

one of the legacy query(written 3yrs back) has MAXDOP set to 2

does this have any impact on the performance?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 12

You'll get a much better response on new questions if you post them to the group at large. The only respondants you'll see here are those that are already subscribing to the question.

However:

Chinna-703814 (5/9/2010)

Is it fine to just set the MAXDOP as the number of processors?

Yes, you can do that. Or you can leave it set to zero, which is the same thing. It'll use the CPU's that are available. Basically, there's no one right answer here. It depends on your system and the load on it.

is it mandatory to use the MAXDOP query hint?

Not only is not mandatory, but I actively discourage using any query hint if you can at all avoid them (and the vast majority of the time, you can).

does the 2 options combinations have any impact?

as i say

we have a server with 4 processrors

our configuratios are set to MAXDOP 4 as an idle case

No, but it's silly redundancy that is completely unnecessary and if you run the query on a system that has 2 or 8 CPU's you'll be ignored or shoot yourself in the foot.

one of the legacy query(written 3yrs back) has MAXDOP set to 2

does this have any impact on the performance?

It depends. If that query can benefit from four cpu's, then yes, it hurts performance. But there's no way to know just from that statement.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

rharderwijk Ten Centuries Points: 1119 More actions · Answer 13

rharderwijk

Ten Centuries

Points: 1119

May 11, 2010 at 3:45 am

#1165421

Besides the pros and cons to setting the MAXDOP setting manually, there is one other factor coming into play: the application. An application may not allow you to have any other value for MAXDOP but 1. BizTalk Server (2006 R2) in one example.

RH