Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 387

How it works...
The SQL Server execution engine uses a plan that is generated during query parsing and the
optimization phase for that query. By default, the MAXDOP value is 0 , which means the query
engine uses all the processor cores to parallelize a query depending upon the number of CPU
cores available on that server.
Initially, in our case, we have set a server-wide maximum degree of parallelism to eight on a 12
CPU core system. Then, we have also chosen to use the MAXDOP query hint in a query by deciding
a value of one, and the SQL Server query execution engine uses the explained route here. If a
query explicitly uses a query hint of MAXDOP value that is greater than zero then the optimizer
overrides the MAXDOP value configured for server-wide through the sp_configure statement.
Further, we remembered about Resource Governor (RG) where a similar operation can be
accomplished. It is essential to highlight how MAXDOP and RG work inter-dependently. Let
us assume that SQL Server instance resources are managed using the Resource Governor
feature, and if a specified value in the MAXDOP query hint is lesser than the RG workload
group's degree of parallelism limit value then the optimizer overrides the RG workload group
setting. In case a server-wide MAXDOP setting is used along with RG enabled then the RG
workload group degree of parallelism limit value is used by default.
The general conception of RG and MAXDOP is different from each other, and the MAXDOP
option— OPTION(MAXDOP) —can be used as a query hint. However, this hint will override
the default setting of MAXDOP that has been set up using the SP_CONFIGURE statement.
Similarly, from SQL Server 2008 onwards, when MAXDOP exceeds the value that was
configured by the RG, the database engine will use the RG MAXDOP value. This means the
MAXDOP restricts the number of CPUs to be used within a query execution; however, the
Resource Governor will not restrict the usage of the number of available physical and logical
CPUs on the server.
There's more...
Further, if the server consist of 32 logical processors then by default the MAXDOP value will
be 32 - unless a different value is explicitly specified using sp_configure statement. Also,
if a server consists of 256 logical processors then MAXDOP uses the value as 64 unless a
different value is specified server-wide.
The maximum value for the degree of parallelism setting is
controlled by the edition of SQL Server, CPU type, and the
operating system. If a value greater than the number of available
processors is specified, the actual number of available processors
is used. If the server has only one CPU, MAXDOP value is ignored.
Refer to the recommendations and guidelines for the max degree of parallelism option from
http://support.microsoft.com/default.aspx?scid=kb;en-US;2023536 .