CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

Rate This
28 Aug 2013 6:42 AM
  • Comments 3

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

 

 

Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post
Comments
  • 29 Aug 2013 11:10 PM

    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!

  • 30 Aug 2013 1:59 AM

    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.

  • Ken
    5 Sep 2013 7:32 AM

    Any hints please on where to look further when I see CouldNotGenerateValidParallelPlan as the NonParallelPlanReason

    Ken

Page 1 of 1 (3 items)
Locations of visitors to this page
Recent Posts