Robert Davis(blog/twitter) recently blogged The Toilet Analogy … or Why I Never Recommend Increasing Worker Threads, in which he uses an analogy for why increasing the value for the ‘max worker threads’ sp_configure option can be bad inside of SQL Server. While I can’t make an argument against Robert’s assertion that increasing worker threads may not improve performance, I can make an argument against his suggestion that, simply increasing the number of logical processors, for example from 4 to 8, can resolve the problem.
Why do I make an argument against this? It’s really quite simple, you can easily double the number of logical processors inside of SQL Server, by enabling hyper threading at the BIOS level for the server. While this feature of Intel processors doubles the number of logical processors available to the system, thus doubling the number of schedulers available to SQL Server, it isn’t without cost. Now, to be perfectly fair, Robert doesn’t mention in his blog post any concept of increasing the number of logical processors by enabling hyper threading for the server, but its not hard for someone that is unfamiliar with SQL Server scheduling to come to that conclusion.
It is unfortunate that Robert uses a easy to misinterpret/misconstrue example of simply doubling the logical processors, which in turn doubles the number of schedulers inside SQL Server. Whether or not enabling hyper threading will help or hinder SQL Server depends on a number of factors, not the least of which is the type of processor in use on the server. Why does the type of processor make a difference? I am very happy that you asked. It makes a difference because the size of the on-chip cache, as well as the number of processor cores affects how efficiently the processor can make use of hyper threading under memory dependent workloads, like that generated by SQL Server. Older processors with smaller caches, can be prone to cache miss problems that can negatively impact performance, while newer processors may not have this same type of problem under the same workload with hyper threading enabled.
One of the key points that Robert accentuates in his blog post, is that appropriate testing should be performed for changes to the system to ensure that the change has the anticipated effect. On some systems, enabling hyper threading, and doubling the number schedulers available inside of SQL Server, might just be the needed change to accommodate a given workload, without having to scale up the physical hardware for the server. However, the same system may be prone to bottlenecks under a different workload. On older hardware, hyper threading has been recommended against, but my own testing on newer hardware (Xeon 5530) processors, have shown the the impact of enabling hyper threading can be beneficial, or at worst negligible depending on the type of workload being generated.
The biggest takeaway from Robert’s blog post is to properly test any type of configuration change before actually implementing it in production.