• Arsh - Thursday, August 31, 2017 7:48 AM

    Grant Fritchey - Wednesday, August 30, 2017 8:54 AM

    In addition to MAXDOP, it's a good idea to set the Cost Threshold for Parallelism to a higher value than the default of 5. Out of the box. You'll see fewer plans going parallel unnecessarily if you make that adjustment.

    Grant , is it suggested to change MAXDOP (with ref to Microsoft doc as well , based on the NUMA config) and CT for parallelism as a unanimous opinion , or there are any other areas to be considered for dependencies ? Thank you.

    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