During my recent SQL Server Performance Tuning Practical Workshop, someone asked how they can determine the automatically configured max worker threads for their machine. This is a common question, and Microsoft has provided guidance on this topic. Let’s explore the concept of max worker threads and how it affects SQL Server performance.
Max worker threads refers to the maximum number of worker threads that SQL Server can create to process incoming requests. Each worker thread is responsible for executing a specific task, such as executing queries or handling connections. The number of worker threads available can impact the overall performance of SQL Server.
Microsoft has defined a table that lists the automatically configured number of max worker threads for various combinations of CPUs. For example:
| Number of CPUs | 64-bit computer |
|---|---|
| <= 4 processors | 512 |
| 8 processors | 576 |
| 16 processors | 704 |
| 32 processors | 960 |
| 64 processors | 1472 |
| 128 processors | 4480 |
| 256 processors | 8576 |
It is important to note that regardless of the number of CPUs, a 64-bit computer running SQL Server will have a minimum of 512 threads. The formula for calculating the number of threads is straightforward:
- For 4 or fewer processors: 512 threads
- For more than 4 and up to 64 processors: 512 + ((logical CPUs – 4) * 16) threads
- For more than 64 processors: 512 + ((logical CPUs – 4) * 32) threads
Now that we understand the basics of max worker threads, let’s address one of the most frequently asked questions: What is the ideal number of threads for any SQL Server?
The answer is that there is no fixed number of threads that can be considered “magic threads.” It is generally recommended to leave the thread count at the default value and not make any changes. When the worker threads are exhausted, SQL Server will simply wait until existing worker threads become available. Increasing the thread count beyond what the SQL Server engine can handle can lead to performance issues.
As a SQL Server consultant, I always advise against changing the thread count unless there is a specific and valid reason to do so. It is best to rely on the default configuration and monitor the performance of SQL Server to identify any potential bottlenecks or issues.
If you have any further questions or need assistance, feel free to connect with me on Twitter.