SQL SERVER – MAXDOP Settings to Limit Query to Run on Specific CPU

This is very simple and known tip. Query Hint MAXDOP – Maximum Degree Of Parallelism can be set to restrict query to run on a certain CPU. Please note that this query cannot restrict or dictate which CPU to be used, but for sure, it restricts the usage of number of CPUs in a single batch.

Let us consider the following example of this query.

The following query usually runs on multicore on a dual core machine (please note it may not be the case with your machine).

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
GO

Now the same query can be ran on a single core with the usage of MAXDOP query hint. Let us see the query for the same.

USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 1)
GO

Execution plan for the query with query hint of maxdop (1) does not have parallelism operator in it. This way we can remove the parallelism using MAXDOP.

However, before playing with this query hint, please make sure that you check your performance using an execution plan. It is quite possible that the performance of Query with MAXDOP as query hint may be quite degraded when compared to the original performance. You should be very careful with this hint.

Let us compare in our case what is the performance difference between the two above queries. The difference between those two queries is only the query hint of MAXDOP.

In our example, we got degraded performance as we restricted our query on a single CPU. This is not necessary in the case of all the queries. MAXDOP may improve or reduce performance, test your query out.

I have now one question for all readers. Do you use this query hint? If you do use it, then what is the purpose of the same. Please leave a comment here.

Reference : Pinal Dave (https://blog.sqlauthority.com)