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?
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.
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.
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.
To learn more about using the above-referenced components, check out some of these previous tips and resources below:
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
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
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
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
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
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
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
"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).
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.
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.