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:
| SQL Server Platform | OS Platform | Stack Size |
| x86 (32-bit) | x86 (32-bit) | 512 KB |
| x86 (32-bit) WoW | x64 (64-bit) | 768 KB |
| x64 (64-bit) | x64 (64-bit) | 2048 KB |
| IA64 (64-bit Itanium) | IA64 (64-bit Itanium) | 4096 KB |
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
Comments
Anonymous
August 19, 2013
thanks for sharing the information !Anonymous
January 27, 2014
I liked the blogAnonymous
February 21, 2016
Useful information