Maximum degree of parallelism

Maximum degree of parallelism

Issue:
SQL query is running slow, Execution Plan is showing parallelism. All CPU’s are not being used to perform the run.

Reason:
If your SQL Server has multiple CPUs, and you have not changed the default setting in SQL Server to limit SQL Server's ability to use all of the CPUs in the server, then the query optimizer will consider using parallelism to execute some queries. Parallelism refers to the ability to execute a query on more than one CPU at the same time. In many cases, a query that runs on multiple processors is faster than a query that only runs on a single processor, but not always.
The Query Optimizer will not always use parallelism, even though it potentially can. This is because the Query Optimizer takes a variety of different things into consideration before it decides to use parallelism. For example, how many active concurrent connections are there, how busy is the CPU, is there enough available memory to run parallel queries, how many rows are being processed, and what is the type of query being run? Once the Query Optimizer collects all the facts, then it decides if parallelism is best for this particular run of the query. You may find that one time a query runs without parallelism, but later, the same query runs again, but this time, parallelism is used.
In some cases, the overhead of using multiple processors is greater than the resource savings of using them. While the query processor does try to weigh the pros and cons of using a parallel query, it doesn't always guess correctly.
If you suspect that parallelism might be hurting the performance of a particular query, you can turn off parallelism for this particular query by using the OPTION (MAXDOP 1) hint.

Solution:
To turn off parallelism, do one of the following:
Run the following code:
sp_configure 'max degree of parallelism', 1
go
reconfigure with override
You also can do this from the sever propertiesadvanceMax Degree of Parallelism set to 1
-or-
Increase 'cost threshold for parallelism' in sp_configure to reduce the chance of a parallel query plan being chosen for the query. One would have to test and find the minimum value for this setting on your computer to avoid a parallel plan for the query because it is related to both the query and the data in the tables involved in the query.