What MAXDOP setting should be used for SQL Server (2024)

Very helpful article

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.

"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 Hintyou 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 notrecommend anyone to do this. Leave at 0 as recommended by KimDelaney 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).

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

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 belowMS 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

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!

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 workloadextremes: on the one hand they werepredominantly OLTPsmall 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 tomore acceptable values (~

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.

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

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.

Good one…

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

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.

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"

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.

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

Thanks for sharing the information.

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 Hintyou can add to every SQL Select. The first link in next steps above:

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