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) * 8)
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)
Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology Tagged: SQL DMV
