-1

we are having continued discussions with our vendor whom is insistent on setting MaxDop to 1

we have performed internal testing and see a general 20% performance improvement by setting this to 4 with cost threshold to 50

we suspect they may be other motives of insisting setting of 1.

I'm unsure what they maybe however.

** EDIT:: to make the question more specific, is there a scenario where by setting maxdop to anything other than 1 cause errors to occur**

RoughPlace
  • 121
  • 6
  • Why don't you ask them about their motivation? They are the only ones who can tell you why they are insistent. – Hannah Vernon Jul 15 '15 at 14:43
  • We have and they have yet to provide any substantial evidence of a reason – RoughPlace Jul 15 '15 at 15:17
  • 2
    Why do you think we will guess their reason(s) correctly? – Aaron Bertrand Jul 15 '15 at 15:54
  • hi aaron, i do not, i was looking for a little insight into other peoples experiences and at best something i could read into that expanded on my understanding of MaxDOP and would help in hte conversation to try to persuade them to support somethign other than 1. – RoughPlace Jul 15 '15 at 22:09
  • i.e. under what particular scenario would they suggest maxdop 1 i.e. poor indexing and large tables – RoughPlace Jul 15 '15 at 22:15
  • We had (on Azure Managed Instance) attempted to set the MaxDOP to 1; and had Full Text Search immediately grind to a halt. It probably shouldn't have, I fully blame Azure for that particular quirk, but managing Full Text on a server where you can't just bounce the Full Text Daemon when you want/need is a struggle. Just my two cents. – Jonathan Fite Sep 06 '23 at 13:41

2 Answers2

1

Unless you are installing any specific Microsoft application such as SharePoint or BizTalk, setting MAXDOP = 1 server wide is not a good choice.

There is a good and repeatable way to calculate MAXDOP on SQL Server based on Microsoft best practice.

Also, refer to How It Works: Maximizing Max Degree Of Parallelism (MAXDOP)

My recommendation is to ask your vendor, why they would need it at server instance level as opposed to query level. Also, look into adjusting cost threshold for parallelism which is by default set to 5. Cost threshold of parallelism refers to what the minimum query cost has to be before Parallelism is considered by the optimizer.

Glorfindel
  • 2,201
  • 5
  • 17
  • 26
Kin Shah
  • 62,225
  • 6
  • 120
  • 236
  • thank kin i was aware of the reccomendation for sharepoint my understanding is this is because sharepoint is particularly bad for table scans – RoughPlace Jul 15 '15 at 22:14
-1

In a highly transactional environment MAXDOP=1 can be an advantage.

It's not really your vendors business what your sever setting is. If their application queries need MAXDOP 1 suggest query hints. Your vendors expectation that you'll make a server wide change for the application is unreasonable.

user41207
  • 97
  • 6