SQL Server Max Worker Threads - What is the best value to setup?
We have been going through a load-performance exercise for an application that will have more than 5000 concurrent connections at one time.
Going by books and expeirence by default the Max Worker Threads setting is 255, which means that up to 255 worker threads are allowed to be created. Most of the times I never had any issues with this default setting of 255 works. But not taking this as a baseline and not guaranteed though, that you can only establish 255 user connections. The basic understanding behind this concept is, the system can have thousands of user connections (which are essentially multiplexed down to 255 worker threads) and, in general, users do not perceive any delays.
So in case of application running 255 queries running concurrently which is multiplexed to the number of available CPUs on the server. In this case concurrency is only a perception anyway, regardless of the number of configured worker threads. If you configure a number of worker threads to a value that is greater than the default, it is almost always counterproductive and slows performance because of scheduling and resource overhead. Only increase this setting under very unusual circumstances and when rigorous methodical testing demonstrates that it is useful to do so. Do not attempt to apply this blindly without going through end-to-end testing on the application & server configuration.
Further to this KBA 319942 talks on "Determine Proper SQL Server Configuration Settings" explains the issue and gotchas you need to consider.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.