Share via


[SQL 2012 query plan enhancement] I want to know why my query is not parallelized

In the past, we have got repeated questions from customers on why a particular query is not parallelized.   We didn’t have a good way to let customer know the reason until SQL 2012.

Starting SQL Server 2012, XML showplan is enhanced to include the reason why the plan is not or cannot be parallelized.

When you open showplan XML, you will see an attribute called  “NonParallelPlanReason” under QueryPlan element.  See the example below.

     
<Statements>

      
<StmtSimple StatementText="select
* from sys.objects option (maxdop 1)" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.107922" StatementEstRows="2201" StatementOptmLevel="FULL" QueryHash="0xC34FFA269AC9A844" QueryPlanHash="0x39C2C734F752156C" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">

         
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

         
<QueryPlan NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="96" CompileTime="6" CompileCPU="6" CompileMemory="824">

  

 

I will pick out a few most common ones.  Most of them are self-explanatory.

  1. MaxDOPSetToOne:   Max
    Degree of Parallelism set to 1 at query or server level
  2. NoParallelDynamicCursor:  Dynamic cursor doesn’t support parallel plan
  3. NoParallelFastForwardCursor:  Fast Forward cursor doesn’t support parallel
    plan
  4. NoParallelCreateIndexInNonEnterpriseEdition:   We don’t’ support parallel index operations
    for non Enterprise editions
  5. NoParallelPlansInDesktopOrExpressEdition:  No parallel plan for express edition  (SQL 2000 desktop edition is the same as
    express edition for later builds)
  6. TSQLUserDefinedFunctionsNotParallelizable:  Scalar TSQL user defined function used in the
    query
  7. CLRUserDefinedFunctionRequiresDataAccess:  If a CLR user defined function ends up access
    data via context connection, the query can’t be parallelized.  But a CLR user defined function that doesn’t
    do data access via context connection can be parallelized.
  8. NoParallelForMemoryOptimizedTables:  This is for any query accessing memory
    optimized tables (part of SQL 2014 in-memory OLTP feature)

We will blog more about 2012 XML plan enhancements in the future.  Stay tuned. 

 

 

Jack Li | Senior Escalation Engineer | Microsoft SQL Server Support

Comments

  • Anonymous
    August 29, 2013
    Thank you! Each piece of information about query plans is appreciated very much! Consulting a binary, gives more non parallel reasons and though much of them are self descriptive it will be cool if you elaborate a little bit more on them. If it is possible of course. EstimatedDOPIsOne NoParallelPlansDuringUpgrade ParallelismDisabledByTraceFlag NoRemoteParallelismForMatrix NoParallelCursorFetchByBookmark NonParallelizableIntrinsicFunction UpdatingWritebackVariable TableVariableTransactionsDoNotSupportParallelNestedTransaction DMLQueryReturnsOutputToClient MixedSerialAndParallelOnlineIndexBuildNotSupported CouldNotGenerateValidParallelPlan NoParallelForCloudDBReplication NoParallelForPDWCompilation Thank you!

  • Anonymous
    August 29, 2013
    One more question, even more interesting and important for understanding, from my point of view. Please explain, when this property is added to the plan? Consider the following example: <code> use tempdb; create table t1(a int); set statistics xml on select *,(select 1) from t1 select *,(select 1) from t1 option(maxdop 1) set statistics xml off drop table t1; </code> In the first query - there is no attribute NonParallelPlanReason, but in the second there is. Why not to add it to every non parallel plan? Thank you.

  • Anonymous
    September 04, 2013
    Any hints please on where to look further when I see CouldNotGenerateValidParallelPlan as the NonParallelPlanReason Ken