Learn more about SQL Server tools

   
   






























































Latest from MSSQLTips














Specifying Max Degree of Parallelism in SQL Server for a Query

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



> > Next Free Webcast - Monitoring SQL Server with Mobile Devices


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?

SQL Server 2000 SQL Server 2005
  1. Open SQL Server 2000 Enterprise Manager
  2. Once the tool loads, navigate to the intended server and right click on server name
  3. Select the 'Properties' option
  4. Select the 'Processor' tab
  5. Review the configurations for the 'Parallelism' section
  1. Open SQL Server 2005 Management Studio
  2. Once the tool loads, navigate to the intended server in the Object Explorer and right click on server name
  3. Select the 'Properties' option
  4. Select the 'Advanced' page from the left navigation
  5. Review the configurations for the 'Parallelism' section
How to configure the number of processors available for parallel queries (Enterprise Manager) How to: Configure the Number of Processors Available for Parallel Queries (SQL Server Management Studio)

 

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

SQL Server 2000 SQL Server 2005
SELECT *
FROM Pubs.dbo.Authors
OPTION (MAXDOP 1)
 
SELECT *
FROM Pubs.dbo.Authors
OPTION (MAXDOP 1) ;
 
OPTION Clause Query Hint (Transact-SQL)

 

Next Steps



Last Update: 8/24/2006


About the author
MSSQLTips author Jeremy Kadlec
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as a SQL Server Consultant at Edgewood Solutions, co-founder of MSSQLTips.com as well as Baltimore SSUG co-leader.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools


Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information







 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.