MAXDOP setting 1

[Eyespi20]

Two questions, regarding setting the maxdop on a select query, what is the correct syntax for the hint and what is the best way to determine the number to set it at?

Thanks in advance.

Margaret

 

[jbenson001]

Syntax example:
OPTION(MAXDOP 1)

The number specifies the number of processers you want to use in parallel.

You can find more info in BOL. Look up OPTIONS clause in the index. Then you can click on "max degree of parallelism Option" to get more info

 

[vongrunt]

> what is the correct syntax for the hint

Simple:

SELECT|INSERT|UPDATE|DELETE query
... from/where/order by stuff as usual
OPTION(MAXDOP n)

> what is the best way to determine the number to set it at?

Unless you have quad hyperthreading server box cooled with liquid helium - leave it as is .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Eyespi20]

Thanks for the information. I had already looked at BOL, but it still did not give me the syntax I was looking for(and neither did you, really), but I have found what I needed elsewhere.

For anyone else with a similar question, the syntax for maxdop applying to a select statement is:

 select 'whatever columns you want' 
              into 
        'wherever you want the data to be' 
              from 
        'xtable' join 'ytable' on xtable.id = ytable.id
option (maxdop 1)

It was WHERE the option(maxdop 1) went in the query that I needed to know. BOL kind of makes it look like it goes at the beginning of the select statement, not at the end.

The number to set maxdop at is determined by the number of CPUs (max of 32). You set it at 1 (the default is 0, by the way) to suppress parallel plan generation. Setting the number to greater than one limits the number of servers used by the query .


Hope this helps others.

Margaret

 

[Eyespi20]

Thanks, Vongrunt for your explanation. While I don't have the hardware you spec'ed, I do need to set that option on a query or two that is hitting on one of my largest tables and pulling masses of data. Would not dream of resetting the whole system

That was helpful.

Margaret

 

[SQLDenis]

>>Unless you have quad hyperthreading server box cooled with liquid helium - leave it as is .

I am missing the liquid helium I have the quad hyperthreading server boxes

I want the liquid helium too :-(

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[vongrunt]

Overclocker!

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)