SQL SERVER – Find Max Worker Count using DMV – 32 Bit and 64 Bit

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)

SQL DMV, SQL Scripts, SQL System Table
Previous Post
SQL SERVER – Find Most Active Database in SQL Server – DMV dm_io_virtual_file_stats
Next Post
SQL SERVER – Create Primary Key with Specific Name when Creating Table

Related Posts

8 Comments. Leave new

  • S.Jahanzaib Hassan
    April 23, 2010 12:14 pm

    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?


  • S.Jahanzaib Hassan
    April 29, 2010 3:53 pm

    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.

    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


  • Dattatray Nale
    November 19, 2012 8:31 pm

    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..


Leave a Reply Cancel reply

Exit mobile version