Worker Threads in SQL Server Always On Availability Groups and The Problems of Having Thousands of Databases on a multiple Instance

Yestarday, i was talking with a friend about of “Kind of problem we can have running a lot of databases with Availability Groups (AG) on a multiple Instances on a single machine” and we got how Worker Threads work with AG, ando NOW i’ll explain this. Check it out!

When you have multiple instances of SQL Server running on a single machine, each instance operates independently with its own set of worker threads. The configuration of worker threads for each instance is managed separately. Each SQL Server instance has its own max worker threads setting. This means you configure the maximum number of worker threads individually for each instance. The setting for one instance does not affect the others.
Although each instance has its own worker thread configuration, the total number of worker threads used across all instances on a machine can be constrained by the total available system resources.

SQL Server does not enforce a strict global limit on the total number of worker threads across all instances. Instead, each instance will use up to its configured maximum worker threads, provided system resources allow.

The practical maximum number of worker threads across all instances is limited by the server’s hardware resources. If you have multiple instances, the total number of threads in use will be the sum of the maximum worker threads settings for each instance, but in practice, the operating system and hardware resources will limit the effective number of threads.

For example, if you set the maximum worker threads to 1000 for each of 3 instances, the theoretical maximum across all instances would be 3000 threads. However, the actual number might be lower based on system resource constraints.

How to find the Max Worker Threads?

If CPU count is greater than 4 and less than 64, use the following formula:

max worker threads = 512 + (logical CPU-4) * 16

If CPU count is greater than 64, use the following formula:

max worker threads = 512 + (logical CPU-4) * 32

Or you can check it on SSMS:

SELECT max_workers_count FROM sys.dm_os_sys_info

EXEC sp_configure 'max worker threads';

A SQL Server Always On availability group requires some worker threads as high level as:

• Availability groups uses 0 thread when idle.
• 40 threads are set reserved when ever Availability groups is active for its internal operations, reducing the available pool.
• The maximum number of threads used by Availability groups is the configured setting for the maximum number of threads (‘max worker threads‘) minus 40. Default configuration for “max worker threads” is 0.
• Typically there are 3–10 shared threads, but this can increase depending on the primary replica workload.
• Primary Replica uses one thread for Log capture (LCT) that captures transaction log changes for replication to secondary replicas.
• One worker thread for each secondary database log Send (LST) that is responsable for sends captured transaction logs to each secondary database.
• Secondary replica requires one worker thread for each secondary db for redo logs or process for applies captured transaction logs to the secondary database, keeping it synchronized with the primary.
• SQL server will release the worker thread if it is inactive for 15 seconds.

The minimum number of worker threads in SQL Server Always On Availability Group depends on the following parameters:

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

So we can use the formula bellow to calculate minimum thread requirements in Always On:

Min Worker Threads AG = D * (LCT + (LST * SRC) + MHT

• Number of Databases (D)
• Log Capture Thread (LCT)
• Log Send Thread (LST)
• Max worker Threads for AG (MHT) — 1 thread reserved by Always On
• Secondary Replica Count (SRC)

So Let’s simulate we have the following set for Always on Availability Groups:

• One Primary Replica
• Two Secondary Replica (One for DR and one for a local DC)
• One Availability Group
• Five hundred Databases

Let’s use the formula mentioned above to calcule or minimum # worker threads in AG:

Min Worker Threads AG = D * (LCT + (LST * SRC)) + MHT

Min Worker Threads AG = 500 * (1 + (1 * 2)) + 1

Min Worker Threads AG = 500 * (3) + 1

Min Worker Threads AG = 1501

At this scenario requires a minimum of 1501 worker threads for the Always On group to function effectively.
So, for example, if we have a machine with 8 cores, based on following formula, the number of threads available to the system would be 576.

max worker threads = 512 + (logical CPU-4) * 16

max worker threads = 512 + (8-4) * 16

max worker threads = 512 + (4) * 16

max worker threads = 576

As i said before, AlwayON requires the Max Threads used by is the
Setting “Max Worker Threads” minus 40, 576–40=536.

In this case, if you have a SQL Server with 16 cores (704 Worker Threads) or
even 32 (960 Worker Threads) , you will have a shortage of worker threads.
When your system doesn’t have workers thread to do the job THREADPOOL waits occur, because SQL Server is unable to assign a worker thread to a new task because all available threads are busy. This usually indicates that the server is under heavy load and cannot keep up with the incoming requests.

You might not face this issue if most of the databases are idle, but you need to consider this point while adding databases in the Availability Groups.

Some Ways to monitor Worker Thread Pool

Keeping an eye on your worker thread pool to ensure optimal performance. SQL Server provides tools to monitor thread activity.
Here’s an example using Extended Events to track worker Threads starts:

CREATE EVENT SESSION HadrThreadPoolWorkerStart
ON SERVER
ADD EVENT sqlserver.hadr_thread_pool_worker_start
ADD TARGET package0.event_file
(
    SET filename = N'{directory_name}\HadrThreadPoolWorkerStart.xel'
)
WITH
(
    max_memory = 4096 KB,
    event_retention_mode = ALLOW_SINGLE_EVENT_LOSS,
    max_dispatch_latency = 30 SECONDS,
    max_event_size = 0 KB,
    memory_partition_mode = NONE,
    track_causality = OFF,
    startup_state = ON
);
GO

One other way, is using a system Extended Events called [system_health], but this is for a dedicated topic to talk about.

Understanding how Always On Availability Groups use worker threads allows you to optimize your configuration effectively. By calculating the minimum thread requirements and monitoring activity, you can ensure your availability groups maintain exceptional performance and high availability for critical databases.