SQL MAXDOP (Maximum Degree of Parallelism)

When SQL is running on a server with multiple processors, it has the option to use multiple processors to satisfy a single query. This is referred to as Parallelism.

This sounds great and wonderful but as we all know, there is no free lunch. Everything has a price associated with it and parallelism is no exception. Parallelism’s cost has to do with the additional CPU required to manage and synchronize all the sub-tasks associated with parallelism.

Another item to keep your eye 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.

Many people suggest limiting MAXDOP at the server level but you could wind up adversely affecting other queries that are running fine under the default server setting. I 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.

I would SET STATISTICS IO ON, SET STATISTICS TIME ON and run the query to ewview the actual SQL execution time. Play with the MAXDOP query hint until you get the optimal performance.

Happy Tuning!

Questions? dhansen@dthsoftware.com