Dynamics 365 CE On Premises: Understanding SQL Server Parallelism

SQL Server Parallelism

When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the maximum degree of parallelism option to limit the number of processors to use in parallel plan execution. The rule is:

• To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 4.
• To suppress parallel plan generation, set the maximum degree of parallelism to 1.
• Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution.

Check if your current setting of Max Degree of Parallelism is 1. It means that the parallel plan generation is suppressed and there is only one processor per query execution. It works well and prevents the instance from bottlenecks. However, as the capacity of processors and memory increases, a better approach should the parallelism be managed instead of being suppressed to avoid that complex query impact the users' experience by only relying on one core at a time.

Let's suppose in this current configuration you have only 4 logical processors, but the server will use only 1 per query execution. Consider about increasing the number of logical processors of SQL Server to at least 16, it could be restricted to a specified number of cores, for example 8, to let other cores free to execute other instructions. For future reference, to manage parallelism, use the following code:

sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

In most scenarios, a better approach could have been to limit the parallelism to 4 or 8, and then set the threshold to 5 at which SQL Server decides to run a query in parallel. The combination of the two settings will allow SQL Server to use maximum 4 or 8 cores at a time for a heavy query, whose estimated execution time will exceed 5 seconds; allowing this way for both parallel workload execution and users’ concurrency in accessing the database. Of course, as discussed, this is more an art than a science. Extensive testing will be required to determine the perfect balance between number of cores and cost threshold in seconds.

Example:

sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'cost threshold for parallelism', 5;
GO
RECONFIGURE WITH OVERRIDE;
GO

It means SQL Server would use 8 cores at time for heavy queries, whose estimated execution time would exceed 5 seconds. It seems to be appropriate, but subject to adjustments and tests to improve performance. This is an example scenario.

Walter Carlin - MBA, MCSE, MCSA, MCT, MCTS, MCPS, MBSS, MCITP, MS

Senior Customer Engineer - Dynamics 365 - Microsoft - Brazil