Who is using my worker threads? SQL Server 2014 - HADR

We had recently a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads.

We got the message:


The thread pool for AlwaysOn Availability Groups was unable to start a
new worker thread because there are not enough avaiable worker
threads.

I already opened another question, to get a statement which (I thought) should help me analyze the problem (Is it possible to see which SPID uses which scheduler (worker thread)?). Although I have now the query to find the threads which are using the system, I don't understand why that server ran out of worker threads.

Our environment is as follows:

• 4 Windows Server 2012 R2

• SQL Server 2014 Enterprise

• 24 Processors --> 832 Worker threads

• 256 GB Ram

• 12 Availability Groups (overall)

• 642 Databases (overall)

So, the server which had the problem had the following configuration:

• 5 Availability Groups (3 Primary / 2 Secondary)

• 325 Databases (127 Primary / 198 Secondary)

• MAXDOP = 8

• Cost Threshold for Parallelism = 50

• Power plan is set to "High performance"

To "resolve" the problem we manually failed one Availability Group over to the secondary server. The configuration of that server is now:

• 5 Availability Groups (2 Primary / 3 Secondary)

• 325 Databases (77 Primary / 248 Secondary)

I'm monitoring the available threads with this statement:

declare @max int
select @max = max_workers_count from sys.dm_os_sys_info

select 
    @max as 'TotalThreads',
    sum(active_Workers_count) as 'CurrentThreads',
    @max - sum(active_Workers_count) as 'AvailableThreads',
    sum(runnable_tasks_count) as 'WorkersWaitingForCpu',
    sum(work_queue_count) as 'RequestWaitingForThreads' ,
    sum(current_workers_count) as 'AssociatedWorkers'
from  
    sys.dm_os_Schedulers where status='VISIBLE ONLINE'

Normally the server has around 250 - 430 worker threads available, but when the issue started there were no workers left.

Today, out of nowhere, the ava

Community wiki answer:

You have a high number of databases in availability groups, that's going to be where your threads are going. There is a lot involved in the compression, encryption, and transport cost. Try turning off compression, it will reduce your thread usage by a about a third (depending on the replica count).

The question is tagged SQL Server 2014, which will by default use compression. SQL Server 2016, by default, will not use compression for sync.

You may need to increase the worker threads on the instance, or better: balance out the most active ones and inactive ones on multiple servers. See the related Q & A AlwaysON Availability group query very slow.

You may also find it is an application that is unable to close requests properly. This can result in lot of sleeping sessions lying around (which consume workers).

The number of threads actually used depends on how active the databases are. You could have 1,000 databases and, if most are idle 95% of the time, you won't have any issues. It seems that your databases have become active more often and have eaten more of your threads. That's the long and short of it.

StackExchange Database Administrators Q#188102, answer score: 3