You may see cases when you have an expensive-enough query that is not parallelized. There are several SQL Server features that inhibit a parallel plan creating a serial plan instead:
- Scalar-valued user-defined functions.
- CLR user-defined functions with data access.
- Miscellaneous built-in functions such as OBJECT_ID(), ERROR_NUMBER(), and @@TRANCOUNT.
- Dynamic cursors.
In a similar way, there are some other features that force a serial zone within a parallel plan, which can lead to performance problems. These features include the following:
- Multi-statement, table-valued, user-defined functions.
- TOP clause.
- Global scalar aggregates.
- Sequence functions.
- Multi-consumer spool.
- Backward scans.
- System table scans.
- Recursive queries.
For example, the following code shows how the first parallel example in this section turns into a serial plan while using a simple user-defined function:
NonParallelPlanReason optional attribute of the QueryPlan element contains a high-level description of why a parallel plan may not be chosen for the optimized query, which in this case is CouldNotGenerateValidParallelPlan, as shown in the next XML plan fragment:
<QueryPlan … NonParallelPlanReason="CouldNotGenerateValidParallelPlan" … >
Finally, there is an undocumented and therefore unsupported trace flag that you could try to force a parallel plan. Trace flag 8649 can be used to set the cost overhead of parallelism to 0, encouraging a parallel plan, which could help in some cases (mostly cost related). Just for demonstration purposes, see the following example using a small table:
The previous query creates a serial plan with cost 0.429621. Using trace flag 8649, as shown next, will create a parallel plan with the slightly lower cost of 0.386606 units: