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)
38 Comments. Leave new
@iana, you may want to look into “resource governor” where you may enforce maxdop for the particular user defined in your connection string.
Can I use a maxdop query hint with insert into temptable exec procname ? or are there any limitations ?
like..
Insert into #tablename
Exec dbo.procedurename
option (maxdop 8) ?
I am trying to run the above code but i get a syntax error at “OPTION” , if i comment out the option (maxdop 8) , the query executes without issues. Not sure what I am doing wrong.
Essentially what I am trying to do is get the output of the Stored procedure into a temp table. I already created the temp table structure .
Any advise greatly appreciated.
I used Resource Manager to set MAXDOP to 1 when a specific user executes a query. How do I test that it is working?
Will MAXDOP Reduce Chance of Deadlock ?
IF yes thanis It Recommended to avoid intra-query parallelism deadlock ?
Yes there good chances to reduce them. However, not always. This requires proper research of your workload.
Hello Pinal,
We have a stored procedure which was facing deadlocks when around 32 parallel threads are calling it. This is implemented from the .NET side for performance reasons.
The deadlock happens in one particular query where some mappings are deleted. Using the OPTION(MAXDOP 1) sure fixes the deadlock but I have the following questions:
Since this is a query level hint, will the procedure still be called by the 32 threads at the same time but only this query be run serially?
Please advise.
Thank you