First published on MSDN on Aug 28, 2013
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.
I will pick out a few most common ones. Most of them are self-explanatory.
Degree of Parallelism set to 1 at query or server level
: Dynamic cursor doesn’t support parallel plan
: Fast Forward cursor doesn’t support parallel
: We don’t’ support parallel index operations
for non Enterprise editions
: No parallel plan for express edition (SQL 2000 desktop edition is the same as
express edition for later builds)
: Scalar TSQL user defined function used in the
: 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.
: 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