none
Recommended MAXDOP for SQL instance RRS feed

  • Question

  • Hello,

    One of our customer is using SQL Server 2008 R2 in association with Dynamic CRM (DCRM) - Underlying physical server got 60 CPU and there is no other application running on underlying host.

    I had reviewed few expensive queries - most of them are running in parallel, with sometime as many threads as 17-24 threads and CXPACKET pops up as prominent wait type. I have read numerous times that MAXDOP should be set to 8 on OLTP server for optimal performance. Do you still recommend setting MAXDOP to 8 on a host with 64 CPU for enhanced performance?  

    Got limited exposure to DCRM and never had opportunity to work on such high end server.

    Appreciate your insight and help. Thank you.


    Monday, July 10, 2017 3:35 PM

Answers

  • Anil,

    60 cores is a lot, we don't see a lot of that anymore - because of Microsoft licensing.  I note you are on 2008 where they had not yet gone to core licensing.

    Of course it depends.  You should also investigate your NUMA structure, you probably want to keep things within one NUMA node unless you have some gigantic batch query where you're willing to turn over the whole server to getting it done faster.  You tend to get diminishing returns much over about 8 generally, after that you generate more CXPACKET but not much mores work. But finally the only way to really know is to take careful notes and then try some different values.

    I had the use of a 64 core and - did anyone ever make a 128, I *think* we had a 128!  But that was projects ago and I didn't get to use them much before I moved on.  Most I've ever had much time on were 24 and 32 core, and I haven't seen one of those since getting off 2008.

    Josh


    • Edited by JRStern Monday, July 10, 2017 10:11 PM
    • Marked as answer by Anil_Kumar_DBA Tuesday, July 11, 2017 6:26 AM
    Monday, July 10, 2017 10:10 PM

