Max. Worker Threads and when you should change it

This is a get started post for this topic and I will be adding more content as and when it is available

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)

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

 When a request is received, SQL Server will try to locate a idle thread to schedule the requst. 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. 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.

Upgrading an instance of the SQL Server 2000 Database Engine to a newer version retains the configuration value as 255 for max worker threads. You have to change this value to 0 to take advantage of SQL Server automatic worker thread calculations.

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

Now, coming to the question of "Should I change Max. worker thread ?", I would like to point you to a Slava Ok's Blog which says:

Should I configure SQL Server to use more threads – sp_configure ‘max server threads’?

You can answer this question by looking at the work queue length for each scheduler. If on average such value is above 1 then you might benefit from adding more threads to the system but only if

A. Your load currently is not CPU bounded

B. Your load currently doesn’t experience any other heavy waits (If you add more threads in this case they will just end up waiting as everyone else)

select

AVG (work_queue_count)

from

sys.dm_os_schedulers

where

status = 'VISIBLE ONLINE'

Additional Reads:

https://blogs.msdn.com/b/sqlcat/archive/2005/09/05/461199.aspx