MAXDOP- Help Required

[Diya1225]

Hi,
Please, could someone help me by explaining the concept of MAXDOP in sql server 2008.

Thanks,
Diya

 

[SQLBill]

MAXDOP lets SQL Server multi-thread a query over more than one processor. Let's say you have a script that contains two select statements. One selects from tableA and the other from tableB. You could run each one separately, but these are in one script. So SQL Server sees them and multi-threads it so one select is running on one processor and one is running on another. That way they can run quicker. However, let's say the second script needs the results from the first. Now the second has to pause until the first one finishes, but it (the second) is now stopping the seoond processor from being used. This is how time-outs, deadlocks, etc can happen so setting the maximum number of processors to be used by one query can be a little bit of a science. It can also be controlled by query by adding this to the end of the query:

OPTION (MAXDOP 1)

the number is the amount of available processors you want the query to be allowed to use.

Disclaimer: I hope I got that explanation correct.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[mrdenny]

Not quite. MAXDOP tells the SQL server how many threads to use for a single query at the most. Each query that is run has an execution plan created for it. By default if an operator has a CPU cost of 5 or higher the SQL server will multithread the query to give the query more resources. The MAXDOP statement tells the SQL server what the max number of threads is. By default the max number is the number of cores that the instances has been allowed to use as configured throughout the affinity mask setting.

Using a MAXDOP of 1 removes all the calculTions from the mix as there is no point in doing the calculations when the highest value you can use is 1.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog