Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
3  
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 testing. Few applications I know that woks good with MAXDOP 1 is DynamicsAX and Sharepoint. – Shanky Feb 12 at 10:16
3  
I would say that (as a rule of thumb) apps that are good with MAXDOP 1 normally don't run that many expensive queries, so you should probably be fine with just a relatively high CT. Setting MAXDOP 1 server-wide is a sledge hammer approach, imho. From my experience, software vendors are better at software development than database tuning, and they're often the source of persistent database tuning myths. :) – Daniel Hutmacher Feb 12 at 10:37
1  
Think you may have posted this on SQLHELP too. Answered you there but for sake of visitors to this question I said "No. You'll see reasons why CTfP is ignored bit.ly/1V8es3V" ...and "but also check out the statement about if explicit statement level MAXDOP used - so probably still worth setting " – Mark Broadbent Feb 12 at 10:46
up vote 8 down vote accepted

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.

share|improve this answer
    
Hi Shanky. Specifically, the vendor is saying there are system processes in SQL Server which benefit from CT despite Maxdop1. Not even referring to user database activity, but simply CT effects those processes regardless of Maxdop 1? I completely agree with what you are saying above but am concerned more about those system-level processes. – Peter Feb 12 at 14:52
1  
I am not aware that SQL Server prepares plan for system processes. It only does that for query which we fire or which comes from client. So I dont agree with CE coming into picture for internal process. This is incorrect – Shanky Feb 12 at 15:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.