Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

What MAXDOP setting should be used for SQL Server

MSSQLTips author Matteo Lorini By:   |   Read Comments (16)   |   Related Tips: More > SQL Server Configurations
Problem

I have a busy SQL Server and notice that several queries are running in parallel.  I know I can set the max degree of parallelism setting, but what MAXDOP should I use?

Solution

The purpose of this article is to share with the community a discussion I had with a Microsoft PFE (Premier Field Engineer) about the max degree of parallelism (MAXDOP) setting. This article, is not a deep and comprehensive discussion of what MAXDOP is, but rather knowledge shared from the field.  I have a great respect for Microsoft PFE's, because they are extremely knowledgeable and their experience comes directly from day-to-day interactions with clients. They deal with complex issues on a daily basis, so I thought the information they shared with me would be beneficial for other DBAs.

What is MAXDOP and why it is important?

When SQL Server runs on a computer with more than one processor or CPU, it detects the best degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.  

What values should I use for MAXDOP?

To answer the question: What values should I user for MAXDOP? The answer is: It depends. It depends on the hardware, the environment (OLTP vs. OLAP), the load and so on.

The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.

Below we can see the current value if we run sp_configure.

sp_configure

How do I change MAXDOP using SSMS or T-SQL?

Below shows the T-SQL command that you can run to change the value.  In this example I am changing the value to 4. This means if a query uses a parallel execution plan it will only use four of the available processors.

Within SSMS, right click on a registered server and select Property.  Then go to the Advanced page as shown below. You can then change this value and click OK to save the value.

After making this change the value goes into affect immediately, there is not a need to restart SQL Server.

What value should be used for MAXDOP?

The Microsoft Premier Filed Engineer gave us some guidelines to follow and consider when setting the MAXDOP feature.

Environment Settings
Hyper-Threading is Enabled MAXDOP should not be 0 and should not be greater than half the number of visible schedulers.

For example if you have a quad core processor with hyper-threading enable, then you will have 4x2=8 visible schedulers (each scheduler is mapped to an individual processor).

Schedulers can be seen by running this query and would be the rows that have a scheduler_id < 255:

SELECT *
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

If Processor Affinity is set in SQL Server or WSRM (Windows System Resource Manager) is used to limit the number of cores available for SQL Server.

Note: processor affinity consists of assigning specific threads to specific processors in order to prevent the OS to move threads amongst available processors. WSRM also can be used to allocate processor and memory resources to applications, users, Remote Desktop Services sessions, and so on...
MAXDOP should be no more than the number of cores available to the SQL Server instance.

So if you only allow SQL to use 4 processors via the processor affinity option this value should be 4 or less.

Environment uses hard NUMA MAXDOP should be no more than the number of cores per NUMA node to avoid expensive foreign memory access that occurs, when a task needs to use memory that does not belong to its NUMA node.

Generic Servers or general settings If you are unsure of the above values then a generic setting for MAXDOP should not be more than 8.  So if you run the command below and have more than 8 you should set this value to a maximum of 8.

SELECT *
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

What is hyper threading and how can I tell if it is enabled?

Hyper-Threading technology is a performance feature that allows one core on the processor to appear like 2 cores to the operating system. This doubles the execution resources available to the Operating System. Most of today's hardware allows you to enable or disable Hyper-Threading at the BIOS level. A quick way to see if Hyper-Threading is enabled is to view your CPU information using Task Manager in Windows and if it is enabled you should see twice the number of CPUs as you have processor cores in your server.

What is NUMA ?

Numa stands for Non-Uniform Memory Access it is a computer memory design used in multiprocessing, where the memory access time depends on the memory location relative to a processor. Under NUMA, a processor can access its own local memory faster than non-local memory, that is, memory local to another processor or memory shared between processors. To learn more about NUMA please check Understanding Non-uniform Memory Access.

Next Steps

To learn more about using the above-referenced components, check out some of these previous tips and resources below:



Last Update: 11/12/2012


About the author
MSSQLTips author Matteo Lorini
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, November 12, 2012 - 9:43:34 AM - Robert Preston Read The Tip

If you are not the DBA, and just want to control your queries,  Or if you can not convince them to change the server settings, there is a select Hint you can add to every SQL Select.   The first link in next steps above: 

SELECT *
FROM Pubs.dbo.Authors
OPTION (MAXDOP 4)


Monday, November 12, 2012 - 11:24:07 AM - Irfan Shaikh Read The Tip

Thanks for sharing the information.


Monday, November 12, 2012 - 11:52:42 AM - bass_player Read The Tip

One thing that SQL Server DBAs must pay attention to is that SharePoint 2010 requires MAXDOP setting value = 1 and this is documented

http://technet.microsoft.com/en-us/library/cc298801.aspx


Monday, November 12, 2012 - 4:57:21 PM - Bob St. Aubyn Read The Tip

Here's a query that will quickly show you your current schedulers-per-node ratio alongside your current server-level Max DOP setting:

select
 (select top 1 count(*) schedulers_per_node from sys.dm_os_schedulers where scheduler_id < 255 group by parent_node_id) schedulers_per_node
 , (select value_in_use from sys.configurations where configuration_id = 1539) current_max_dop

According to best practices also documented here, these two values should match.  Seems that most modern servers use NUMA these days.


Monday, November 12, 2012 - 7:14:49 PM - Bala Murugesan Read The Tip

Great Article! Not sure why we need to set MAXDOP to 1 for SharePoint 2010? ( Pointed by bass_Player).
"To ensure optimal performance, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances that host SharePoint Server 2010 databases. For more information about how to set max degree of parallelism"


