Home > Workload Solutions > SQL Server > Best Practices > AMD-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Performance Best Practices > SQL Server 2019: Max Worker Threads
In this best practice we increased SQL Server max worker threads. By increasing max work threads, we hoped to provide the database with more worker threads to optimize system performance.
Category | SQL Server 2019 Enterprise |
Product | SQL Server |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine Tuning |
Overview
The default value for worker threads is zero. A setting of zero directs the SQL Server to automatically configure the number of worker threads at database startup. In our baseline tests we configured max worker threads to 864, as this is the recommended value for databases using eight CPUs and 64-bit computer starting with SQL Server 2016 (13.x) SP2 and SQL Server 2017 (14.x). For more information about Microsoft’s recommendations see, Configure the max worker threads Server Configuration Option.
After testing a few max worker thread configurations, we found increasing the value to 864 provided a slight performance improvement.
Recommendation
Every database configuration is different. For this reason, we recommend following Microsoft’s recommendations for worker thread values. With additional testing, customers might be able to improve performance by adjusting the settings. In this best practice, increasing max worker threads to 864 provided for slight improvements to the following metrics:
There were no changes to the metrics below:
Because of the slight performance improvement, we recommend this practice as a Day 3 Fine Tuning recommendation.
Implementation Steps
For information on configuring max worker threads, please refer to this link.
Additional Resources
Configure the max worker threads Server Configuration Option.