PROBLEM:
What is max degree of parallelism and when should I set it?
SOLUTION:
When you see a lot of Locks/Blocks with the Kaseya database, one of the first things to do is to set the max degree of parallelism.
The maximum of value of the degree of parallelism setting is controlled by the edition of SQL Server, CPU type, and operating system. if a value greater than the number of available processors is specified, the actual number of available processors is used.
A lot of calculations are required to determine whether parallel processing should be used. Generally, SQL Server processes queries in parallel in the following cases:
- When the number of CPUs is greater than the number of active connections.
- When the estimated cost for the serial execution of a query is higher than the query plan threshold (The estimated cost refers to the elapsed time in seconds required to execute the query serially.)
Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE or DELETE statements contain a WHERE clause, or an INSERT statement contains a SELECT clause, WHERE and SELECT can be executed in parallel. Changes are applied serially to the database in these cases.
To set the max degree of parallelism, do the following.
exec sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
exec sp_configure "max degree of parallelism", 1
GO
RECONFIGURE WITH OVERRIDE
GO