MAXDOP Query Hint

The MAXDOP query hint is used to implicitly specify the maximum degree of parallelism to use for a specific statement.  The degree of parallelism is roughly defined as how many separate processors to utilize for a single query.  To set this, use the following convention:

[cc lang=”sql”]
SELECT *
FROM Sales.SalesOrderDetail
OPTION (MAXDOP 1)
[/cc]

This will force only one instance of the SPID to be spawned. To allow more instances of a SPID to be spawned replace the 1 with the maximum number.

This needs to be specified after each statement. It is not specific to the statement.

As with all query hints, it is recommended that you know internally what you are controlling by specifying this hint. While this specific hint is relatively harmless if you specify “1” it is still recommended you understand it’s effects.

5 comments
Ratnamala 29 Mar 2016 at 8:11 am
NIkita 29 Mar 2016 at 8:09 am

Really helpful..

nidhi 29 Mar 2016 at 8:06 am

excellent block

Buddy 29 Aug 2015 at 8:01 am

You’ve really imeessprd me with that answer!

Suzanne 08 May 2013 at 6:06 pm

Hey Deetro,

I just used your article to check the DOP hint we are using for a “Select” insert bug in SQL Server. The Scope_Identity has occasionally been getting the wrong identity value in one of our SPs – we finally realized it was a bug in SQL Server and it doesn’t look like our version of SQL Server – (2008 Express edition) – has the fix that is available for 2008R2 in one of the SP1 cumulative patches. Thanks!

Featured Articles

 Site Author

  • Thanks for visiting!
css.php