Resource Governor and MAXDOP, Parallel plan generation

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.

Serial plan snippet

Parallel plan snippet

Bob Dorr - Principal SQL Server Escalation Engineer