Pythian Blog: Technical Track

The importance of the worker threads is always on

by Hemantgiri Goswami on Jul 11, 2018 12:00:00 AM

Most of my blog posts are an outcome of the work that I do over the weekend while holding the pager for my team. This blog post is no different. In this blog post, I will talk about the importance of the worker threads in Always On availability group. We will also learn what worker threads are so we can understand things better. And, when we understand how it works, it will be much easier to follow things in detail.
What is worker thread?
Sometimes it is confusing to relate threads in SQL Server to the ones with the OS threads. But they are different. For SQL Server, the threads (worker threads) sit on top of the OS threads. Whenever a request comes in, the User Mode Scheduler (UMS) manages the execution of the request. These are the worker threads that are used here. There will be a single UMS for each CPU. At any point, UMS will have a running queue of requests waiting for CPU, IO Locks, Memory and or user requests. The worker thread is a configurable option but at the same time, you should be cautious as an inaccurate or improper configuration of this option can cause performance issues. For a better understanding of what the best possible value for the worker thread can be, I suggest reading the updated document on the Microsoft site here. Now that we know what a worker thread is, let's jump into the main topic - the importance of the worker threads in Always On. Always On Availability Groups is an enhanced version of DB Mirroring. Always On Availability groups carry out tasks such as:
  • Log capture
  • Log send queue/Redo queue
  • Message handler
It is imperative that you go through the restrictions (Availability Groups) section of the MS document here. Most importantly, the section where it says "the actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit." What that means is, if you configure Multiple Always On Groups with a number of databases with improper Worker Thread configuration, it can exhaust the worker threads and in turn, your system may perform poorly.
Calculating the max worker thread value for Always On Availability Group
Bob Dorr explains the formula in one of his articles on PSSSQL site. According to the article, MAX HADR Thread Pool = (Max Worker Thread - 40). You can increase the Max HADR Thread Pool size by increasing the worker threads but you also need to play with this very cautiously. He also takes care to explain the Minimum Pool Size that is required when you configure Always On Availability Group. Min Pool Size = Max Databases * 2 (REDO or SCAN Per Database) + At least 1 message handler You may also want to review the article to see how it is configured by default based on the number of CPUs for 32 or 64-bit architecture. So the next time you have a chance to configure or design the Always On Availability solution for your customer, make sure you consider using these formulas, especially when you have many databases. You may want to visit our resources page to explore our technical content, and if you are in need of assistance or in need of a professional help, please visit our expert’s page and we will certainly be able to help you.  

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.