About Worker Thread:
When a request is received, SQL Server will try
to locate an idle thread to schedule the request. If an idle worker was not
located, new worker will be created. If all workers have been created
and none is idle, then the request is queued which will be picked up by any
worker thread from the pool of worker threads created.
The following table shows the automatically configured number of
max worker threads for various combinations of CPUs and versions of SQL Server.
CPU CORE COUNT
|
MWT- 32 BIT
|
MWT-64 BIT
|
Up-to 4 processors
|
256
|
512
|
Octa-Core 8 processors
|
288
|
576
|
16 processors
|
352
|
704
|
32 processors
|
480
|
960
|
64 processors
|
736
|
1472
|
128 processors
|
4224
|
4480
|
256 processors
|
8320
|
8576
|
-- show Max number of worker threads on the SERVER
select serverproperty('instancename') as instancename, max_workers_count as
'AllowedOn64Bit' from
sys.dm_os_sys_info
-- show number Current no of worker threads
select serverproperty('instancename') as instancename, COUNT(*) as MAXCOUNT from sys.dm_os_workers
-- show the THREADPOOL wait time
select * from sys.dm_os_wait_stats where wait_type = 'THREADPOOL'
---- most of the sessions are waiting for
LCK_M_S
select * from sys.dm_os_waiting_tasks