I’ve had the following discussion with two different CRM administrators in the past week. We’ll call them “Larry.”

Larry: “My CRM organization is very slow. I try to search for records and it takes 25 seconds. I tried to import an organization, and it took 25 hours to complete.”

Me: “What is your Max Degrees of Parallelism setting in SQL?”

Larry: “0”

Me: “Change it to 1.”

Larry: “That’s much better!”

This is an old tip (I think I blogged about it in a “optimal settings for CRM 3” post, but I can’t find it), but it is one of the most frequent causes of less than optimal CRM performance, because this property is set to 0 by default when SQL Server is installed. The default setting means that SQL server gets to choose how many processors are used in the execution of a query. Sometimes you can be ok with the default setting, but it is especially a problem when you have a SQL server that CRM shares with other applications. SQL may decide to use all of the processors for another application’s queries, and CRM requests may be delayed. In both of the cases I saw it in the last week, CPU resources did not appear to be overloaded at the time the slow performance took place, but changing the Max Degrees of Parallelism to 1 made a huge difference.

Common objections to changing this setting

• If it should be set to 1, Microsoft would have it default to that setting.

Microsoft sets it to 0 as that is the most common setting, but strongly recommends that you change it based on application query patterns. CRM documentation like the implementation guide and the  Server Optimization Whitepaper specifically recommend that Max Degrees of Parallelism be set to 1 for CRM.

• I have another database, like a reporting data warehouse, that needs to have a different setting

If you want to sacrifice CRM performance to make your reporting data warehouse work better, that is your choice, but in trying to save a little bit of money on SQL licensing, you will probably wind up with frustrated users and lost time waiting for CRM forms to load. If you want to save money and cut corners on your deployment infrastructure, SQL is not the place to do it.