During several recent training courses, I found it very interesting that Worker Thread is not quite known to everyone despite the fact that it is a very important feature. At some point in the discussion, one of the attendees mentioned that we can double the Worker Thread if we double the CPU (add the same number of CPU that we have on current system). The same discussion has triggered this quick article.
Here is the DMV which can be used to find out Max Worker Count
SELECT max_workers_count
FROM sys.dm_os_sys_info
Let us run the above query on my system and find the results.
As my system is 32 bit and I have two CPU, the Max Worker Count is displayed as 512.
To address the previous discussion, adding more CPU does not necessarily double the Worker Count. In fact, the logic behind this simple principle is as follows:
For x86 (32-bit) upto 4 logical processors max worker threads = 256
For x86 (32-bit) more than 4 logical processors max worker threads = 256 + ((# Procs – 4) * 8)
For x64 (64-bit) upto 4 logical processors max worker threads = 512
For x64 (64-bit) more than 4 logical processors max worker threads = 512+ ((# Procs – 4) * 16)
In addition to this, you can configure the Max Worker Thread by using SSMS.
Go to Server Node >> Right Click and Select Property >> Select Process and modify setting under Worker Threads.
According to Book On Line, the default Worker Thread settings are appropriate for most of the systems.
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
Hello Pinal,
No doubt It will increase the performance but In addition its required handsome RAM to handle these worker threads.
Value 0.Database engine calculate the optimal worker threads
Hi Pinal,
Do we have to set the Max Woker Thrads? Let say I got 704 after running your query above. I have 15 CPU and 24GB on Memory. Max Woker Thrads is 0 by default at the moment. Is it really gain the performance to change it to 0 to 704?
I run the following query as well..
select count(*) from sys.dm_os_threads
I got – 88.
Any suggestions?
Thanks.
Leo
Dear Leo,
Max worker threads depends on physical processor it will be create over head if we will set depend on logical processor like Core 2 Duo,Quad core etc
First check it total physical processor,you can find easily from the device manager,
0-CPU to 15-CPU=total 16 CPU
1) 4 Quad Core= 4 physical X 4 Logical= 16
First of all find physical processor then set this parameter accordingly.
Thanks
S.Jahanzaib Hassan
Im a little confused why you would change this from 0 to say 512 which is the result of my query when I ran it. What is the gain and negative for changing this?
hi dear;
This is my system configuration:
Intel(R)Core(tm)2 Duo CPU E4500@2.20gz 1 gb ram and database like MsSqlserver 2005 developer editions
how to impure my CPU And MEMORY utilization,reply me
Thanks
k.ganeshan
Hi Pinal, looks like a typo there
For x64 (64-bit) more than 4 logical processors max worker threads = 512+ ((# Procs – 4) * 8)
For x64 (64-bit) more than 4 logical processors max worker threads = 512+ ((# Procs – 4) * 16)
I think its 16, not 8.
Not sure what is the logic behind, but below is what i observed..
max worker threads = 512+ ((# Procs – 4) * 16) — this calculation worked for 64-bit – 64 logical processors machine
max worker threads = 512+ ((# Procs – 4) * 32) — this calculation worked for 64-bit – 80 logical processors machine
Can i increase the maximum workers count?
Query : select max_workers_count from sys.dm_os_sys_info;
this will give the maximum count, So i need to increase the workers count …
Any suggestion..