Query Processing and Execution - SQL Server 2012 Internals and Troubleshooting - page 127

The PhysicalOp column of the following statistics proi le output indicates that both joins are now
hash joins:
PhysicalOp
NULL
Parallelism
Hash Match
Parallelism
Hash Match
Parallelism
Index Scan
Parallelism
Clustered Index Scan
Parallelism
Index Scan
Using a query hint can cause both compile-time and runtime issues. The compile-time issues are likely
to happen when SQL Server is unable to create a plan due to the query hint. Runtime issues are
likely to occur when the data has changed enough that the Query Optimizer needs to create a new
plan using a different join strategy but it is locked into using the joins dei ned in the query hint.
MAXDOP n
The MAXDOP query hint is only applicable on systems and SQL Server editions for which parallel
plans are possible. On single-core systems, multiprocessor systems where CPU afi nity has been set
to a single processor core, or systems that don't support parallel plans (i.e. if you are running the
express edition of SQL Server which can only utilize a single processor core), this query hint has no
effect.
On systems where parallel plans are possible, and in the case of a query where a parallel plan is
being generated, using MAXDOP ( n ) allows the Query Optimizer to use only n workers.
On very large SMPs or NUMA systems, where the SQL Server coni guration setting for
Max Degree of Parallelism is set to a number less than the total available CPUs, this option
can be useful if you want to override the systemwide Max Degree of Parallelism setting for a
specii c query.
A good example of this might be a 16 core SMP server with an application database that needs to
service a large number of concurrent users, all running potentially parallel plans. To minimize the
impact of any one query, the SQL Server coni guration setting Max Degree of Parallelism is set
to 4, but some activities have a higher “priority” and you want to allow them to use all CPUs. An
example of this might be an operational activity such as an index rebuild, when you don't want to
use an online operation and you want the index to be created as quickly as possible. In this case, the
specii c queries for index creation/rebuilding can use the MAXDOP 16 query hint, which allows SQL
Server to create a plan that uses all 16 cores.
OPTIMIZE FOR
Because of the extensive use of plan parameterization, and the way that the Query Optimizer sniffs
for parameters on each execution of a parameterized plan, SQL Server doesn't always do the best
job of choosing the right plan for a specii c set of parameters.