Wednesday, November 28, 2012 - 12:18:59 AM - Dallas Read The Tip

In case it's not clear, setting MAXDOP to 1 is effectively disabling parallelism.  All queries will use just 1 processor when executing. There are many reasons why you would want to do this, and it depends on on the queries being run, as some queries tend to perform better without it.

It remains unclear why Sharepoint recommends this.


Wednesday, November 28, 2012 - 7:19:20 AM - Nirmal Read The Tip

 

Good one…

 

sp_configure will return list out ‘max degree of parallelism’ only if 'show advanced options' set to 1

 

 

 


Wednesday, November 28, 2012 - 9:14:59 AM - Tony Trus Read The Tip

You can pull this with or without advanced options from a system table:

SELECT Value FROM SYS.CONFIGURATIONS WHERE Name = 'max degree of parallelism'

I will on occasion check to see if cmdshell is enabled this way too and if I found that it was not enabled and I did not neccesarily wish to keep it enabled always I would temporarily enable it, perform a task, then disable. 


Wednesday, November 28, 2012 - 9:29:08 AM - Thomas LeBlanc Read The Tip

SharePoint support makes some interesting points about why MAXDOP 1. First, they want their queries to always use the same query plan for support. This is why they also suggest to have Create Statistics and Auto Update Statistics off on the Content database(s). 

They say not creating new statistics and letting their (SharePoint) maintenance jobs update the index fragmentation and statistcs will keep the query plan the same on all their qureies.

The SharePoint database is also not normalizaed, so it might take a query on 6 tables to return one row in a list.

I got miost of this information from a PASS Summit 2012 session called SQL Server and Sharepoint: Best Frienemies. The lady presenting was a DBA at micrsoft turned SQLCAT member for SharePoint.

Side note: We changed MAXDOP from 8 to 1 on our SQL Server instance for SharePoint and some pages that took 3 seconds to load were loading in less than 1 second after the change. Bizzare!!!

Thomas, MCITP 2008/2005 and MCDBA 2000

Senior DBA at Turner Industries

 


Wednesday, November 28, 2012 - 11:20:09 AM - John Val Read The Tip
Test carefully with Hyper-Threading enabled. We had really poor performance with it enabled on our 2008 R2 EE data warehouse during the ETL as well on the bigger queries.

Thursday, November 29, 2012 - 9:21:50 AM - Aaron Read The Tip

The default value for MAXDOP is 0 (zero) and can be set or viewed using (sp_configure). A value of 0 means that SQL Server will use all processors if a query runs in parallel.

 

Actually, from BOL:

To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value.

 

It's a subtle difference, yet important to make sure is clearly understood.


Thursday, November 29, 2012 - 2:59:15 PM - Ray Ayyelos Read The Tip

The baseline recommendations made here could definitely help configurations where OLAP/OLTP workloads must coexist and query tunining/application behavior isn't at the top of the organizations list of priorities. 

I've personally managed several environments where MAXDOP default was simply not appropriate. All cases involved a mixed workload extremes: on the one hand they were predominantly OLTP small fast executing queries and on the other OLAP where aggregation and reporting were performed against the server at unpredictable intervals (based on the threshold of arriving data + time frame).  In these cases, the aggregation and reporting jobs are highly parallizable.  One would observe extremely high values for CXPACKET wait/ context switches.  While the aggregation and reporting jobs were running waiting on its own threads to process, the other smaller OLTP more frequent (and customer impacting) queries would suffer waiting.  In my case, I lobbied to move aggregation/reporting off the production host and, when that failed, compromised and constrainted MAXDOP to NumCores/PhyProc (8); as mentioned in this post, this prevented parallelization across processor boundaries and drove parallelization wait times down to more acceptable values (~

 


Thursday, November 29, 2012 - 3:12:05 PM - Ray Ayyelos Read The Tip

Ooops, apparently LessThanEqual symbols aren't allowed and I can't edit my post, so I'll finish the thought...  LTE 5000*PhyCores.  I forgot the rest of my post!


Monday, August 26, 2013 - 11:31:16 AM - Liu GaoYuan Read The Tip

Excellent post.

 

I would like to clarify on the below:

 

In the post, it is clearly stated when NUMA is configured, MAXDOP should be no more than the number of cores per NUMA node.

 

While referring to the below MS kb,

http://support.microsoft.com/kb/2806535

For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node

Just wondering whether "number of physical processors" in MS kb referrs to "number of physical sockets" or "number of physical cores"?

 

Thank you


Friday, September 20, 2013 - 12:07:38 PM - Suresh Read The Tip

 Setting MAXDOP=1 , performance was a big hit. Do not recommend anyone to do this. Leave at 0 as recommended by Kim Delaney of SQLSkills.


Friday, June 27, 2014 - 1:32:57 AM - richard Read The Tip

"If you are not the DBA, and just want to control your queries,  Or if you can not convince them to change the server settings, there is a select Hint you can add to every SQL Select."


And if you ARE the DBA, don't openly share this tip with a big team of developers unless you're in a position to review code, because it may be tempting to simply spread the query out than properly optimise it (indexes etc).


"Setting MAXDOP=1 , performance was a big hit. Do not recommend anyone to do this. Leave at 0 as recommended by Kim Delaney of SQLSkills."


We experienced the opposite. Setting it to 1 was a massive performance increase. It very much depends on the sort of querying you're doing - if you have a small number of very heavy queries, having MAXDOP > 1 will be helpful. If you have a large volume of smaller queries, it can bite (especially with fewer cores).



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



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.