Controlling MAXDOP of executing queries

Question: I’m having trouble figuring out how to control the execution characteristics of the workloads on my server. I have a mixed workload where some queries support an OLTP application, some support reporting, and some are ad hoc queries from users. How can I control the MAXDOP of one set without affecting the others?

Answer: The most obvious answer and the one I’m sure you’ve tried is to set the sp_configure server-wide MAXDOP setting. This limits the degree of parallelism that parallel-capable query plans can be executed with (and note that this is just a maximum, it’s not a guaranteed value - how far a query parallelizes is determined at query execution time)

You can work around this for index rebuild operations by using the MAXDOP option on ALTER INDEX, but there’s no such option for consistency checks.

As far as queries are concerned, you can override the server-wide MAXDOP setting by using the OPTION (MAXDOP X) query hint, where X is the MAXDOP you’d like that query to run with. Contrary to popular misconception, *anyone* can specify this query hint and override the server setting.

The problem with that solution is that you have to hint *everything* that you don’t want to be limited by the server-wide setting, or use 0 (i.e. unlimited) for the server-wide setting and hint everything that you *do* want to be limited. Neither is very palatable when you have you have hundreds or thousands of queries and SPs.

Your savior on Enterprise Edition is Resource Governor in SQL Server 2008. With this you can define different buckets (called workload groups) that connections are dropped into when they connect (or a connection is reset when using connection pooling). It’s designed to be able to apportion CPU and query execution memory between workloads, but you can ignore all that and just make use of it’s MAXDOP capabilities.

Each workload group can have a different MAXDOP setting (called MAX_DOP) and this MAXDOP setting overrides the server-wide setting and any query hints.

With this functionality, you can set the server-wide MAXDOP setting and then group connections together by their workload characteristics and set a MAXDOP for each workload easily without having to hint each query/proc individually.

Check out Books Online for more details on resource governor.