Written by: Cindy Gross
Friday, March 21, 2008 9:37:08 AM

Facts about MAXDOP:

• MAX DOP is a per query step setting, it does NOT limit the number of CPUs SQL Server will use.
• MAXDOP query hint overrides sp_configure if hint is lower #.
• Each query step will use either 1 CPU or MAXDOP (edited 7/22/08 - this is true for 2000, but in 2005 SQL Server can use any number of schedulers between 1 and MAXDOP)

MAXDOP should generally be no more than:

• Eight for an average OLTP system
• The number of CPUs assigned to a NUMA node
• The number of physical processors

More details can be found at:

• 329204 General guidelines to use to configure the MAXDOP option http://support.microsoft.com/default.aspx?scid=kb;EN-US;329204
• 837983 You may receive error message 8650 when you run a query that uses intra-query parallelism http://support.microsoft.com/default.aspx?scid=kb;EN-US;837983 

In an OLTP environment it may be best to set MAXDOP to 1 at the instance level.
Using multiple CPUs per query is only beneficial when there are fewer queries running than CPUs and those queries are CPU intensive.
To set this at the query level add the hint: OPTION (MAXDOP 1). Warning: avoid hints whenever possible!

Copyright ©2008 Cindy Gross

Trackback Print

Location: Blogs Troubleshooting and Tips - Cindy Gross