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 (http://blog.SQLAuthority.com)

About these ads

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

  1. 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: http://feodorgeorgiev.com/blog/2010/03/how-to-disable-parallelism-on-your-sql-server/

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

      • 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”

  7. 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

  8. 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.

  9. 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.

    • 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.

  10. 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);

  11. 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.

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

  13. 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.

  14. 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.

  15. 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

  16. @iana, you may want to look into “resource governor” where you may enforce maxdop for the particular user defined in your connection string.

  17. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s