What is the purpose for using OPTION MAXDOP 1 in SQL Server

Question

I have never clearly understood the usage of MAXDOP  I do know that it makes the query faster and that it is the last item that I can use for Query Optimization  However  my question is  when and where it is best suited to use in a query

User · Answer

Adding my two cents  based on a performance issue I observed  If simple queries are getting parellelized unnecessarily  it can bring more problems than solving one  However  before adding MAXDOP into the query as  quot knee-jerk quot  fix  there are some server settings to check  In Jeremiah Peschka - Five SQL Server Settings to Change  MAXDOP and  quot COST THRESHOLD FOR PARALLELISM quot   CTFP  are mentioned as important settings to check  Note  Paul White mentioned max server memory aslo as a setting to check  in a response to Performance problem after migration from SQL Server 2005 to 2012  A good kb article to read is Using large amounts of memory can result in an inefficient plan in SQL Server Jonathan Kehayias - Tuning    cost threshold for parallelism    from the Plan Cache helps to find out good value for CTFP  Why is cost threshold for parallelism ignored  Aaron Bertrand - Six reasons you should be nervous about parallelism has a discussion about some scenario where MAXDOP is the solution  Parallelism-Inhibiting Components are mentioned in Paul White - Forcing a Parallel Query Execution Plan

User · Answer

There are a couple of parallization bugs in SQL server with abnormal input  OPTION MAXDOP 1  will sidestep them   EDIT  Old  My testing was done largely on SQL 2005  Most of these seem to not exist anymore  but every once in awhile we question the assumption when SQL 2014 does something dumb and we go back to the old way and it works  We never managed to demonstrate that it wasn t just a bad plan generation on more recent cases though since SQL server can be relied on to get the old way right in newer versions  Since all cases were IO bound queries MAXDOP 1 doesn t hurt

User · Answer

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

User · Answer

There are a couple of parallization bugs in SQL server with abnormal input  OPTION MAXDOP 1  will sidestep them   EDIT  Old  My testing was done largely on SQL 2005  Most of these seem to not exist anymore  but every once in awhile we question the assumption when SQL 2014 does something dumb and we go back to the old way and it works  We never managed to demonstrate that it wasn t just a bad plan generation on more recent cases though since SQL server can be relied on to get the old way right in newer versions  Since all cases were IO bound queries MAXDOP 1 doesn t hurt

User · Answer

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

User · Answer

As something of an aside  MAXDOP can apparently be used as a workaround to a potentially nasty bug   Returned identity values not always correct

[sql] What is the purpose for using OPTION(MAXDOP 1) in SQL Server?

Examples related to sql

Examples related to sql-server