Arsh - Thursday, August 31, 2017 7:48 AM
Nothing is ever unanimous. Of course there are differences of opinion on all this.
The general consensus is that you should change these values from the defaults. Exactly what to change them to is open to debate. I defer to others on how to deal with MAXDOP since it gets so much into hardware architecture where I just don't have adequate knowledge. I follow the guidelines offered by the SQLSkills team on this.
The Cost Threshold for Parallelism value on the other hand, I can make specific suggestions on. I have a document on the best way to identify a specific value for your system The general consensus is that you should change these values from the defaults. Exactly what to change them to is open to debate. I defer to others on how to deal with MAXDOP since it gets so much into hardware architecture where I just don't have adequate knowledge. I follow the guidelines offered by the SQLSkills team on this. The Cost Threshold for Parallelism value on the other hand, I can make specific suggestions on. I have a document on the best way to identify a specific value for your system on my blog. If you don't want to do all that work, then I can suggest starting values of 50 for OLTP and 30 for DW. If you don't want to do all that work, then I can suggest starting values of 50 for OLTP and 30 for DW.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning