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

  • Article

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