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.

Improve this question
7
  • 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.
    – TychoBrahe
    Mar 8, 2021 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 8, 2021 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?
    – TychoBrahe
    Mar 9, 2021 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 9, 2021 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.
    – TychoBrahe
    Mar 9, 2021 at 14:54

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 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.
    – TychoBrahe
    Mar 9, 2021 at 14:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.