OPTION(MAXDOP 1) in SQL Server

Question

I have never clearly understood the usage of MAXDOP. I know it makes the query faster and thats the last that I can use in Query Optimization. But when and where it is best suited..

Thanks Guys!!!

Answer 1

This is a general rambling on Parallelism in SQL Server, it might not answer your question directly.

From Books Online, on MAXDOP:

Sets the maximum number of processors the query processor can use to execute a single index statement. Fewer processors may be used depending on the current system workload.

See Rickie Lee's blog on parallelism and CXPACKET wait type. It's quite interesting.

Generally, in an OLTP database, my opinion is that if a query is so costly it needs to be executed on several processors, the query needs to be re-written into something more efficient.

Why you get better results adding MAXDOP(1)? Hard to tell without the actual execution plans, but it might be so simple as that the execution plan is totally different that without the OPTION, for instance using a different index (or more likely) JOINing differently, using MERGE or HASH joins.

Answer 2

As Kaboing mentioned, MAXDOP(n) actually controls the number of CPU cores that are being used in the query processor.

On a completely idle system, SQL Server will attempt to pull the tables into memory as quickly as possible and join between them in memory. It could be that, in your case, it's best to do this with a single CPU. This might have the same effect as using OPTION (FORCE ORDER) which forces the query optimizer to use the order of joins that you have specified. IN some cases, I have seen OPTION (FORCE PLAN) reduce a query from 26 seconds to 1 second of execution time.

Books Online goes on to say that possible values for MAXDOP are:

0 - Uses the actual number of available CPUs depending on the current system workload. This is the default value and recommended setting. 1 - Suppresses parallel plan generation. The operation will be executed serially. 2-64 - Limits the number of processors to the specified value. Fewer processors may be used depending on the current workload. If a value larger than the number of available CPUs is specified, the actual number of available CPUs is used.

I'm not sure what the best usage of MAXDOP is, however I would take a guess and say that if you have a table with 8 partitions on it, you would want to specify MAXDOP(8) due to I/O limitations, but I could be wrong.

Here are a few quick links I found about MAXDOP:

Books Online: Degree of Parallelism

General guidelines to use to configure the MAXDOP option

Answer 3

There are a couple of parallization bugs in SQL server with abnormal input. OPTION(MAXDOP 1) will sidestep them.

Answer 4

As something of an aside, MAXDOP can apparently be used as a workaround to a potentially nasty bug:

Returned identity values not always correct