Triumph recently became involved in troubleshooting a Rock instance with very high overall database CPU and Worker usage. The initial indicator was slow check-in performance and failure to save attendance on check-in.
We pulled out all the stops and tested our usual suspects (we checked jobs, data views and reports, etc.) with no results. After much gnashing of teeth and weeping and wailing (otherwise known as “research” and “troubleshooting”) we came across a setting in SQL Server, “Maximum Degrees of Parallelism” - “MAXDOP".
Parallelism comes into play when a SQL Server has more than one CPU core available. Certain queries can “go parallel”, meaning that they can use more than one core simultaneously to process a query. An example might be scanning a table for records within a specified date range. There wouldn’t be any reason that these records need to be scanned sequentially, so the SQL Server query processor can split the query up and assign chunks to different cores. The benefit is a potential increase in query speed.
In certain cases, a query that has gone parallel can use up all the cores and starve other queries of resources.
Microsoft published an article, Configure the max degree of parallelism (MAXDOP) in Azure SQL Database, that explains a little about parallelism and the setting that limits how many cores a query can consume.
Interestingly, this article contains a note indicating that prior to September of 2020 Azure SQL databases had MAXDOP set by default to “0”, or “unlimited.” That month they changed the default setting to “8.” According to Microsoft, “this default helped prevent performance problems due to excessive parallelism”.
Changing the MAXDOP setting from “0” to “8” on our problem instance drastically reduced resource usage.
Here is a screenshot of database performance during Sunday morning check-in prior to the change:
And performance after the change:
This setting only applies to instances with more than one core. The number of cores available to a lower-tier Azure database is likely to be one or two and the setting will have little or no effect. Higher-tier databases may have a few cores and setting MAXDOP to something like half of the available cores may make an improvement. It will have the most effect on a large instance with many cores. The new default setting of 8, in our experience, is likely to be fine on a large instance. Experimenting would help to determine if a different setting would be of benefit.
This setting is made in the database properties, accessible in SSMS or Azure Data Studio:
