SQL Server uses the native thread services of the Microsoft Windows 2000 and Windows Server 2003 operating systems. One or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users.
Seperate operating system thread is assigned for each new request. However, if the number of requests on a server is high, the performance of the server is impacted. In such scenarios the ‘max worker thread’ option comes in handy, which creates a pool of worker threads to service high number of requests.
By default, ‘max worked thread’ is set to 0, which means that SQL Server will dynamically assign this value during service start up. Microsoft does not recommend modifying this setting, since the value of this option depends on the System Configuration.
This query can be used to find the value assigned to ‘max worker thread’ by SQL Server.
select max_workers_count from sys.dm_os_sys_info