SQL Tips: When MAXDOP is not the same as MAXDOP.

The other day, I ran across the resource governor in SQL server.  It looked great.  I was testing some queries that were abusing parallelism. The queries in question executed as a parellel plan but ended up hanging on a CXPACKET wait type.  (See my previous posts on wait stats).  As such, parallelism was not gaining the benefits.

The queries were able to be re-written to avoid this, but I also investigated some alternative options as well.

First, without doing much modification, it’s easy to eliminate parallelism in an executing query, using the OPTION (MAXDOP 1) clause.  Putting this at the end of the query will eliminate SQL server from using parallelism when it compiles the plan.  This is a good alternative when the query just can’t quite be re-written in a way where SQL server avoids parallelism.

Another way to eliminate parallelism is to use the server wide setting “max degree of parallelism”.  However, since this is a server wide setting, it will eliminate parallelism in most cases.  Applying this server wide seems like overkill as certain queries may perform better with a parallel plan.

The third option I found actually turned out to not work at all. This was to set up a resource pool in resource governor which limited the MAXDOP setting for a specific user.  The user was set up in the database and running all of the queries which were in question.  After doing some preliminary research the option looked promising, but after doing some query tests, the query actually performed worse.  The MAXDOP option in the resource governor doesn’t behave the same way.  The first two options are taken into account during compilation of a query, while the MAXDOP option in the resource governor is not.  Once the setting is set, it doesn’t eliminate parallelism from the query plan, it just limits the number of processors that can be used in a parallel plan.  It seems misleading since they have the same name.

This article describes the option well:  http://msdn.microsoft.com/en-us/library/ms188611(v=SQL.105).aspx

Just remember MAXDOP is not always the same as MAXDOP!


One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s