My primary source of income for me is the Comprehensive Database Performance Health Check for any application using SQL Server. Recently, I was hired by a large organization which was struggling with the poor performance of their Microsoft Dynamics CRM. They had recently upgraded their SQL Server to the latest version of SQL Server and were facing slow queries, slow loading of the pages as well as white screens in the applications.
The team managing the Microsoft Dynamics CRM were very sharp and had pretty much done every single possible optimization for their server. However, they were still getting the poor and slow performance. During the Comprehensive Database Performance Health Check we figured out the root cause of their slow performance and it is parallel plans.
We discovered that there are many queries which are using parallel processors. These parallel processors can be very helpful if you have a long-running query which needs the additional processors but for the most of the queries, parallel plans can negatively impact performance. There is a cost associated with parallel operations. For simple and trivial queries the cost of the parallel operations can be very expensive which can lead to overall poor performance.
If you are using Microsoft Dynamics CRM, the recommended setting of parallelism is to 1. In another word in your system, the max degree of parallelism is set to 0 by default by MS when SQL Server is installed.
Here is the script to set the max degree of parallelism to 1 for your SQL Server which is running Microsoft Dynamics CRM.
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'max degree of parallelism', N'1' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO
Well, this blog post only talks about Dynamics but if you are SQL Server for OLTP transactions, here is the official guidelines of Microsoft. If you need further help you can always reach out to me to help you to set these settings to the best possible value for your workload.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Thanks , great article as always.
I experienced the same issue on our OLTP system which is all in house built. Users where complaining daily of slow performance , I noticed a lot of latches , CXPACKET was top of the list of wait types, did not want to just change maxdop so quickly so i investigated further and found that SQL server was doing parallel processing for many small transactions which I thought did not need all cpu’s, there where also key lookups in the execution plan which i fixed, I then decided to change maxdop to 4 and cost threshold to 50, this actually solved the performance issues.
I think this solution not only works for CRM but can work on OLTP systems as well with further investigation. Since 2016 CU3 there is a new waittype called CXConsumer , this will be immediately after CXPacket , this tells you that the parallelism is a genuine and can be safely ignored.There is a lot more to CXPacket and CXConsumer , people reading this can read up on it for further investigating there issue
Thanks for sharing. The maxdop value really depends on application.
For Dynamics AX, Microsoft still recommends MAXDOP 1, even though AX makes a lot of very expensive queries for reporting and for example, periodical calculations like VAT calculations or trial balance.
I have had very good results in every AX installation using MAXDOP 4 to 8, depending on processor cores and NUMA.
The essential key here is to adjust “Cost threshold for parallelism” to something much higher that the default 5.
Depending on your environment, AX features in use etc. I strongly suggest to try setting MAXDOP > 1 and experiment “Cost threshold for parallelism” values between 25-50.
I have also made same changes to a few CRM installations and results have also been good.
Thanks for sharing your thoughts Vesa.
Thanks Pinal Dave. Would you assume a recommended MaxDOP setting of 1 still for Dynamics v9 on-prem with SQL 2016? We have run into significant performance issues when user load exceeds 1000