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)
