SQL Server Optimization – MAXDOP, Query Hints, and Cost Threshold for Parallelism

maxdopoptimizationparallelismsql serversql-server-2012

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually go parallel?

I haven’t been able to dig up this specific information although this link suggests that CTFP is ignored if MAXDOP is 1. This makes sense without query hints as no request, regardless of cost, will go parallel when MAXDOP is 1.

Can anyone let me know what the expected behaviour of these two requests will be?

Example 1:

Instance Maxdop: 1 
CTFP: 50 
Query hint: Maxdop=2 
Query cost: 30

Example 2:

Instance Maxdop: 1
CTFP: 50
Query hint: Maxdop=2
Query cost: 70

Best Answer

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually go parallel?

Simple answer: yes.

Details

There are a couple of separate things going on here, which it is important to separate:

  1. What is the effective maximum degree of parallelism available to a query?

    The contributors to this are (broadly in order of importance):

    • Resource Governor MAX_DOP setting
    • Query hint MAXDOP setting
    • The max degree of parallelism instance configuration option

    The details are explained in Server’s “Max Degree of Parallelism” setting, Resource Governor’s MAX_DOP and query hint MAXDOP–which one should SQL Server use? by Jack Li, Senior Escalation Engineer for Microsoft SQL Server Customer Service and Support. The table below is reproduced from that link:

    parallelism table

  2. Will a query plan use parallelism?

    The SQL Server query optimizer always finds a serial plan first*.

    Then, if:

    • Further optimization is justified; and
    • The cost of the best serial plan exceeds the cost threshold for parallelism configuration value


    ...the optimizer will try to find a parallel plan.

    Then, if:

    • A parallel plan is found (i.e. is possible); and
    • The cost of the parallel plan is less than the best serial plan


    ...a parallel plan will be produced.

Note: the cost threshold for parallelism only affects whether the optimizer looks for a parallel plan. Once a parallel plan is cached, it will execute using parallelism when it is reused (so long as threads are available) regardless of the CTFP setting.

Examples

For both examples, with instance maxdop 1 and query hint maxdop 2, the effective available DOP is 2. If a parallel plan is chosen, it will use DOP 2.

Example 1

Given CTFP of 50 and a cheapest serial plan found cost of 30, SQL Server will not try to find a parallel plan. A serial plan will be produced.

Example 2

Given CTFP of 50 and a cheapest serial plan found cost of 70, SQL Server will try to find a parallel plan. If this plan (if found) has a cost less than 70 (the serial plan cost) then a parallel plan will be produced.

The end result of query optimization is always a single cached plan: serial or parallel. The optimizer finds only a serial plan in search0 (TP) and search1 (QP) phases.

It may then (as described) re-run search1 with a requirement to produce a parallel plan. A choice is then made between serial and parallel based on best whole plan cost so far. That choice is binding in case optimization moves on to search2 (Full Optimization). Each phase of optimization considers many alternatives, but the output from a stage is always a single best plan, which is either serial or parallel.

I wrote about some of this in Myth: SQL Server Caches a Serial Plan with every Parallel Plan

Related Question