Worker Threads in SQL Server Always On Availability Groups

Introduction

SQL Server Always On Availability Groups (AGs) are a powerful technology for ensuring high availability and disaster recovery of your databases. But have you ever wondered how these groups leverage worker threads to keep things running smoothly? This post takes a look into the world of worker threads in Always On and equips you with the knowledge to optimize your AG configuration.

Worker Threads: The Powerhouse of Always On

At the core of Always On lies a pool of worker threads, responsible for handling various tasks within the availability group. Here’s a breakdown of the essential worker threads:

• Max Worker Threads: This server-wide setting defines the maximum number of threads available for all tasks, including AG operations. By default, SQL Server automatically manages this value based on your system resources.
• Primary Replica Threads:
  • Log Capture Thread: Captures transaction log changes for replication to secondary replicas.
  • Log Send Thread (per Secondary Database): Sends captured transaction logs to each secondary database.
• Secondary Replica Threads:
  • Redo Thread (per Secondary Database): Applies captured transaction logs to the secondary database, keeping it synchronized with the primary.
  • Backup Thread (Optional): Manages backups on the primary replica.

Important Note: Always On reserves 40 worker threads for its internal operations, reducing the available pool for other tasks.

Calculating Minimum Worker Threads for Your AG

The minimum number of worker threads your AG requires depends on several factors:

• Number of Databases: The number of databases within your availability group.
• Secondary Replicas: The number of secondary replicas configured for your AG.

Here’s a formula to calculate the minimum worker threads for your Always On setup:

Min Worker Threads AG = (D * (LCWT + (LSWT * SRC))) + MHWT

Where:

• D: Number of databases in the AG.
• LCWT: Local Commit Worker Threads (typically 1).
• LSWT: Local Send Worker Threads (typically 1).
• SRC: Synchronous Replica Count (number of synchronous replicas).
• MHWT: Max Worker Threads for the AG (1 thread reserved by Always On).

The formula considers local commit and send threads, the number of synchronous replicas, and the reserved worker threads by Always On.

Example: Calculating Minimum Worker Threads

Let’s consider an Always On scenario with:

• One Primary Replica
• Two Secondary Replicas (One for disaster recovery and one for a local data center)
• One Availability Group
• Ten Databases

Using the formula:

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

Min Worker Threads AG = 31

This scenario requires a minimum of 31 worker threads for the Always On group to function effectively.

Let’s increase the database count to 200 and see the number of threads for AG:

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

Min Worker Threads AG = 601

Let’s again increase the database count to 500 and see the number of threads for AG:

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

Min Worker Threads AG = 1501

Monitoring the Worker Thread Pool

Keeping an eye on your worker thread pool helps ensure optimal performance. SQL Server provides tools to monitor thread activity. Here’s an example of creating an event session to track worker thread starts:

CREATE EVENT SESSION HadrThreadPoolWorkerStart
ON SERVER
ADD EVENT sqlserver.hadr_thread_pool_worker_start
ADD TARGET package0.event_file
(
    SET filename = N'E:\\SQLSERVER\\SQL14\\backup\\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

Conclusion

Understanding how Always On utilizes worker threads empowers you to optimize your AG setup. By calculating minimum thread requirements and monitoring thread activity, you can ensure your availability groups have the resources they need to deliver exceptional performance and maintain high availability for your critical databases.

Bonus Tip: For further exploration, check out our previous blog post on “SQL Server 2022: Improving Database Performance with Enhanced Parallel Redo” to learn about advancements related to worker thread utilization in newer SQL Server versions.