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 (http://blog.SQLAuthority.com)

8 thoughts on “SQL SERVER – Find Max Worker Count using DMV – 32 Bit and 64 Bit

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

  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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s