AlwaysOn and Max Worker Thread and Timeouts

The following table shows the number of worker threads that are automatically-configured based on the number of CPUs present on the server:

Availability groups uses 0 thread when idle.

But 40 threads are set aside when ever Availability group is active.

The maximum number of threads used by Availability Groups is the configured setting for the maximum number of threads (‘max worker threads‘) minus 40.
Typically there are 3-10 shared threads, but this can increase depending on the primary replica workload.

So Primary Replica uses one thread for Log capture.

One worker thread for each secondary database log Send.

Secondary replica requires one worker thread for each secondary db for redo logs or process.

SQL server will release the worker thread if it is inactive for 15 seconds.

Minimum worker threads for Always On Groups depend on the following:

Number of AlwaysOn availability Groups
Databases in the Availability Groups
Number of secondary replicas

How do you see the worker threads (pool)?

select * from sys.dm_exec_requests
where command like ‘%HADR%’
or command like ‘%DB%’
or command like ‘%BRKR%’

Log Capture Thread (LCT)
Log Send Thread (LST)
Message Handler Thread (MHT)
Min worker thread AG = DC * (lct+(lst*src))+MHT

Example- If we have 10 databases in a availability Group and 2 secondary replicas then the thread count will be:

10(1+(1×2)+1) = 10(1+2+1)
10*4
40

For 20 Databases and 2 Secondary replicas
20(1+(1×2)+1)
20*4
80

The Minimum Pool size required for our environment in this article would be 80.

If we then looked at the worker thread table above, we’d see that the number of threads available to the system on a 64 bit 4 cpu server would be 512.

So with the Alwayson Requirements The Max Threads used by AOAG is the
Setting “Max Worker Threads” minus 40
so 512-40 = 472

Then subtract the availability Group Threads = 472-80

Then we need to take into consideration the Other threads used for transactional activity like backups etc. This can exhaust the threads very quickly and make the system go into a TIMEOUT scenario and we will start seeing the below messages.

see error messages related to Worker Thread Exhaustion, but when you do, it is likely to be a symptom of adding too many databases to your AG environment or related to AG with too many activity.

Your system will become unresponsive and, if you try to open a new connection to your instance of SQL Server through SSMS, you will more than likely be met with an error message such as “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding”

How do we CAP the Worker Threads

The HardThreadPool is capped at the sp_configure ‘max worker threads’ minus 40 level.

To increase the size of the HadrThreadPool increase the max worker thread setting.

Note: increasing the max worker thread setting can reduce the buffer pool size.