Resource Governor and MAXDOP, Parallel plan generation

Published Jan 15 2019 01:25 PM 347 Views
Microsoft
First published on MSDN on Feb 03, 2011

I had an interesting conversation with some other support engineers and a customer as it relates to the MAXDOP setting in the workload group.

Inquiry: The customer set the MAXDOP=1 for the workload group but when looking at the showplan the parallel operators showed up. They were expecting to force a MAXDOP=1 for the workload group and the login could only use serial plans.   (In the end, SQL Server is using serial plans.)

The plan output is expected as the workload and resource pool settings don’t impact the plan generation.   The resource governor is a RUNTIME application of the values.   The plan is generated for the server and cached so any session can use it.  The MAXDOP is applied at runtime along with the CPU and other resource governor settings.

When you look at the statistics profile output you will see the parallel operators are never executed ( executes column) or capped at the MAXDOP setting for the workload group.

The confusion came when the customer started using OPTION(MAXDOP 1) on the query and the plan output no longer showed the parallel operators.   This is because the sp_configure and OPTION(MAXDOP) options are seen at compile time and the version of the plan can be safely compiled without any parallel operators and inserted into cache properly and later matched.

For example if you change the max degree of parallism setting via sp_configure as soon as you issue the reconfigure action the query cache is emptied.

To illustrate this I ran the query with and without the OPTION(MAXDOP 1) enabled and looked at what sys.dm_exec_quer_stats entries were present after execution.  There are 2 entries present in the DMV, one serial and the other parallel.

Text

sql_handle

Select ….

0x020000000BB099010E10E3238B3D460A445ABB2AB36FC0C4

Select … option(MAXDOP 1)

0x0200000071DFFF340DD0D1036E784C2627259D0A030945BF

Serial plan snippet

Parallel plan snippet

Bob Dorr - Principal SQL Server Escalation Engineer

Version history
Last update:
‎Jan 15 2019 01:25 PM
Updated by: