5

Been debating with my company's app vendor regarding parallelism. They have maintained that Maxdop = 1 has significant performance improvements for the app/db but they have provided absolutely no proof of this.

I have tested various DOP/CT settings and found a 28% overall performance improvement from automated testing, when using Maxdop 4 (8 hyperthreaded cores) and CT 150.

The vendor's DBA recently said: "There is an issue with internal processes of MSSQL that are able to use this value even if DOP is ‘1’, there are SPID’s lower than 51 in a MSSQL server that is using it, ghost cleanups and like this."

I have researched this and, despite fairly scant information, Paul Randall at least says that ghost cleanup is always single-threaded, so apparently the vendor's dba is incorrect?

Any thoughts on the effect of CT when Maxdop =1?

Peter
  • 1,490
  • 1
  • 16
  • 35

1 Answers1

8

To say MAXDOP=1 is always beneficial is totally incorrect statement. MAXDOP value should be set according to SQL Server CPU core configuration followed by a good round of testing. Few applications I know that woks good with MAXDOP 1 is DynamicsAX and Sharepoint.

Yes ghost cleanup is single threaded as mentioned by Paul in this article but just because system process is single threaded IT DOES NOT means that MAXDOP=1 is going to be beneficial for you. This is what I gauge from your question. You can start with cost threshold of parallelism value =50. The value 5 mentioned is utter useless and it was for old days. With systems running on many cores now I recommend to set cost threshold of parallelism value to 50 and MAXDOP value which you can get from link shared below. See how this works for you.

We already have SE thread What is good way to calculate MAXDOP value I suggest you get MAXDOP value from queries mentioned in this link and see if that helps your environment.

Also I am not saying that you completely ignore Vendors recommendation but vendor supplies you application they cannot change SQL Server query engine behavior.

Community
  • 1
Shanky
  • 18,985
  • 4
  • 35
  • 58