4

I have a SQL Server 2008 R2 SP3 Standard Edition 64-bit instance on 8 cores (576 max worker threads) with 32 GB RAM (MaxMem = 28000). It is the data store for a SharePoint installation, with 218 databases.

It was getting dozens of "SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection." errors per day, but no other errors. I found that MAXDOP = 0, which is bad for SharePoint. I gradually (over weeks) brought MAXDOP down to 1. As I did so, the frequency of those errors went down to zero on most days. But I still see them once in a while.

sys.dm_os_wait_stats has this to say about THREADPOOL waits:

waiting_tasks_count wait_time_ms    max_wait_time_ms    signal_wait_time_ms
26149               474516          4428                9

The server was last restarted at Mar 25 2018 5:55PM and current server time is Apr 20 2018 10:07PM. sp_Blitz finds nothing interesting other than use of join and order hints and slow storage writes on the drives holding tempdb files.

This is on a VM in a private cloud. Increasing the number of CPUs will be very expensive, and while it is heavily used, CPU usage doesn't seem to be a problem. In this case, would increasing the max worker threads be a reasonable thing to try, should I just leave it alone and live with the occasional 17189 error, or is there another option?

Improve this question
2
  • 1
    Maybe this is just my hatred of Sharepoint talking, but with the frequency seemingly low, is it actually causing you any issues? Or are you just pursuing a clean error log (nothing wrong with that, just trying to contextualize the issue).
    – LowlyDBA - John M
    Apr 20, 2018 at 21:03
  • @LowlyDBA: I haven't heard any complaints, but I don't want to assume that means there isn't a real problem. I'm no SharePoint fan, but I don't want to just brush this off as "it just does that sometimes" without digging into it a bit more.
    – Mark Freeman
    Apr 23, 2018 at 13:04

1 Answer 1

Reset to default
6

Increasing MWT can lead to increased SOS_SCHEDULER_YIELD waits. Not the end of the world, but think of it like adding a buncha kids to a teacher's class. All of a sudden, it's gonna be harder for each kid to get attention.

When a process exhausts its 4ms quantum, there will potentially be more threads ahead of it waiting to get on the CPU. It's really difficult to say if performance will be worse with that trade off.

You could try increasing MWT a incrementally.

This is how SQL Server manages it:

NUTS

Notice how doubling core counts doesn't double MWT, and you get the same number with 1 core as you do with 4 cores?

It's like a sign, or something.

The equation is: 512 + ((logical CPUs - 4) * 16), which means at 10 cores you'd have 608 threads, and at 12 cores you'd have 640 threads.

Those are reasonably safe increments to try, but I wouldn't go over that without a support call with Microsoft.

Hope this helps!

Improve this answer
2
  • I found that table while doing my research on this and was definitely getting the hint that increasing MWT was unlikely to be a cure-all. It being SharePoint, my assumption is that there is something "unusual" about the load it puts on a SQL Server. I definitely would be very conservative in raising MWT, hoping that maybe it just needs a little more slack in this area. I'll coordinate with our Infrastructure team and come up with a plan.
    – Mark Freeman
    Apr 23, 2018 at 13:11
  • Just to follow up on this, we increased MWT by a tiny increment and the instance dumped core after a short time. We reverted the change.
    – Mark Freeman
    Aug 10, 2020 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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