sqlskills-logo-2015-white.png

SQL Server 2012 Execution Plan’s NonParallelPlanReason

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.

7 thoughts on “SQL Server 2012 Execution Plan’s NonParallelPlanReason”

  1. Curious to see what it claims as NonParallelPlanReason in case of a scalar UDF or a table variable modification…

  2. Great question. For the Table Variable scenario, it generates a "CouldNotGenerateValidParallelPlan" value. Example query below:

    DECLARE @t1 TABLE (ProductLine nchar(2), TotalSalesAmount money);

    INSERT @t1
    SELECT p.ProductLine,
    SUM(f.SalesAmount) 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;

    I’ll test the scalar UDF scenario shortly…

  3. The UDF scenario, like the TV scenario, also generates a "CouldNotGenerateValidParallelPlan". I did the following test in the Credit database:

    CREATE FUNCTION dbo.udf_Useless_Scalar_UDF
    (
    @charge_no int
    )
    RETURNS int
    AS
    BEGIN
    RETURN @charge_no;
    END
    GO

    SELECT charge_no ,
    dbo.udf_Useless_Scalar_UDF(charge_no) AS udf_charge_no
    FROM dbo.charge
    WHERE charge_dt =’1999-07-20 10:49:11.833′;

  4. Found these entries by doing a little digging:

    CLRUserDefinedFunctionRequiresDataAccess
    CouldNotGenerateValidParallelPlan
    DMLQueryReturnsOutputToClient
    EstimatedDOPIsOne
    MaxDOPSetToOne
    MixedSerialAndParallelOnlineIndexBuildNotSupported
    NonParallelizableIntrinsicFunction
    NoParallelCreateIndexInNonEnterpriseEdition
    NoParallelCursorFetchByBookmark
    NoParallelDynamicCursor
    NoParallelFastForwardCursor
    NoParallelForCloudDBReplication
    NoParallelForPDWCompilation
    NoParallelHekatonPlan
    NoParallelPlansDuringUpgrade
    NoParallelPlansInDesktopOrExpressEdition
    NoRemoteParallelismForMatrix
    ParallelismDisabledByTraceFlag
    TableVariableTransactionsDoNotSupportParallelNestedTransaction
    TSQLUserDefinedFunctionsNotParallelizable
    UNUSED
    UpdatingWritebackVariable

    Not sure if that’s all of them.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course