When it comes to optimizing the performance of your SQL Server, there are various settings and configurations that you need to consider. One such setting is the Max Worker Threads, which determines the maximum number of worker threads that SQL Server can create to process incoming requests.
Many DBAs often wonder what the optimal value for Max Worker Threads should be. By default, the value is set to zero, which can be concerning for some. However, it’s important to note that the default value of zero does not mean that your system is using zero threads.
To determine the current value of worker threads in your system, you can run the following query:
SELECT max_workers_count
FROM sys.dm_os_sys_info
This query will provide you with the actual number of threads active in your system.
SQL Server uses a default algorithm to calculate the number of worker threads based on the number of logical processors in your system. The algorithm differs for 32-bit and 64-bit systems:
- For x86 (32-bit) systems with up to 4 logical processors, the max worker threads value is set to 256.
- For x86 (32-bit) systems with more than 4 logical processors, the max worker threads value is set to 256 + ((# Procs – 4) * 8).
- For x64 (64-bit) systems with up to 4 logical processors, the max worker threads value is set to 512.
- For x64 (64-bit) systems with more than 4 logical processors, the max worker threads value is set to 512 + ((# Procs – 4) * 16).
Here is a table that indicates the default values of max worker threads for different CPU configurations:
| 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 |
Now, let’s address the question of the optimal value for Max Worker Threads. Surprisingly, the optimal value is actually the default value of zero. It is recommended to leave this setting as it is and not change it to any other value.
If your system starts running out of worker threads, increasing the max worker threads value may not be the best solution. It could potentially overload your system and result in more waiting threads. Instead, it is advisable to analyze and tune other resources in your system to improve performance.
Remember, the optimal value for Max Worker Threads is 0 (ZERO). If you need further assistance in optimizing your system’s performance, feel free to leave a comment or reach out to me.