SQL SERVER -Max. Worker Threads

Worker threads are the agents of SQL Server which are scheduled in CPU and they carry out the tasks

Memory for Worker threads come from Non-Buffer Pool region of SQL Server.

You can configure maximum worker threads SQL Server can spawn using:

sp_configure

'max worker threads'

If you leave 'Max. Worker threads' to 0 then SQL Server will decide the worker thread count based on formula below:

For 32 bit operating system:

Total available logical CPU’s <= 4 :     max worker threads = 256

Total available logical CPU’s > 4 :        max worker threads = 256 + ((logical CPUS’s - 4) * 8)

For 64 bit operating system:

Total available logical CPU’s <= 4 :     max worker threads = 512 

Total available logical CPU’s > 4 :        max worker threads = 512 + ((logical CPUS’s - 4) * 16)

Example :My system   have a 4 CPU  as below snapshot  max threads  would be  512  count

512+(4-4)*16=512 or

If you have set Max. Worker thread to 0, you can check the worker thread count calculated by SQL Server using the query

SELECT max_workers_count FROM sys.dm_os_sys_info

Output

max_workers_count

512

Query below gives the amount of worker threads created at the moment in your SQL Server:

SELECT  SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

General recommended to leave Max. worker threads to 0.

Here is a snippet showing the physical memory requirement for each worker thread spawned by SQL Server: