Why SQL Server May Not Parallelize a Query

By:


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:

p0157-01

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:

p0158-01

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:

p0158-02

Leave a Reply