SQL Server 2012 introduces a “NonParallelPlanReason” attribute in the QueryPlan element in a query execution plan.  This attribute is not officially documented as of this writing, and so I was curious which scenarios this new functionality covered.

Take the following example query which runs in parallel on my test SQL Server instance:

SELECT     p.ProductLine,
SUM(f.SalesAmount) AS TotalSalesAmount
FROM [dbo].[FactInternetSales] AS f
INNER JOIN [dbo].[DimProduct] AS p ON
f.ProductKey = p.ProductKey
GROUP BY p.ProductLine
ORDER BY p.ProductLine;

A few details on my starting state:

  • I increased the number of rows in FactInternetSales to 123,695,104 rows.
  • Max degree of parallelism is “0”
  • Cost threshold for parallelism is “5”
  • Resource governor is not enabled beyond the default values
  • The table has a columnstore index on it (unrelated to the current post, but more an FYI)
  • My plan runs in parallel when unconstrained
  • The total cost of the aforementioned query is 57.0931

Within the query execution plan, NonParallelPlanReason does not show up when the query runs in parallel:

<QueryPlan DegreeOfParallelism=”8″ MemoryGrant=”52808″ CachedPlanSize=”64″ CompileTime=”10″ CompileCPU=”10″ CompileMemory=”424″>

What happens if I add OPTION (MAXDOP 1) to the query?   I see the following in the actual plan:

<QueryPlan DegreeOfParallelism=”0″ NonParallelPlanReason=”MaxDOPSetToOne” MemoryGrant=”1024″ CachedPlanSize=”32″ CompileTime=”7″ CompileCPU=”7″ CompileMemory=”384″>

If I remove the hint and use EXEC sp_configure ‘max degree of parallelism’, 1, I see the same attribute value:

<QueryPlan DegreeOfParallelism=”0″ NonParallelPlanReason=”MaxDOPSetToOne” MemoryGrant=”1024″ CachedPlanSize=”32″ CompileTime=”7″ CompileCPU=”7″ CompileMemory=”384″>

Next I was curious what would happen if I set max degree of parallelism back to 0 and bumped up the cost threshold for parallelism to a higher value, causing the query to execute with a serial plan? This time there was no NonParallelPlanReason attribute and a DegreeOfParallelism=”1”:

<QueryPlan DegreeOfParallelism=”1″ MemoryGrant=”1024″ CachedPlanSize=”24″ CompileTime=”6″ CompileCPU=”6″ CompileMemory=”384″>

What about setting processor affinity to just one core?  This time we see a new NonParallelPlanReason:

<QueryPlan DegreeOfParallelism=”0″ NonParallelPlanReason=”EstimatedDOPIsOne” MemoryGrant=”1024″ CachedPlanSize=”24″ CompileTime=”8″ CompileCPU=”8″ CompileMemory=”384″>

I’m sure there are other scenarios to explore and if you’ve seen other examples of values for NonParallelPlanReason, please post them in the comments.