question

KranthiDBA-1529 avatar image
0 Votes"
KranthiDBA-1529 asked Criszhan-msft answered

The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads. This may degrade AlwaysOn Availability Groups performance.

I am seeing the below error within the SQL logs on a server with 8 CPU cores.
Currently the max worker thread value is set to default.
There is a single Availability group with 200 Databases that are part of the AG.
Please advise what is the best value that we can set for the "Max worker thread" to resolve the issue.

  • Error message:

The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads. This may degrade AlwaysOn Availability Groups performance. Use the "max worker threads" configuration option to increase number of allowable threads.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

Hi,

According to this article, the minimum worker thread for an availability group with 200 databases is 601. In your environment, the number of CPUs is 8, and the maximum number of worker threads configured by SQL Server by default is 576.
https://www.sqlshack.com/max-worker-threads-for-sql-server-always-on-availability-group-databases/


Changing the maximum number of worker threads may provide limited performance assistance. A more effective way is to reduce the system load or upgrade the hardware (increase the number of CPUs).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

It is very rarely a good idea to change the value "max worker thread" directly. What you can do to increase the number of workers is to add more cores to the system, and I think that you need to do that. 200 databases in an AG is lot. And then I mean LOT!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.