Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Eyespi20 (TechnicalUser) (OP)
13 Oct 05 13:13
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 (Programmer)
13 Oct 05 13:32
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
Helpful Member!  vongrunt (Programmer)
13 Oct 05 13:33
> 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 (TechnicalUser) (OP)
13 Oct 05 13:44
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:

CODE

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 (TechnicalUser) (OP)
13 Oct 05 13:48
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 (Programmer)
13 Oct 05 13:50
>>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 (Programmer)
13 Oct 05 14:05
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)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close