2

Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and indicates that some of the issues aren't fixed in SQL Server 2008.

Is there any indication that here are still problems with MAXDOP > 0 and are any DBAs out there not using 0 to reduce risks?

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
Leo Miller
  • 41
  • 3
  • 3
    As the author of the article, I can say that I was intentionally a bit fear-mongery due to a rash of parallelism issues. Have you looked at the Connect items? It's quite possible they've all been fixed in the meantime (or that you aren't affected by any of the issues). To be honest I don't go back and check 6-year old posts that often. :-) But I can tell you that my general recommendation today is certainly not to set maxdop 1 at the instance level. Unless you're suffering share point or dynamics... – Aaron Bertrand Jan 20 '16 at 23:59
  • 2
    Agree with Aaron that unless you are on sharepoint, adjust MAXDOP away from 1. You can use option(maxdop n) at query level if you want some queries to execute serially. – Kin Shah Jan 21 '16 at 00:51
  • 2
    I read over my post and I think you have taken my advice a little to the extreme. My general recommendation is to be careful about parallelism, and I certainly did not mean to imply that you should set MAXDOP = 1 generally, on any version of SQL Server. I've updated the post to indicate the status of each of the six bugs I highlighted; hopefully that can help you make a more informed decision about what you should do in your specific scenario. – Aaron Bertrand Jan 21 '16 at 01:46
  • I've learned recently from a class that if you have NUMA, to set MAXDOP equal to the number of physical cores in a single processor, but if Non-NUMA, to set no higher than the number of physical cores with a max value of 8. – Chris Aldrich Jan 21 '16 at 13:44
  • Thanks Aaron, and yes I was taking your article to an extreme. The company I work for manages a couple of thousand SQL Servers from SQL 7.0 up. With so many servers we have a first option recommendation for each version, and I want to find a preferred first option for each. It looks like this will remain MAXDOP = 0 for SQL 2008 and above, with caveats around SharePoint etc. and performance tests. – Leo Miller Jan 22 '16 at 01:46

1 Answers1

0

This should be set with respect to your system baseline, you have to balance it with your CPU + COST Threshold for parallelism.

for example, i was using the configuration of maxdop=0 when i realized that i have an high number of worker threads. once i reset it to 5 and cost threshold to 50 everything was fine.

Osama Waly
  • 161
  • 1
  • 4
  • 15