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: