Share via

Resource Governor and MAXDOP, Parallel plan generation

  • Article

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

clip_image001

Parallel plan snippet

clip_image002

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

  • Anonymous
    December 20, 2011
    Great observation and explanation ! I just have one doubt, when you say "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". Does that empties entite query cache all plans are gone) ? Varun Dhawan | Database Architect

  • Anonymous
    January 08, 2015
    Great article, the new SQL 2014 resource governor also comes with the ability to set a threshold for I/O, check details here sqlturbo.com/sql-server-2014-new-features-resource-governor-for-io