SQL
Server uses the operating system’s thread services by keeping a pool of workers
(threads or fibers) that take requests from the queue.
It attempts to divide the
worker threads evenly among the SQLOS schedulers so that the number of threads
available to each scheduler is the Max Worker Threads setting divided by the
number of CPUs. With 100 or fewer users, there are usually as many worker
threads as active users (not just connected users who are idle). With more
users, it often makes sense to have fewer worker threads than active users.
Although some user requests have to wait for a worker thread to become
available, total throughput increases because less context switching occurs.
The Max Worker Threads default value of 0 means that the number of workers is configured by SQL Server, based on the number of processors and machine architecture. For example, for a four-way 32-bit machine running SQL Server, the default is 256 workers. This does not mean that 256 workers are created on startup. It means that if a connection is waiting to be serviced and no worker is available, a new worker is created if the total is currently below 256. If this setting is configured to 256 and the highest number of simultaneously executing commands is, say, 125, the actual number of workers will not exceed 125. It might be even smaller than that because SQL Server destroys and trims away workers that are no longer being used. You should probably leave this setting alone if your system is handling 100 or fewer simultaneous connections. In that case, the worker thread pool will not be greater than 100.
