When capacity planning for your AG environment, one key factor is the worker thread resources each replica needs for good performance. Consider normal workload plus the number of databases involved in data transfer from primary to secondary replicas. This article focuses solely on scaling worker threads for AGs.


AG Worker Thread Requirements (from Microsoft Docs: Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups – Thread Usage):

- Idle SQL Server with AGs uses 0 threads.

- Max threads used by AGs = configured ‘max worker threads’ minus 40.

- All replicas on an instance share one HADR Worker Pool.

- Threads allocated on-demand; typically 3-10 shared threads, more under primary workload.

- Idle threads released back to general pool after ~15 seconds (may retain longer based on activity).

- Unshared threads:

- Primary: 1 Log Capture thread per primary database + 1 Log Send thread per secondary database (released after ~15s inactivity).

- Secondary: 1 Redo thread per secondary database (released after ~15s inactivity).

- Backup on secondary holds a thread on primary for duration of backup.


AGs differ from database mirroring (dedicated threads per DB); AGs use shared HADR pool + request queue (see Bob Dorr’s HADRON series for details).


Minimum Worker Thread Calculation

Based on:

- Number of AGs

- Number of availability databases per AG

- Number of availability replicas (max 4 secondaries in SQL 2012; up to 8 in later versions)


Variables:

- DC = Database Count

- SRC = Secondary Replica Count

- LCT = Log Capture Thread (1 per DB)

- LST = Log Send Thread (1 per secondary per DB)

- MHT = Message Handler Thread (1)


Formula (from Bob Dorr’s HADRON Worker Pool article):

Minimum Pool Size = (DC × (LCT + (LST × SRC))) + MHT


Example environment:

- 1 Primary Replica

- 1 Secondary Replica

- 1 Availability Group

- 100 Availability Databases


Worst-case (all 100 DBs active):

Minimum Pool Size = (100 × (1 + (1 × 1))) + 1 = (100 × 2) + 1 = 201 threads


On a 64-bit 4-CPU server, default max worker threads = 512.

AG max = 512 – 40 = 472.

201 threads for AGs leaves limited headroom for transactional workload, backups, or spikes.


Risks of Under-Planning

Adding too many databases via script can exhaust threads quickly → lost connections, incomplete joins, databases left out of AG. Recovery may require dropping the AG and restoring databases from backups taken during setup.


Recommendation

Plan deployments carefully. Calculate required threads ahead of time, especially in production. Understand current capacity and forecast growth to size server resources properly.


References

- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prerequisites-restrictions-and-recommendations-for-always-on-availability

- Bob Dorr: HADRON Learning Series – Worker Pool Usage (http://blogs.msdn.com/b/psssql/archive/2012/05/17/alwayson-hadron-learning-series-worker-pool-usage-for-hadron-enabled-databases.aspx)