If you try fixing SQL Server Max Worker Threads for errors such as: “The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread.” then we have some recommendations for you. The error looks like something given below:
Error: “The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread.”
Fixing SQL Server Max Worker Threads
Typically, the easiest shortcut to solve this problem is by increasing the number of CPU cores in your operating environment. If you are running a VM (Virtual Machine), this can be achieved quickly, provided you have the available resources. But, it would be better to understand why this occurs and what are the limitations for such scenarios.
Thread Usage by Availability Groups
Always On availability groups (AG) has the following requirements for worker threads:
- On an idle instance of SQL Server (2012, 2016 or 2017), Always On availability groups use 0 threads.
- The maximum number of threads used by the availability groups is the configured setting for the max number of server threads (‘max worker threads’) minus 40. See Table below:
- The availability replicas hosted on a server instance share a common single thread pool.
Threads are always shared on an on-demand basis, as follows:
- In typical scenarios, there are about 3 to 10 shared threads, but this number can increase depending on the primary replica workload.
- If a given thread is idling for a while, it is released back into the general thread pool of the SQL Server thread instance. Typically, an inactive thread is released after ~15 seconds of inactivity. However, depending on the last activity, an idle thread might be retained longer.
- A SQL Server instance uses up to 100 threads for a parallel-redo for secondary replicas. Each of the databases uses up to one-half of the aggregated total number of CPU cores, but no more than 16 threads per database. If the total number of required threads for a single instance exceeds 100, SQL Server uses a separate redo thread for every remaining database. Redo threads are released after ~15 seconds of inactivity.
Note to Self:
Databases are determined to go single-threaded based on their ascending database ID. As such, the database creation order should be counted for SQL Server instances that host more availability group databases than the available worker threads. For example, on a system with 64 or more CPU cores, all databases starting with the 7th database that joined the availability group will be in serial redo mode irrespective of the actual redo workload for each database. Databases that require parallel redo should be added to the availability group first.
- Also, availability groups use unshared threads, as follows:
- Each of the primary replicas uses 1 Log Capture thread for each central database. Also, it uses 1 Log Send thread for each secondary database. Log send threads are released after ~15 seconds of inactivity.
- A backup on a secondary replica holds a thread on the primary replicas for the duration of the backup operation.
For more information, see Worker Pool Usage for Availability Groups Enabled Databases
Also Read: SQL Server Always On Max Worker Threads Restrictions
Here is a quick look at the fixed numbers.
| CPU Core Count | Older 32 bit Servers | Latest 64 bit Servers |
| Up-to 4 processors | 256 | 512 |
| Octa-Core 8 processors | 288 | 576 |
| 16 processors | 352 | 704 |
| 32 processors | 480 | 960 |
| 64 processors | 736 | 1472 |
| 128 processors | 4224 | 4480 |
| 256 processors | 8320 | 8576 |
How to change the Max Worker Threads count using T-SQL
USE AdventureWorks2016 ; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE ; GO EXEC sp_configure 'max worker threads', 1000 ; GO RECONFIGURE; GO
Use the following Query to find out which System Tasks have caused additional worker threads to be spawned:
SELECT s.session_id, r.command, r.status, r.wait_type, r.scheduler_id, w.worker_address, w.is_preemptive, w.state, t.task_state, t.session_id, t.exec_context_id, t.request_id FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id INNER JOIN sys.dm_os_tasks AS t ON r.task_address = t.task_address INNER JOIN sys.dm_os_workers AS w ON t.worker_address = w.worker_address WHERE s.is_user_process = 0;
Conclusion
Typically, you will face difficulties with worker threads on SQL Server when you do not have enough CPU cores to handle the load. In these scenarios, you will need to increase the CPU cores to address the problem.
Questions or suggestions are welcome in the comments section below. Thanks!