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.
There are a couple of separate things going on here, which it is important to separate:
What is the effective maximum degree of parallelism available to a query?
The contributors to this are (broadly in order of importance):
- Resource Governor
- Query hint
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:
Will a query plan use parallelism?
The SQL Server query optimizer always finds a serial plan first*.
- 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.
- 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.
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.
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.
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.
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