Tip #216: In a parallel dimension

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.

Tweet about this on TwitterShare on Facebook4Share on Google+1

4 thoughts on “Tip #216: In a parallel dimension

  1. Dave Ireland says:

    Confused. Completely agree that an mdop of 1 makes sense for crm. But in your conversation you seem to be telling Larry to set it to zero. Typo?

  2. Chad Rexin says:

    Another way to get good performance is to leave the Max Degree of Parallelism at 0 or …

    Hi folks, Tipp Jarr’s here. We interrupt this comment to let you know that we pulled it out only to give it its own post space. Watch out for the Monday’s tip!

Leave a Reply

Your email address will not be published. Required fields are marked *