Specifying Max Degree of Parallelism in SQL Server for a Query

By: Jeremy Kadlec   |   Read Comments   |   Related Tips: More > Query Optimization

Problem
The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs.  This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel.  MAXDOP is very beneficial in a number of circumstances, but what if you have a reporting like query that runs in an OLTP system that monopolizes much of the CPU and adversely affects typical OLTP transactions.  How can you scale back the CPU usage just for this query?

Solution
Luckily SQL Server 2000 and 2005 provide the OPTION clause which among other configurations, offers the opportunity to specify the MAXDOP for the specific query.  This means that a fixed number of CPUs is used for the specific query overriding the server configuration. 

If you leave MAXDOP enabled, which is the default, and need to disable it on particular queries, leverage the OPTION code to achieve non parallel processing.  This may benefit the situation where OLTP processing is impacted by reporting or batch processing.  If you have already disabled MAXDOP, then the OPTION clause can enhance particular queries. 

How do I configure the Max Degree of Parallelism at the server level?

 

How can I specify the Max Degree of Parallelism at the query level?

 

Next Steps

• Review your current MAXDOP configurations on your multiple CPU servers.  Determine if these configurations would benefit or hinder server wide performance or for specific queries.
• Conduct testing in a test environment to determine the impact of leveraging these configurations at a server and query level.
• Make the decision on the ideal MAXDOP configuration on a per server basis and begin to deploy these configurations.  *** NOTE *** - A server reboot may be required to commit the MAXDOP configuration.
• Check out this additional information related to Max Degree of Parallelism:
  • max degree of parallelism Option
  • Parallel Query Processing
  • Parallel Query Example 
  • Degree of Parallelism
  • SQL Server 2005 Crosswalk - Max Degree of Parallelism for Index Commands

Last Update: 8/24/2006

About the author

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA. Join the over million SQL Server Professionals who get their issues resolved daily. The SQL Server Security THREAT - It’s Closer Than You Think