0

We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine. Our main database server has 16 physical cores with hyper threading and 2 numa nodes. We have set the maxdop to 16 for this machine.

Edit: I had previously set it to 8 and saw the same issues.

The mirror host has a much weaker cpu, so we had to set the maxdop to 4.

When we are running an intense query on the principle database, we sometimes get an error:

Unexpected job failure.: System.Data.SqlClient.SqlException (0x80131904): The query processor could not start the necessary thread resources for parallel query execution.

I am wondering if could be due to the underpowered mirror server running out of resources while it is trying to commit all the transactions that are being fed across to it from the principal server.

I'm not a DBA, and we currently do not have one on staff (so I guess I am the DBA), so a lot of these settings and errors are foreign to me.

Edit2: This question is different from the one suggested because I originally followed the directions and scripts in that post to set my MAXDOP to appropriate values.

dev30207
  • 101
  • 2
  • No, that doesn't answer my question. I read that thread in order to figure out my MAXDOP settings, but it hasn't eliminated the problem. There are other suggestions in that thread e.g. Wait and Queue stats, that I am going to dig into next. – dev30207 Mar 08 '21 at 16:04
  • 1
    This is a MAXDOP setting problem - trying lowering to 8 or configuring your cost threshold for parallelism. This question is still a dup of that one, IMO. – LowlyDBA - John M Mar 08 '21 at 18:35
  • I previously had it at 8 and saw the same issue. How is this a dupe if the solution in that post doesn't fix my problem? Shouldn't the same problems have the same solutions? – dev30207 Mar 09 '21 at 14:02
  • You should include that in your post, it is helpful information. You both have the same error, so StackExchange would have all answers pertinent to that in the same question unless your scenario is markedly different (which you haven't indicated yet). – LowlyDBA - John M Mar 09 '21 at 14:40
  • I included it in my post. We may have the same error, but the circumstances are different, specifically because I already analyzed what my MAXDOP should be and set it appropriately, on both machines. I have no increased the cost threshold for parallelism from 5 to 10. Remember, as I stated before, I am not a DBA and we do not have one on staff. – dev30207 Mar 09 '21 at 14:54
  • If this feels over your head (which is totally OK) you may want to hire a consultant short term to help you figure this out. You still need to in some way tune your MAXDOP (at the global or query level) and/or cost threshold. If they were set correctly, you would not be seeing this issue. – LowlyDBA - John M Mar 09 '21 at 17:53
  • If I am understanding MAXDOP correctly, it will only allow SQL server to use X number of cores for a query. If I have it set to 16 on a machine that has 32 cores through hyper threading, then does that mean I have 2 simultaneous queries that are both exhausting all available threads on the CPUs? By increasing the cost threshold does that limit how quickly SQLOS will split the query to another thread, essentially forcing more throughput on one thread before running the query across a 2nd thread?

    I will talk to my employer about getting in contractor to look into this. Thank you.

     – dev30207 Mar 09 '21 at 18:57

1 Answers1

0

It sounds like the issue is your queries are exhausting the available threads of the new underpowered server, interestingly even with you tuning the MAXDOP setting. Please see this DBA.StackExchange answer for more details. You may need to limit your MAXDOP on certain queries further, or this could be a hint to up the server's provisioning to increase performance more.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • How do I know which queries to specifically limit MAXDOP for? This error message comes at seemingly random times throughout the day when load on the DB itself does not seem incredibly high. I cannot reiterate enough that I am not a DBA, and we no longer have one on staff. The person in charge of this recently passed away and now we are trying to figure these things out on the fly. – dev30207 Mar 09 '21 at 14:55