[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.
- MaxDOPSetToOne: Max
Degree of Parallelism set to 1 at query or server level - NoParallelDynamicCursor: Dynamic cursor doesn’t support parallel plan
- NoParallelFastForwardCursor: Fast Forward cursor doesn’t support parallel
plan - NoParallelCreateIndexInNonEnterpriseEdition: We don’t’ support parallel index operations
for non Enterprise editions - NoParallelPlansInDesktopOrExpressEdition: No parallel plan for express edition (SQL 2000 desktop edition is the same as
express edition for later builds) - TSQLUserDefinedFunctionsNotParallelizable: Scalar TSQL user defined function used in the
query - 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. - 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