sqlskills-logo-2015-white.png

Workload Group MAX_DOP and the Faux Parallel Plan

Let’s say you’ve “disabled” parallelism on your SQL Server instance via the ‘max degree of parallelism’ setting as follows:

EXEC sp_configure‘max degree of parallelism’, 1

RECONFIGURE

Now most folks know (or are finding out) that this doesn’t really prevent parallel plans if you throw in a MAXDOP hint.  For example, let’s take the following query:

EXEC sp_executesql

       N’SELECT charge_no FROM dbo.charge

       WHERE charge_dt = @charge_dt OPTION (MAXDOP 4)’,

       N’@charge_dt datetime’, 

       @charge_dt = ‘1999-07-20 10:49:11.833’;

If we look at the actual plan and properties, even though I’ve capped the instance level max degree of parallelism, I get a parallel plan (using SQL Sentry Plan Explorer):

clip_image002

clip_image004

Now let’s say I want to out-smart anyone capping the MAXDOP by using Resource Governor and the MAX_DOP workload group setting:

CREATE WORKLOAD GROUP wgReportUsers

WITH

(

     MAX_DOP = 1

) USING [rpReportUsers]

GO

I also created a classifier function and a resource pool (assuming I want to restrict anything else).  I won’t add that setup code here since it’s just the standard RG configuration process.  For this example I’ve configured nothing unusual with the exception of setting MAX_DOP = 1 and making sure my reporting user gets classified to the constrained workload group.

Now let’s say the user connects in after RG is configured.  I am able to confirm this via Profiler through the PreConnect:Completed event class:

clip_image006

In this example, GroupId 257 maps to my workload group.

So I’m logged in as the report user, and I’ve given that user the appropriate read-permissions and also SHOWPLAN so we can see that serial plan we’re expecting.  I also cleared the cache so that the new execution would be a new compilation.

But what do we see when the user tries to override the MAXDOP?

EXEC sp_executesql

       N’SELECT charge_no FROM dbo.charge

       WHERE charge_dt = @charge_dt OPTION (MAXDOP 4)’,

       N’@charge_dt datetime’, 

       @charge_dt = ‘1999-07-20 10:49:11.833’;

clip_image008

And you’ll see the same in SQL Server Management Studio (in case you were wondering):

clip_image010

So what about the rows per thread that I showed you earlier?  How does it look now?  This time, there are no separate columns by thread:

clip_image012

Not there.  And while there are parallel iterators, there rows per thread is not showing.

Seen another way, let’s look at the properties of the Clustered Index Scan and associated XML Showplan RunTimeCountersPerThread values by thread for the original, unconstrained execution:

clip_image014

<RunTimeCountersPerThread Thread=“3” ActualRows=“2” ActualEndOfScans=“1” ActualExecutions=“1” />

<RunTimeCountersPerThread Thread=“2” ActualRows=“2” ActualEndOfScans=“1” ActualExecutions=“1” />

<RunTimeCountersPerThread Thread=“4” ActualRows=“6” ActualEndOfScans=“1” ActualExecutions=“1” />

<RunTimeCountersPerThread Thread=“1” ActualRows=“6” ActualEndOfScans=“1” ActualExecutions=“1” />

<RunTimeCountersPerThread Thread=“0” ActualRows=“0” ActualEndOfScans=“0” ActualExecutions=“0” />

And this is what the MAXDOP 1 workload group constrained request looks like in the properties in contrast:

clip_image016

<RunTimeCountersPerThread Thread=“0” ActualRows=“16” ActualEndOfScans=“1” ActualExecutions=“1” />

For the constrained plan, I can also see that the DegreeOfParallelism is 0 for the QueryPlan element, but not for the RelOps:

<QueryPlan DegreeOfParallelism=“0” CachedPlanSize=“16” CompileTime=“1” CompileCPU=“1” CompileMemory=“120”>

<RelOp AvgRowSize=“19” EstimateCPU=“0.880078” EstimateIO=“6.8935” EstimateRebinds=“0” EstimateRewinds=“0”

EstimateRows=“14.8802” LogicalOp=“Clustered Index Scan” NodeId=“1” Parallel=“true”

PhysicalOp=“Clustered Index Scan” EstimatedTotalSubtreeCost=“7.77357” TableCardinality=“1600000”>

What’s more, while the graphical plans look identical and has identical estimated costs (I didn’t mention this earlier – but both plans had an estimated cost of 8.18609).

So the request did indeed honor the max degree of parallel setting for the workload group in the end – it just didn’t remove the parallelism related iterator and properties.

2 thoughts on “Workload Group MAX_DOP and the Faux Parallel Plan

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.