What MAXDOP setting should be used for SQL Server

By:   |   Updated: 2012-11-12   |   Comments (18)   |   Related: > 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 max degree of parallelism setting

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.

max degree of parallelism setting in SSMS

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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-11-12

Comments For This Article




Friday, September 2, 2016 - 3:15:23 AM - Rohit Back To Top (43256)

 Very helpful article

 

Tuesday, August 26, 2014 - 12:01:19 PM - Gavin Back To Top (34269)

BizTalk mandates a maxdop of 1 for performance reasons.

 

From the support page :

Parallel queries are generally best suited to batch processing and decision support workloads. They are typically not desirable in a transaction processing environment where you have many short, fast queries running in parallel. In addition, changing the MDOP setting sometimes causes the query plan to be changed, which leads to poor query performance or even deadlocks with the BizTalk Server queries.

 That page also references the following kb article : http://support.microsoft.com/kb/899000

BizTalk Server database queries are relatively small, and they execute quickly. Therefore, BizTalk Server database queries do not benefit from a Parallelism setting that specifies using more than one processor.

 

From those two articles we can see that the maxdop setting should be highly dependent on the type of queries you expect to be seeing on your server.

 

Friday, June 27, 2014 - 1:32:57 AM - richard Back To Top (32428)

"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).

Friday, September 20, 2013 - 12:07:38 PM - Suresh Back To Top (26879)

 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.

Monday, August 26, 2013 - 11:31:16 AM - Liu GaoYuan Back To Top (26484)

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

Thursday, November 29, 2012 - 3:12:05 PM - Ray Ayyelos Back To Top (20642)

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!

Thursday, November 29, 2012 - 2:59:15 PM - Ray Ayyelos Back To Top (20640)

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 - 9:21:50 AM - Aaron Back To Top (20628)

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.

Wednesday, November 28, 2012 - 11:20:09 AM - John Val Back To Top (20598)
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.

Wednesday, November 28, 2012 - 9:29:08 AM - Thomas LeBlanc Back To Top (20588)

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 - 9:14:59 AM - Tony Trus Back To Top (20587)

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 - 7:19:20 AM - Nirmal Back To Top (20585)

 

Good one…

 

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

 

 

 

Wednesday, November 28, 2012 - 12:18:59 AM - Dallas Back To Top (20581)

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.

Monday, November 12, 2012 - 7:14:49 PM - Bala Murugesan Back To Top (20299)

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"

Monday, November 12, 2012 - 4:57:21 PM - Bob St. Aubyn Back To Top (20298)

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 - 11:52:42 AM - bass_player Back To Top (20295)

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 - 11:24:07 AM - Irfan Shaikh Back To Top (20293)

Thanks for sharing the information.

Monday, November 12, 2012 - 9:43:34 AM - Robert Preston Back To Top (20287)

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)















get free sql tips
agree to terms