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 if you configure it incorrectly. The default value 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, but it can become a problem if you do it wrong. The basic idea with Parallelism is the server will attempt to take a query a split it across multiple CPU cores if it will make the query run faster. 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 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.
SELECT column1, column2, SUM(column3) AS Total FROM MyTable WHERE column1< $25.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 ON, SET 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.