All replies

  • You would have to ask in a forum for CRM to get any recommendations to that product.

    One reason to reduce MAXDOP on an OLTP system is that you don't want a single query to take all CPUs and starve out other users. If you reduce MAXDOP, the queries that require parallelism will run slower, but the overall social experience may be better.

    Monday, July 10, 2017 9:03 PM
  • Anil,

    60 cores is a lot, we don't see a lot of that anymore - because of Microsoft licensing.  I note you are on 2008 where they had not yet gone to core licensing.

    Of course it depends.  You should also investigate your NUMA structure, you probably want to keep things within one NUMA node unless you have some gigantic batch query where you're willing to turn over the whole server to getting it done faster.  You tend to get diminishing returns much over about 8 generally, after that you generate more CXPACKET but not much mores work. But finally the only way to really know is to take careful notes and then try some different values.

    I had the use of a 64 core and - did anyone ever make a 128, I *think* we had a 128!  But that was projects ago and I didn't get to use them much before I moved on.  Most I've ever had much time on were 24 and 32 core, and I haven't seen one of those since getting off 2008.

    Josh


    • Edited by JRStern Monday, July 10, 2017 10:11 PM
    • Marked as answer by Anil_Kumar_DBA Tuesday, July 11, 2017 6:26 AM
    Monday, July 10, 2017 10:10 PM
  • Like with a lot of things...it depends.  8 is generally the recommended setting.  If the server is usually active with a lot of smaller queries you could decrease the MAXDOP to a lower setting.  If there's normally just a few queries running doing heavy lifting, consider increasing the MAXDOP to a higher value (ie 12-16).  

    Additionally, I'd recommend looking at the cost threshold for parallelism if you haven't done so already.  The default value of 5 is really too low for most environments.

    As mentioned previously - it'll take some testing and experimenting to fine tune everything.

    Tuesday, July 11, 2017 3:53 AM
  • Like with a lot of things...it depends.  8 is generally the recommended setting.  If the server is usually active with a lot of smaller queries you could decrease the MAXDOP to a lower setting.  If there's normally just a few queries running doing heavy lifting, consider increasing the MAXDOP to a higher value (ie 12-16).  

    Additionally, I'd recommend looking at the cost threshold for parallelism if you haven't done so already.  The default value of 5 is really too low for most environments.

    As mentioned previously - it'll take some testing and experimenting to fine tune everything.

    Thank you DeviantLogic for helpful response !

    Yeah, we are going to change cost threshold for parallelism to 30.  

    Tuesday, July 11, 2017 6:23 AM
  • Anil,

    60 cores is a lot, we don't see a lot of that anymore - because of Microsoft licensing.  I note you are on 2008 where they had not yet gone to core licensing.

    Of course it depends.  You should also investigate your NUMA structure, you probably want to keep things within one NUMA node unless you have some gigantic batch query where you're willing to turn over the whole server to getting it done faster.  You tend to get diminishing returns much over about 8 generally, after that you generate more CXPACKET but not much mores work. But finally the only way to really know is to take careful notes and then try some different values.

    I had the use of a 64 core and - did anyone ever make a 128, I *think* we had a 128!  But that was projects ago and I didn't get to use them much before I moved on.  Most I've ever had much time on were 24 and 32 core, and I haven't seen one of those since getting off 2008.

    Josh


    Thank you Josh for helpful response !

    There are 4 NUMA which mean MAXDOP shouldn't be set to more than 16.

    Noted your suggestion about keeping MAXDOP to 8..

    Marking your response as answer.....

    Tuesday, July 11, 2017 6:26 AM
  • You would have to ask in a forum for CRM to get any recommendations to that product.

    One reason to reduce MAXDOP on an OLTP system is that you don't want a single query to take all CPUs and starve out other users. If you reduce MAXDOP, the queries that require parallelism will run slower, but the overall social experience may be better.

    Thank you Erland - Noted your point and and checking with DCRM community !
    Tuesday, July 11, 2017 6:32 AM
  • Thank you Josh for helpful response !

    There are 4 NUMA which mean MAXDOP shouldn't be set to more than 16.

    Noted your suggestion about keeping MAXDOP to 8..

    Marking your response as answer.....

    If you have four NUMA nodes then that probably also means that MAXDOP is already effectively 16 even if you have it set to zero - I've never seen anything about this in BOL but have heard rumors and seen one or two blog articles that suggest that plans are usually built to stay within a NUMA node.

    When you a lot of lines for a SPID on sp_who2 or something they may be plan elements waiting for a processor, there may be a reliable way to get real parallelism if you read the full details of a plan - or use the newest updates on the query store or something, but that's not on 2008 is it.

    Glad I could help, at least to cheer you on.

    Josh

    Tuesday, July 11, 2017 3:52 PM
  • If you have four NUMA nodes then that probably also means that MAXDOP is already effectively 16 even if you have it set to zero - I've never seen anything about this in BOL but have heard rumors and seen one or two blog articles that suggest that plans are usually built to stay within a NUMA node.

    When you a lot of lines for a SPID on sp_who2 or something they may be plan elements waiting for a processor, there may be a reliable way to get real parallelism if you read the full details of a plan - or use the newest updates on the query store or something, but that's not on 2008 is it.

    Glad I could help, at least to cheer you on.

    Josh

    Hello Josh,

    I checked again and found two queries with DOP 19 and 37. Actual execution plan was extracted from query stats DMV for queries in question and i used SQL Sentry plan explorer to open the execution plan. I think that should be reliable approach.

    Thank you again for your help !!

    Thursday, July 13, 2017 5:36 AM
  • I checked again and found two queries with DOP 19 and 37. Actual execution plan was extracted from query stats DMV for queries in question and i used SQL Sentry plan explorer to open the execution plan. I think that should be reliable approach.

    Well that's interesting, so sometimes it does go beyond one node - but apparently stops short of the full 60!

    Again, I've never seen anything but a few very vague statements from Microsoft about how maxdop is selected for big queries when there are a lot of cores "available".  Also you are on 2008, and it may be possible that NUMA-rules became stronger in later versions as hardware NUMA became standard only a bit after 2008, I think.

    Wish I had the opportunity to work with it myself, but my main environments for the last couple of years have been mostly 3 and 4 core systems - although on Azure we sometimes push it up to 20 for a few hours, and would push it higher if it was available (available and not hideously expensive).

    And before that - I don't even recall, it was a gig with severely underpowered servers which factor alone was enough to kill the project and get fifty people laid off, the dev servers were toys and I think the prod server might have had twelve cores?  But mostly it needed RAM.  I think it was eight years ago I was on servers with 24++ cores.

    Josh

    Thursday, July 13, 2017 7:15 AM
  • I checked again and found two queries with DOP 19 and 37. Actual execution plan was extracted from query stats DMV for queries in question and i used SQL Sentry plan explorer to open the execution plan. I think that should be reliable approach.

    Well that's interesting, so sometimes it does go beyond one node - but apparently stops short of the full 60!

    Again, I've never seen anything but a few very vague statements from Microsoft about how maxdop is selected for big queries when there are a lot of cores "available".  Also you are on 2008, and it may be possible that NUMA-rules became stronger in later versions as hardware NUMA became standard only a bit after 2008, I think.

    Wish I had the opportunity to work with it myself, but my main environments for the last couple of years have been mostly 3 and 4 core systems - although on Azure we sometimes push it up to 20 for a few hours, and would push it higher if it was available (available and not hideously expensive).

    And before that - I don't even recall, it was a gig with severely underpowered servers which factor alone was enough to kill the project and get fifty people laid off, the dev servers were toys and I think the prod server might have had twelve cores?  But mostly it needed RAM.  I think it was eight years ago I was on servers with 24++ cores.

    Josh

    Hello Josh,

    While working on SQL Server, sometime I feel overwhelmed because of complexity and lot many moving components. I think it happens with every technology. My current worry is to explain the management why should we allocate more memory and what is the logic behind same.

    Microsoft licensing has also influenced allocation of computing resources, at least in recent times. 

    Thank you again for your response !

    Friday, July 14, 2017 8:59 AM