Max Degree of Parallelism for CRM

One of the major settings used during performance tuning for CRM On Premise is setting the Max Degree of Parallelism. 

Max Degree of Parallelism is something that can become a very deep topic and so we are going to limit the scope here down to just CRM considerations.  I have some links at the bottom of this article that talk about the real meat and potatoes of this server level setting if you want more information.  For CRM we are considered an OLTP workload.  The simple version of that meaning that we are a transaction heavy program and while the end user might only have one click to see a new view, open a record or run a report; there are a number of actions that are occurring in the background. 

The general suggestion from CRM is that we have Max Degree of Parallelism set to a value of 1.  This will allow the largest number of simultaneous queries be executed at the same time.  For environments that have a large number of processors on the SQL server that can be consumed this value can potentially be set to a higher value, but something that will need and want to be tested for how overall performance is impacted.  The query set for setting max degree of parallelism is below.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

More information:

max degree of parallelism Option

Case Study: Part 1: CXPACKET Wait Stats & 'max degree of parallelism'

Case Study: Part 2: CXPACKET Wait Stats & 'max degree of parallelism'

Recommendations and Guidelines for 'max degree of parallelism' configuration option