MAXDOP Setting

Maximum Degree of Parallelism (MAXDOP)

This setting helps determine if a query running on a server with multiple processors will use parallelism to take advantage of multiple cores to execute the query faster. This is referred to as Parallelism and can cause major issues. The default values is “0”, but can be changed to either disable Parallelism or to limit the number of cores used.

While the concept of Parallelism sounds great, there is no such thing as a free lunch. This feature has a price and the cost has to do with the additional CPU cycles required to manage and synchronize all the sub-tasks associated with parallelism. You might find an increase on is the CXPACKET wait type. This occurs when 1 or more sub-tasks of a query running in parallel are waiting for the other sub-tasks of the same query to finish. Unfortunately, these waiting processes are can cause bottlenecks on the system since now these threads at not available to service other queries. If you notice a lot of CXPACKET wait type, you might want to limit the degree of parallelism using the MAXDOP query hint.

Some people suggest limiting MAXDOP at the server level but you could adversely affecting other queries that are running fine under the default server setting. Many people recommend using MAXDOP at the query level to limit parallelism on individual queries.

Example:

SELECT column1, column2, SUM(column3) AS Total FROM SomeTable WHERE column1< $5.00 GROUP BY column1, column2 ORDER BY column1, column2 OPTION (MAXDOP 2)

You can safely play with the MAXDOP setting at the query level without affecting other queries. Take this opportunity to see which setting works best for your query. You could SET STATISTICS IO ONSET STATISTICS TIME ON and run the query to view the actual SQL execution time. Play with the MAXDOP query hint until you get the optimal performance.

As always, test everything twice.

Advertisements

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