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
Pinal, I use this query hint only for debugging in order to compare performance of 2 queries.
I your post you forgot to mention that there is a global configuration setting for SQL Server which manages parallelism on the entire server. General rule is, that for the OLTP databases parallelism can seriously degrade performance, hence it is most likely better to turn it off.
Here is a script on how to do this:
Hi Feodor ,
It is better if we use hint for the parallelism instead of general setting. Because we require it sometime for the query and sometime not requite.
Really ?? Do you want to implement this on a BI database where you have more than 5000 queries in different forms? It should be on the global level, instead; unless you are working on small database server.
On an OLTP server where the MaxDop server setting is at 1, I use the hint on any large index creation or large data query that would benefit.
Overriding it upwards like this is rare to see, but has some very good scenarios in which I would want to do it – in a high end transactional environment, and online index operation has the ability to get out of hand and runaway as new data is coming in and existing data is changing, whilst a single thread is trying to put together a new index. Overriding it upwards so that it has more threads allocated brings it under control.
Yes, I have experienced that by setting off parallelism by MAXDOP =1 or by setting max degree of parallelism to 1, generally on development and QA environments where we have less amount of RAM, we are getting measurable performance improvement.
Also, before setting this off we used to have too mach wait for CXPACKET wait type, but after setting parallelism off we are not getting wait for this wait type. This is applicable to DEV and QA environments having less then 3/4 GB ram.
The MAXDOP hint becomes very useful when working with large datasets. For some reason, running certain queries referencing large datasets performs better when the query is restricted to 1 processor than when allowed to use them all. The question is WHY?
I have not found a solid explanation for this behavior. I would be very interested to know WHY this works if anyone has found such an explanation.
I use MAXDROP because I currently have no testing environment. Server performance drops when running large queries. So I restrict the query to 1 processor, allowing the other processor to handle the end user’s day to day tasks.
I know it isn’t how it is meant to be used but it certainly is a decent workaround.
I recently came accros an issue with one of UPDATE statement that was using JOIN before updating data. But, for large amout of data, this was giving me following error:
Transaction (Process ID 62) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I added Option(MaxDOP 1) query hint to my update query and that solved issue.
Hi Savita,
I think setting MAXDOP=1 has nothing to do with solving deadlock problems.
Hi, Aivars
Do you have better solution/suggestion?
Thank you
I know the thread is pretty old, but I also faced a similar problem recently where I was consistently getting “Transaction (Process ID ??) was deadlocked on communication buffer resources with another process and has been chosen as the deadlock victim” with a complex Select query on a huge database. Using the MAXDOP=1 option resolved the issue, though I don;t know how or why.
wiki: ” deadlock happens when two processes each within its own transaction updates two rows of information but in the opposite order. For example, process A updates row 1 then row 2 in the exact timeframe process B updates row 2 then row 1. Process A can’t finish updating row 2 until process B is finished, but it cannot finish updating row 1 until process A finishes”
how to Force a Parallel Plan
Well, I have had the same problem with parallel queries when making updates.
I get the message Transaction (Process ID 62) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
If I set MAXDOP to 1, the problem gets solved
There a various types of deadlocks. Resources such as RAM and CPU can also cause deadlocks, specially on servers where both resources are spiked.
Be careful to only read wiki. It can be a great source, but it may not tell you the full story about what actually happens in SQL internals. See the following source: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms178104(v=sql.105)
According to SQL Books online, MAXDOP = 1 is: “Suppresses parallel generation. The operation will be executed serially.”
This is why in above example, the query got degraded performance.
I just posted an article to my blog on MSDN called “SQL Server Parallelism-The Dark Side” in which my single threaded, 18 seconds query takes over 60 seconds with parallelism:
Over a reasonably large table (~300M rows) I was trying to determine which values in a column which were causing RI violations when I was trying to apply an FK to that column.
I used a NOT EXISTS approach to find these but the query seemed to be taking an inordinate amount of time, I suspected blocking and so checked the output of SP_WHO2, sure enough blocking was happening to the SPID that was running the query – but the blocking SPID was itself!
Clearly the parrallel operation was getting under it’s own feet, perhaps I could have used the WITH (NOLOCK) hint to avoid this (dirty reads were’nt an issue for this type of query), but decided instead to use MAXDOP. Sure enough next time round, only one instance of the query’s SPID listed by SP_WHO2, no blocking (of itself!) and the query came back much quicker.
@Ringo – that was not a SPID blocking itself; rather it was threads waiting on each other to complete before having the results are reassembled. So basically your query/statement is only as fast as the slowest thread. If the optimizer lost its mind because you have outdated statistics or fragmented tables/indexes, then you may find issues with parallelism.
People that leave maxdop at 0 on a prod box are retarded.
Actually Jeff, you are probably the one that is developmentally challenged. First, I’ve administered plenty of production servers were it was left at the default and they performed just fine. In data warehousing environments, you often times want to take advantage of parallelism so you leave it at 0. Secondly, sometimes it makes more sense to adjust the Cost Threshold for Parallelism so your system can find a nice balance between using and not using parallelism. Monitor your wait types and adjust accordingly.
i use it specifically when I am checking Index Fragmentation on a very larg database. Here is an example query:
SELECT
db.name AS databaseName
, object_name(ps.OBJECT_ID, ps.database_id) AS objectName
, ps.index_id AS indexID
, ps.partition_number AS partitionNumber
, ps.avg_fragmentation_in_percent AS fragmentation
, ps.page_count
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N’Limited’) ps
ON db.database_id = ps.database_id
WHERE ps.index_id > 0
AND ps.page_count > 100
AND ps.avg_fragmentation_in_percent > 30
OPTION (MaxDop 1);
I faced issue while processing OLAP database. All were working fine till I changed data source view to point to a database view where I have 2 large tables joined using inner join. When I process database with default MaxDop of 0, processing fails with parallelism error however setting MaxDop to 1 fix the issue. Not sure what is the right approach in such cases.
very nice script really i enjoy and find the fragmented indexes of all database.
Thanks.
MaxDop is used to control the number of threads per execution plan operators work, it does not restrict the CPU’s used by single batch or Query. If you set maxdop to 5 it doesn’t mean SQL Server will create only 5 threads and use Maximum 5 CPU’s. It uses 5 threads per execution plans operator work so the actual threads used and number cpu’s used in parallel can be lot more than your Maxdop.
This is a very important distinction, thanks for reminding us, Karthick.
Usually I run select queries with this MAXDOP option in Production database (for debugging purpose).
In rare situations I have seen SQL Server return inconsistent results for the same query, I am talking about running a query 3 times and getting 3 different results without any changes to to the server or data. Adding the MAXDOP 1 option has resolved this issue. In my case we eventually re-wrote the query and were able to get consistent results without MAXDOP.
Hello all,
Do you know if it is possible to specify the maxdop option (or something similar) right in the connection string, for a SQL Server 2012, and make sure that all queries running within that connection respect the maxdop setting?
I am asking because the development team I am working with face deadlocks or timeouts because of complex queries that use parallelism. They use entity framework and it seems they have no control over these queries so cannot specify the maxdop option per query. On the other hand i would not make this setting at the database level, since there might be queries that could benefit from parallelism. Hence … the above question :).
Thank you,
Iana
@iana, you may want to look into “resource governor” where you may enforce maxdop for the particular user defined in your connection string.