may 31, 2024
CPU load optimisation
In our practice there was a case when incorrectly set parameter resulted in 8% increase of request waiting time, while correct setting increased OLTP requests performance by 10%. In this article we will consider the max worker threads parameter.
Parameter definition
This parameter is responsible for the size of the pool of worker threads that each processor has. Each request to a processor creates one or more threads, which are commands for the processor. While all the threads are busy, the request is pending. By changing the max worker threads parameter, you can increase the size of the worker thread pool.
By default, the max worker threads parameter is 0, which means that the server chooses the size of the thread pool by itself. This value is calculated at startup and depends on the number of logical processors, system architecture, MS SQL Server version. The values of the parameter are shown in Table 1.
Table 1. Default thread pool size
| Number of logical processors | 32-bit server with SQL Server before 2014 (12.x) | 64-bit server with SQL Server before 2016 (13.x) SP1 | 64-bit server with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x) |
|---|---|---|---|
|
<=4 |
256 |
512 |
512 |
|
8 |
288 |
576 |
576 |
|
16 |
352 |
704 |
704 |
|
32 |
480 |
960 |
960 |
|
64 |
736 |
1472 |
1472 |
|
128 |
1248 |
2496 |
4480 |
|
256 |
2272 |
4544 |
8576 |
You can change the parameter through the server settings, as shown in Figure 1.
Figure 1. Configuring the Maximum worker threads parameter
Alternatively, the following script can be used to change the parameter
Errors when selecting a parameter
— The default value may seem inappropriate, but changing the parameter without carefully analysing system performance can lead to degraded performance.
— Underestimating the parameter can lead to thread starvation, where threads are not released in time for requests. Requests will wait for a thread to be released with the THREADPOOL wait type.
— An overestimated value of the parameter leads to increased memory utilisation and frequent thread context changes when the processor stops executing one thread and starts executing another. This constant unloading and loading of thread states reduces performance. The thread is waiting anew for CPU time to be granted with the wait type SOS_SCHEDULER_YIELD.
You can check the waiting statistics for the above problems using the following script:
This script counts the proportion of a particular expectation from the total, but does not take into account expectations that can be ignored. The expectations that should be paid attention to are listed at the top of the result.
On one of our clients’ servers, the manually set value of the parameter was 2.5 times higher than the recommended value, which resulted in frequent changes of thread context. More than 8% of the total wait time was taken up by waits with the SOS_SCHEDULER_YIELD type. After changing the parameter to the default value, the performance of OLTP requests increased by 10% and the overall system performance improved.
Recommendations
Before changing the max worker threads parameter, queries should be optimised, which is more likely to reduce CPU load and thread starvation. Changing the max worker threads parameter is recommended only for experienced database administrators, after analyses and other performance improvements. Microsoft does not recommend changing the value of the parameter.
