1

I've learned today, if you append to your query

OPTION (MAXDOP 0)

your query will run on multiple processors and if it's huge query, query will perform faster.

I know general guidelines on query optimizations (using indexes, selecting only needed fields etc.), my question is about SQL Server optimization. Maybe changing some options in configurations or anything else.

What guidelines are there for SQL Server Optimization?

Thank you.

P.S. I suppose, this is not the right place to ask server related questions. Should I delete it or maybe it can be migrated to serverfault?

0

1 Answer 1

2

I've learned today, if

Suggestion: READ THE MANUAL, at least once. The whole "option" thing is well documented, and you may be surprised how much else you can learn.

your query will run on multiple processors and if it's huge query, query will perform faster

No, this is not what MAXDOP does. Under NORMAL circumstances it does absolutely NOTHING. Get that - you learned hogwash ;)

Here we go:

The MaxDOP option defines the MAXIMUM degree of parallelism. It does not say "use more processors", it says "go maximum parallelism X" and if X = 0 that is the number of processors.

Here is the catch, though - there is a system wide setting for MaxDOP that already says 0, so under normal circumstances it does nothing in the way you want it.

What it is usefull for is to limit the max peralellism FURTHER for queries where it makes no sense (because no, it does not automagically make a query run faster - it can actually make it run a lot slower). In those cases specific uewries may like an Option (maxdop 1) to be llimited mor ethan the default setting does. You can read more on it at http://msdn.microsoft.com/en-us/library/ms181007.aspx

That being said, MaxDOP is a tricky bgeast to master - in 99% of the cases it makes zero sense to put a maxdop into a query.

What guidelines are there for SQL Server Optimization?

There are none. Standard SQL principles apply (only ask for what you need, have proper indices etc.). The rest are rarely to be used options - so generic principles do not apply (as they are rare - generic guidelin: do not use them).

The guidelines are, btw., called the documentation.

Like always (your post is a very good example) limited knowledge pretty often is one thing: totally wrong (as you did not even know what MaxDop 0 actually does).

3
  • On our server maxdop is set to 1, so for huge queries if I put that option to 0, it will use as much as it can, so the query will perform faster. Isn't that so?
    – hgulyan
    Jun 12, 2010 at 9:37
  • Generally, you're certainly right, I must read documentation. maxdop was just an example. I wanted to learn some principles, guidelines, but the best guideline is reading manuals and documentations. THat's very very true. Thank you.
    – hgulyan
    Jun 12, 2010 at 9:43
  • Not necessarily. More Processors do not help if the query is IO bound. In general, SQL Server is good at deciding that itself - this option is more for the cases where the system goes parallel, but the parallelism overhad kills query performance, for example because of IO overload. PÜaralellism CAN be a LOT slower if decided in error - and this is what the hint is for. Stopping it from being used. Server should in general NOT be set to 1, but to the proposed value per documentation (depends on processor).
    – TomTom
    Jun 12, 2010 at 11:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .